insert data to two/three table at once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert data to two/three table at once

Author

Topic

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-20 : 22:27:38

Hi,
i have a question. Can i insert data into two/three table at once???

my code:
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(GetConnectionString());

string cust = ("INSERT INTO Customer (CarOwnerName, IdentityNo, Address, PostalCode, City, State, HomeTel, MobileNo) VALUES " + " (@CarOwnerName, @Identity, @Address, @PostalCode, @City, @State, @HomeTel, @MobileNo)";
try
{
con.Open();
SqlCommand cmd = new SqlCommand(cust, con);
cmd.CommandType = CommandType.Text;

string CarOwnerName = txtCarOwnerName.Text;
string Identity = txtIdentityNo.Text;
string Address = txtAddress.Text;
string PostalCode = txtPostalCode.Text;
string City = txtCity.Text;
string State = ddlState.SelectedItem.Value;
string HomeTel = txtHomeTel.Text;
string MobileNo = txtMobileNo.Text;

cmd.Parameters.AddWithValue("@Identity", txtIdentityNo.Text);
cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
cmd.Parameters.AddWithValue("@PostalCode", txtPostalCode.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", ddlState.SelectedItem.Value);
cmd.Parameters.AddWithValue("@HomeTel", txtHomeTel.Text);
cmd.Parameters.AddWithValue("@MobileNo", txtMobileNo.Text);
cmd.ExecuteNonQuery();

}
catch (System.Data.SqlClient.SqlException ex_msg)
{
//Here will be catch elements
string msg = "Error occured while inserting";
msg += ex_msg.Message;
throw new Exception(msg);
}
finally
{
//Here will be fially elements
con.Close();
}

if can, where i want to add the code???
Thanks...

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 23:14:23

No

Perhaps a view you can

But it's such a hack

Why

Are you trying to save lines of code?

In any case, you should be calling a stored procedure

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:01:45

are they like related tables may be parent child relationship?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-21 : 22:18:49

quote:
Originally posted by visakh16

are they like related tables may be parent child relationship?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

i have a table name Customer which Consists Customer ID(auto generate),and information above).
besides that, i have another table name Car. In Car table, it also consists Customer ID and other column.
How i want to insert data to these two table at same time when i register????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:51:31

you will be doing batch insert or one insert at a time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:52:27

the best approach for this is to create a procedure where you can wrap both these inserts. you can use parameters in procedure to pass the values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:32:10

stored procedure is best way to reduce code and easy coding ..Pass parameters font front-end to store values...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-23 : 16:11:27

You might try using the OUTPUT clause. But, I believe that only works for two tables. For example:

DECLARE @T1 TABLE (ID INT IDENTITY(1,1), Val INT)
DECLARE @T2 TABLE (ID INT, Val INT)


INSERT @T1 (Val)
OUTPUT Inserted.ID, 99
INTO @T2
VALUES(1)

SELECT * FROM @T1
SELECT * FROM @T2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 00:26:02

Also you should be using SQL 2005 with compatibility level 90 or above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources