Saving Data into two table at a time.

Question

5.00/5 (1 vote)

See more:

Hello Friends,

I Have Two Database Tables

Table A
StudID(it is Autogenerated by Setting Identity), Name, Contact, etc.

Table B
ID StudID Address City...

I want to store StudentName contact etc First then get the StudID and Save Data into B with retrived StudID Address into one Transaction.

Posted 27-Mar-11 1:26am

nitin bhoyate

Updated 27-Mar-11 5:19am

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 27-Mar-11 11:20am

Edited for Grammar and Corrected Column Header Names.

nitin bhoyate 28-Mar-11 8:35am

Thanks

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Costica U · Answer 1 · 2011-03-27T02:27:00

Solution 1

1. Insert into TableA
INSERT INTO TableA(...)VALUES(...)

2. Store last generated id
declare @StuID int; set @StuID=SCOPE_IDENTITY(); See SCOPE_IDENTITY()[^]

3 Insert into TableB
INSERT INTO TableB(StuID,...)VALUES(@StuID,...)

Posted 27-Mar-11 2:27am

Costica U

Updated 27-Mar-11 2:39am

v3

Comments

Dalek Dave 27-Mar-11 11:20am

Seems reasonable.

nitin bhoyate 28-Mar-11 8:26am

can you provide sample code..

nitin bhoyate 28-Mar-11 8:34am

Take a look at my code..

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MRProjectConnectionString"].ConnectionString);
SqlCommand cmd;
con.Open();
string sql = "Declare @ChemistCode int;";
sql = sql + "Insert into Chemist(ChemistName,Qualification,Birthdate,Email,Website ,MobNo,HqName,Anniversary,Hobbies,other ,Submittedby,Status)Values(@ChemistName,@Qualification,@Birthdate,@Email,@Website ,@MobNo,@HqName,@Anniversary,@Hobbies,@other ,@Submittedby,@Status)";
sql = sql + " SET @ChemistCode = SCOPE_IDENTITY();";
sql = sql + "Insert into ChemistOfficeContactDetails(ChemistCode,AddressLine1,AddressLine2,City ,State,Landline,Fax)Values(@ChemistCode,@AddressLine1,@AddressLine2,@City ,@State,@Landline,@Fax)";
cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@ChemistName", SqlDbType.NVarChar).Value = TextBoxName.Text;
cmd.Parameters.Add("@Qualification", SqlDbType.NVarChar).Value = TextBoxQualification.Text;
cmd.Parameters.Add("@Birthdate", SqlDbType.SmallDateTime).Value = TextBoxBirthdate.Text;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = TextBoxEmail.Text;
cmd.Parameters.Add("@Website", SqlDbType.NVarChar).Value = TextBoxWebsite.Text;
cmd.Parameters.Add("@MobNo", SqlDbType.NVarChar).Value = TextBoxMobileNo.Text;
cmd.Parameters.Add("@HqName", SqlDbType.NVarChar).Value = DropDownListHqName.Text;
cmd.Parameters.Add("@Anniversary", SqlDbType.SmallDateTime).Value = TextBoxAnniversary.Text;
cmd.Parameters.Add("@Hobbies", SqlDbType.NVarChar).Value = TextBoxHobbies.Text;
cmd.Parameters.Add("@other", SqlDbType.NVarChar).Value = TextBoxOther.Text;
cmd.Parameters.Add("@Submittedby", SqlDbType.NVarChar).Value = "1";
cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = "P";

cmd.Parameters.Add("@AddressLine1", SqlDbType.NVarChar).Value = "A";
cmd.Parameters.Add("@AddressLine2", SqlDbType.NVarChar).Value = "A";
cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = "A";
cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = "A";
cmd.Parameters.Add("@Landline", SqlDbType.NVarChar).Value = "A";
cmd.Parameters.Add("@Fax", SqlDbType.NVarChar).Value = "A";
cmd.ExecuteNonQuery();
con.Close();

ERROR:::

You Must Declare ChemistCOde

Costica U 28-Mar-11 18:21pm

I don't see any problem.
Stop the program after line: cmd = new SqlCommand(sql, con);
and see how the cmd.CommandText looks.

Do you have column ChemistOfficeContactDetails.ChemistCode?

Hadi Basiri · Answer 2 · 2011-03-27T08:17:00

Solution 2

after first insert you must use of SCOPE_IDENTITY().

Posted 27-Mar-11 8:17am

Hadi Basiri

Brijesh Kr · Answer 3 · 2011-03-27T19:28:00

Solution 4

Just use a after trigger on table on A, You have to retieve StudID, and insert into Table B in that trigger only.

Posted 27-Mar-11 19:28pm

Brijesh Kr

Wendelius · Answer 4 · 2011-03-27T10:14:00

Solution 3

One alternative option is to define OUTPUT[^] clause and use the returning values in the next statements

Posted 27-Mar-11 10:14am

Wendelius

Updated 27-Mar-11 10:15am

v2