Updating multiple tables

Petro Tiburcio

13y1.8kVisual Basic .NET

I have 2 tables namely Profile and Info.

My table looked like this:

PROFILE

| p_Id | FirstName | LastName |

INFO

| 1 | 1 | 3 | 2 |

I'm retrieving values from those tables by displaying them in textboxes, my select query is:

SELECT p.p_Id, p.FirstName, p.LastName, i.*, (SELECT pp.FirstName+' '+pp.LastName FROM Profile pp WHERE pp.p_Id=i.childsID) AS child, (SELECT pp.FirstName+' '+pp.LastName FROM Profile pp WHERE pp.p_Id=i.fathersID) AS father, (SELECT pp.FirstName+' '+pp.LastName FROM Profile pp WHERE pp.p_Id=i.mothersID) AS mother FROM Info i INNER JOIN Profile p ON p.p_Id=i.childsID

no problem with select, I'm able to display values on textboxes, but the problem is, I can't update them, I have tried so far this:

using (SqlCommand cmd = con.CreateCommand())     {         con.Open();         cmd.CommandText = @"UPDATE Profile SET FirstName=@fname, LastName=@lname WHERE p_Id = @pid;                             UPDATE Info SET childsID=@child, fathersID=@father, mothersID=@mother, Country=@country WHERE I_Id = @iid;";          cmd.Parameters.Add(new SqlParameter("@fname", txtfname.Text));         cmd.Parameters.Add(new SqlParameter("@lname", txtlname.Text));         cmd.Parameters.Add(new SqlParameter("@child", txtchild.Text));         cmd.Parameters.Add(new SqlParameter("@father", txtfather.Text));         cmd.Parameters.Add(new SqlParameter("@mother", txtmother.Text));         cmd.Parameters.Add(new SqlParameter("@country", txtcountry.Text));         cmd.Parameters.Add(new SqlParameter("@pid", txtpid.Text));         cmd.Parameters.Add(new SqlParameter("@iid", txtiid.Text));         cmd.ExecuteNonQuery();         Response.Write("alert('DATA UPDATED')");     }

I'm using c# and Asp.net Thanks in advance :) God Bless

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Parveen Siwach13y ago
You can try this way:

SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
        con.Open();
        using (SqlCommand cmd = con.CreateCommand())
        {
            con.Open();
            cmd.CommandText = @"UPDATE Profile SET FirstName=@fname, LastName=@lname WHERE p_Id = @pid; ";
            cmd.Parameters.Add(new SqlParameter("@fname", txtfname.Text));
            cmd.Parameters.Add(new SqlParameter("@lname", txtlname.Text));
            cmd.Parameters.Add(new SqlParameter("@pid", txtpid.Text));
            cmd.ExecuteNonQuery();

            cmd.CommandText = @"UPDATE Info SET childsID=@child, fathersID=@father, mothersID=@mother, Country=@country WHERE I_Id = @iid;";
            cmd.Parameters.Add(new SqlParameter("@child", txtchild.Text));
            cmd.Parameters.Add(new SqlParameter("@father", txtfather.Text));
            cmd.Parameters.Add(new SqlParameter("@mother", txtmother.Text));
            cmd.Parameters.Add(new SqlParameter("@country", txtcountry.Text));
            cmd.Parameters.Add(new SqlParameter("@iid", txtiid.Text));
            cmd.ExecuteNonQuery();

            Response.Write("alert('DATA UPDATED')");
        }

Note: For these type of scenerios SqlTransactions must be used.

e.g:

SqlTransaction myTrans = con.BeginTransaction();
cmd.Transaction = myTrans;
//For Commit
myTrans.Commit();
//For Rollback
myTrans.Rollback();

Hope this will solve your problem.
Petro Tiburcio13y ago
Hello Shankar,

I have used stored procedure for my data entry page. Below is my code:

SqlConnection con = new SqlConnection("Data Source=petro-PC\\SQLEXPRESS;Initial Catalog=dbUsers;Integrated Security=True");
SqlCommand cmd = new SqlCommand("qryINSERT", con);
cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.AddWithValue("@Lname", txtlname.Text);
cmd.Parameters.AddWithValue("@Mname", txtmname.Text);
cmd.Parameters.AddWithValue("@Fname", txtfname.Text);

cmd.CommandType = System.Data.CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();

and in my stored procedure I have this:

ALTER PROCEDURE qryINSERT
@Fname Varchar(50),
@Mname Varchar(50),
@Lname Varchar(50)

AS
BEGIN
INSERT INTO Profile ( Fname, Mname, Lname)
VALUES (@Fname, @Mname, @Lname)

END

But I just don't know how to do it with multiple tables update, I know if it is just one table. I have tried bunch of update queries but nothing helps. :(
Shankar M13y ago

Hi Pertro,

You can create a stored Procedure that contains the update statements that is created as a
Explicit Transaction using BEGIN TRANSACTION AND COMMIT TRANSACTION.

And, you can make a call to the Stored Procedure using the ExecuteNonQuery() Method of Command object.

Thanks, Shankar M