Insert some data into multiple tables using C#

Question

I am trying to insert some data into 2 tables at one time but the problem I am having with my code is... I am only able to insert one table at a time. If I comment out the first insert statement then the 2nd insert will work and vise-versa.

Here is my code

 SqlCommand cmd = new SqlCommand("select Name from MainTable where Name= '" + Name+ "'  ", sqlcon);
 SqlDataReader dr = cmd.ExecuteReader();
 if (dr.Read())
 {
    lblmsg.Text = "We are already have  this Name" + Name;
 }

 else
 {
    dr.Close();
    sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address)";
    sqlcmd.CommandText = "INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

    sqlcmd.Parameters.Clear();
    sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
    sqlcmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = Address;
    sqlcmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = Company
    sqlcmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = Address2;

    sqlcmd.Connection = sqlcon;
    sqlcmd.ExecuteNonQuery();

    DV_NameAdd.ChangeMode(DetailsViewMode.ReadOnly);
    sqlcon.Close();
 }
 sqlcon.Close();

Answer 1

You are setting the commandtext of the same SqlCommand

  sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address)";
  sqlcmd.CommandText = "INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

Only the last one will be executed because it has replaced the first one. Probably you need to execute the first one, clear the parameters collection, set the new text and then reexecute, or create a separate command

  sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address)";
  sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
  sqlcmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = Address;
  sqlCmd.ExecuteNonQuery();
  sqlCmd.Parameters.Clear();
  sqlcmd.CommandText = "INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";
  sqlcmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = Company
  sqlcmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = Address2;
  sqlCmd.ExecuteNonQuery();

BY the way, the first SELECT is very dangerous. You should use the parameters also for that command

Answer 2

You are overwriting your original SqlCommand and that is why only one works at a time. A SqlCommand only runs one command at a time. It doesn't accept additional versions of CommandText as your code seems to expect. If you want to run a two or more commands at the same time you have to create a single, semicolon-delimited CommandText, something like this:

sqlcmd.CommandText = 
"INSERT INTO Table1(Name, Address) VALUES(@Name, @Address);" + 
"INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

Note the semicolon (;) in between the two commands. Your parameter names are unique so you should be OK there.