将一些数据使用C＃多个表 [英] Insert some data into multiple tables using C#

问题描述

我试图在同一时间插入一些数据表2，但我有我的代码有问题是...我只能够一次插入一个表格。如果我注释掉的第一个插入语句，然后第二个插入会工作，反之亦然。

下面是我的代码

 的SqlCommand CMD =新的SqlCommand（从MainTable选择名在名称='+姓名+'，sqlcon）; 
 SqlDataReader的博士= cmd.ExecuteReader（）; 
如果（dr.Read（））
 {
 lblmsg.Text =我们已经有这样的名称+姓名; 
} 
 
，否则
 {
 dr.Close（）; 
 sqlcmd.CommandText =INSERT INTO表1（姓名，地址）VALUES（@Name，@address）; 
 sqlcmd.CommandText =INSERT INTO表2（公司地址2）VALUES（@Company，@地址2）; 
 
 sqlcmd.Parameters.Clear（）; 
 sqlcmd.Parameters.Add（@名，SqlDbType.VarChar）.value的=名称; 
 sqlcmd.Parameters.Add（@地址，SqlDbType.VarChar）.value的=地址; 
 sqlcmd.Parameters.Add（@公司，SqlDbType.VarChar）.value的公司= 
 sqlcmd.Parameters.Add（@地址2，SqlDbType.VarChar）.value的=地址2; 
 
 sqlcmd.Connection = sqlcon; 
 sqlcmd.ExecuteNonQuery（）; 
 
 DV_NameAdd.ChangeMode（DetailsViewMode.ReadOnly）; 
 sqlcon.Close（）; 
} 
 sqlcon.Close（）; 
  

解决方案

您要设置相同的SqlCommand的CommandText

  sqlcmd.CommandText =INSERT INTO表1（姓名，地址）VALUES（@Name，@address）; 
 sqlcmd.CommandText =INSERT INTO表2（公司地址2）VALUES（@Company，@地址2）; 
  

只是因为它已经取代了第一个最后一个将被执行。
也许你需要执行的第一个，清除参数采集，设定新的文本，然后重新执行，或创建一个单独的命令

  sqlcmd.CommandText =INSERT INTO表1（姓名，地址）VALUES（@Name，@address）; 
 sqlcmd.Parameters.Add（@名，SqlDbType.VarChar）.value的=名称; 
 sqlcmd.Parameters.Add（@地址，SqlDbType.VarChar）.value的=地址; 
 sqlCmd.ExecuteNonQuery（）; 
 sqlCmd.Parameters.Clear（）; 
 sqlcmd.CommandText =INSERT INTO表2（公司地址2）VALUES（@Company，@地址2）; 
 sqlcmd.Parameters.Add（@公司，SqlDbType.VarChar）.value的公司= 
 sqlcmd.Parameters.Add（@地址2，SqlDbType.VarChar）.value的=地址2; 
 sqlCmd.ExecuteNonQuery（）; 
  

顺便说一句，第一个SELECT是很危险的。您应该使用的参数也该命令

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();

解决方案

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

这篇关于将一些数据使用C＃多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！