我想从表中插入多行到SqlServer但有问题 [英] I want to Insert Multiple Row from Table To SqlServer but Having a Problem
本文介绍了我想从表中插入多行到SqlServer但有问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
当我插入数据时，最后一次插入两次。（假设我插入3行，然后我的代码在数据库的最后一行插入4行插入两次）。
我的代码........
When I Insert the data the Last Low Insert Two Time.( suppose I insert 3 row then my code inserted 4 row in data base last row insert Two Time).
My code........
protected void btngo_Click(object sender, EventArgs e)
{
try
{
lblerror.Visible = false;
divform.Visible = true;
btnsave.Enabled = true;
tblinstallment.Visible = true;
int projectid = Convert.ToInt32(ddlproject.SelectedValue);
ViewState["Installment"] = txtinstallment.Text.Trim();
createtable();
}
catch (Exception ex) { }
}
protected void btnsave_Click(object sender, EventArgs e)
{
try
{
lblerror.Visible = false;
ViewState["ItemName"] = null;
#region DataTable
if (dttrue == true)
{
table = new DataTable("Installment");
DataColumn column1 = new DataColumn();
column1.DataType = System.Type.GetType("System.String");
column1.Caption = "ItemName";
column1.ColumnName = "ItemName";
DataColumn column2 = new DataColumn();
column2.DataType = System.Type.GetType("System.String");
column2.Caption = "Quantity";
column2.ColumnName = "Quantity";
DataColumn column3 = new DataColumn();
column3.DataType = System.Type.GetType("System.String");
column3.Caption = "UOM";
column3.ColumnName = "UOM";
DataColumn column4 = new DataColumn();
column4.DataType = System.Type.GetType("System.String");
column4.Caption = "Description";
column4.ColumnName = "Description";
table.Columns.Add(column1);
table.Columns.Add(column2);
table.Columns.Add(column3);
table.Columns.Add(column4);
dttrue = false;
}
#endregion
DataRow row;
int count = int.Parse(ViewState["Installment"].ToString());
for (int i = 1; i <= count; i++)
{
DropDownList textdays;
int uom = 0;
int ItemName = 0;
DropDownList textbp = (DropDownList)tblinstallment.FindControl("txtbasic" + i.ToString());
if (textbp.SelectedIndex != 0)
{
ItemName = Convert.ToInt32(textbp.SelectedIndex);
}
else
{
lblerror.Text = "Please select Item Name";
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
return;
}
ViewState["ItemName"] = ItemName;
TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;
textdays = (DropDownList)tblinstallment.FindControl("txtdays" + i.ToString());
if (textdays.SelectedIndex != 0)
{
uom = Convert.ToInt32(textdays.SelectedIndex);
}
else
{
lblerror.Text = "Please select UOM";
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
return;
}
TextBox textIFM = (TextBox)tblinstallment.FindControl("txtifm" + i.ToString());
quantity = textIFM.Text.Trim();
TextBox textinststage = (TextBox)tblinstallment.FindControl("txtpaymentdesc" + i.ToString());
string Description = textinststage.Text.Trim();
row = table.NewRow();
row["ItemName"] = ItemName;
row["UOM"] = uom;
row["Quantity"] = quantity;
row["Description"] = Description;
table.Rows.Add(row);
}
ViewState["ItemName"] = null;
DataSet ds = new DataSet();
ds.Tables.Add(table);
ViewState["Table"] = ds;
TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;
SaveRequisition();
//Response.Redirect("~/Purchase/Requisition.aspx");
}
catch (Exception ex)
{
lblerror.Text = ex.ToString();
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
}
}
public void SaveRequisition()
{
try
{
if (ViewState["Table"] != null)
{
ds = (DataSet)ViewState["Table"];
int count = ds.Tables[0].Rows.Count;
objConnection = commonfunctions.GetConnection();
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
myTransaction = objConnection.BeginTransaction();
myCommand = new SqlCommand("sp_insert_Requisition", objConnection, myTransaction);
myCommand.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < count; i++)
{
myCommand.Parameters.Clear();
SqlParameter param1 = new SqlParameter("@projectid", SqlDbType.Int, 4);
param1.Value = Convert.ToInt32(ddlproject.SelectedValue);
myCommand.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@DeptId", SqlDbType.Int, 4);
param2.Value = Convert.ToInt32(ddlpaymentplan.SelectedValue);
myCommand.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("@ItemName", SqlDbType.Int, 4);
param3.Value = Convert.ToDecimal(ds.Tables[0].Rows[i]["ItemName"].ToString());
myCommand.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter("@Quantity", SqlDbType.Decimal, 9);
param4.Value = Convert.ToDecimal(ds.Tables[0].Rows[i]["Quantity"].ToString());
myCommand.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter("@UOM", SqlDbType.Int, 4);
param5.Value = Convert.ToDecimal(ds.Tables[0].Rows[i]["UOM"].ToString());
myCommand.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter("@description", SqlDbType.VarChar, 200);
param6.Value = (ds.Tables[0].Rows[i]["Description"].ToString());
myCommand.Parameters.Add(param6);
SqlParameter param7 = new SqlParameter("@Submitedby", SqlDbType.VarChar, 50);
param7.Value = TextBox1.Text;
myCommand.Parameters.Add(param7);
SqlParameter param8 = new SqlParameter("@Approvedby", SqlDbType.VarChar, 50);
param8.Value = TextBox2.Text;
myCommand.Parameters.Add(param8);
myCommand.ExecuteNonQuery();
}
myCommand.ExecuteNonQuery();
myTransaction.Commit();
myCommand.Dispose();
lblerror.Text = "Data save successfully"; ;
lblerror.ForeColor = Color.Blue;
lblerror.Visible = true;
divform.Visible = false;
txtinstallment.Text = "";
ViewState["Table"] = null;
objConnection.Close();
ddlproject.SelectedIndex = 0;
ddlpaymentplan.SelectedIndex = 0;
//bindGrid();
}
}
catch (Exception ex)
{
btnsave.Enabled = true;
myTransaction.Rollback();
objConnection.Close();
lblerror.Text = ex.Message;
lblerror.Visible = true;
}
}
推荐答案
像这样使用
确保数据表列名和表列名应该相同
SqlConnection Con = new SqlConnection（@Connectionstring）;
Con.Open（）;
DataRow [] rowArray = dtright.Select（）; // dtright是数据表
using（SqlBulkCopy bulkCopy = new SqlBulkCopy（Con） ）
{
bulkCopy.DestinationTableName =dbo.FileUpload;
试试
{
bulkCopy.WriteToServer（rowArray）;
MessageBox.Show（成功插入......！）;
}
catch（Exception ex）
{
MessageBox.Show（Operation faild）;
}
}
Con.Close（）;
Use like this
make sure that datatable column name and table column name should be same
SqlConnection Con = new SqlConnection(@"Connectionstring");
Con.Open();
DataRow[] rowArray = dtright.Select();// dtright is datatable
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Con))
{
bulkCopy.DestinationTableName = "dbo.FileUpload";
try
{
bulkCopy.WriteToServer(rowArray);
MessageBox.Show("Successfully inserted...!");
}
catch (Exception ex)
{
MessageBox.Show("Operation faild");
}
}
Con.Close();
试试这个
protected void btngo_Click（object sender，EventArgs e）
{
试试
{
lblerror.Visible = false;
divform.Visible = true;
btnsave.Enabled = true;
tblinstallment.Visible = true;
int projectid = Convert.ToInt32（ddlproject.SelectedValue） ;
ViewState [Installment] = txtinstallment.Text.Trim（）;
createtable（ ）;
}
catch（exception ex）{}
}
protected void btnsave_Click（object sender， EventArgs e）
{
试试
{
lblerror.Visible = false;
ViewState [我temName] = null;
#region DataTable
if（dttrue == true）
{
table = new DataTable（分期付款）;
DataColumn column1 = new DataColumn（）;
column1 .DataType = System.Type.GetType（System.String）;
column1.Caption =ItemName;
column1.ColumnName =ItemName;
DataColumn column2 = new DataColumn（）;
column2.DataType = System.Type.GetType（System.String）;
column2.Caption =Quantity;
column2.ColumnName =Quantity;
DataColumn column3 = new DataColumn （）;
column3.DataType = System.Type.GetType（System.String）;
column3.Caption =UOM;
column3.ColumnName =UOM;
DataColumn column4 = new DataColumn（）;
column4.DataType = System.Type.GetType（System.String）;
column4.Caption =Description;
column4.ColumnName =描述;
table.Columns.Add （column1）;
table.Columns.Add（column2）;
table.Columns.Add（column3）;
table.Columns。添加（第4列）;
dttrue = false;
}
#endregion
DataRow行;
int count = int.Parse（ViewState [Installment]。ToString（））;
for（int i = 1;我< =计数; i ++）
{
DropDownList textdays;
int uom = 0;
int ItemName = 0;
DropDownList textbp =（DropDownList）tblinstallment.FindControl（txtbasic+ i.ToString（））;
if（textbp.SelectedIndex！= 0）
{
ItemName = Convert.ToInt32（textbp .SelectedIndex）;
}
其他
{
lblerror.Text =请选择项目名称;
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
return;
}
ViewSta te [ItemName] = ItemName;
TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;
textdays =（DropDownList）tblinstallment.FindControl（txtdays+ i.ToString（））;
if（textdays.SelectedIndex！= 0）
{
uom = Convert.ToInt32（textdays.SelectedIndex）;
}
else
{
lblerror.Text =请选择UOM;
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
返回;
}
TextBox textIFM =（TextBox）tblinstallment.FindControl（txtifm+ i.ToString（））;
quantity = textIFM.Text.Trim（）;
TextBox textinststage =（TextBox）tblinstallment.FindControl（txtpaymentdesc+ i.ToString（））;
string描述= textinststage.Text.Trim（）;
row = table.NewRow（）;
row [ItemName] = ItemName;
行[UOM] = uom;
行[数量] =数量;
行[描述] =说明;
table.Rows.Add（row）;
}
ViewState [ItemName] = null;
DataSet ds = new DataSet（）;
ds.T ables.Add（table）;
ViewState [Table] = ds;
TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;
SaveRequisition（表格）;
//Response.Redirect(\"~/Purchase/Requisition。 aspx）;
}
catch（exception ex）
{
lblerror.Text = ex.ToString （）;
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
}
}
public void SaveRequisition（DataTable dt）
{
try
{
if（ViewState [Table]！= null）
{
ds =（DataSet）ViewState [Table];
int count = ds.Tables [0] .Rows.Count;
objConnection = commonfunctions.GetConnection（）;
if（objConnection.State！= ConnectionState.Open）
{
objConnection.Open（）;
}
DataRow [] rowArray = dtright.Select（）; // dtright是数据表
使用（SqlBulkCopy bulkCopy = new SqlBulkCopy（Con））
{
bulkCopy.DestinationTableName =YourTableName;
尝试
{
bulkCopy.WriteToServer（rowArray）;
lblerror.Text =数据保存成功; ;
lblerror.ForeColor = Color.Blue;
lblerror.Visible = true;
divform.Visible = false;
txtinstallment.Text =;
ViewState [Table] = null;
objConnection.Close（）;
ddlproject .SelectedIndex = 0;
ddlpaymentplan.SelectedIndex = 0;
}
catch（exception ex）
{
btnsave.Enabled = true;
myTransaction.Rollback（）;
objConnection.Close（）;
lblerror.Text = ex.Message;
lblerror.Visible = true;
}
}
}
}
}
try this
protected void btngo_Click(object sender, EventArgs e)
{
try
{
lblerror.Visible = false;
divform.Visible = true;
btnsave.Enabled = true;
tblinstallment.Visible = true;
int projectid = Convert.ToInt32(ddlproject.SelectedValue);
ViewState["Installment"] = txtinstallment.Text.Trim();
createtable();
}
catch (Exception ex) { }
}
protected void btnsave_Click(object sender, EventArgs e)
{
try
{
lblerror.Visible = false;
ViewState["ItemName"] = null;
#region DataTable
if (dttrue == true)
{
table = new DataTable("Installment");
DataColumn column1 = new DataColumn();
column1.DataType = System.Type.GetType("System.String");
column1.Caption = "ItemName";
column1.ColumnName = "ItemName";
DataColumn column2 = new DataColumn();
column2.DataType = System.Type.GetType("System.String");
column2.Caption = "Quantity";
column2.ColumnName = "Quantity";
DataColumn column3 = new DataColumn();
column3.DataType = System.Type.GetType("System.String");
column3.Caption = "UOM";
column3.ColumnName = "UOM";
DataColumn column4 = new DataColumn();
column4.DataType = System.Type.GetType("System.String");
column4.Caption = "Description";
column4.ColumnName = "Description";
table.Columns.Add(column1);
table.Columns.Add(column2);
table.Columns.Add(column3);
table.Columns.Add(column4);
dttrue = false;
}
#endregion
DataRow row;
int count = int.Parse(ViewState["Installment"].ToString());
for (int i = 1; i <= count; i++)
{
DropDownList textdays;
int uom = 0;
int ItemName = 0;
DropDownList textbp = (DropDownList)tblinstallment.FindControl("txtbasic" + i.ToString());
if (textbp.SelectedIndex != 0)
{
ItemName = Convert.ToInt32(textbp.SelectedIndex);
}
else
{
lblerror.Text = "Please select Item Name";
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
return;
}
ViewState["ItemName"] = ItemName;
TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;
textdays = (DropDownList)tblinstallment.FindControl("txtdays" + i.ToString());
if (textdays.SelectedIndex != 0)
{
uom = Convert.ToInt32(textdays.SelectedIndex);
}
else
{
lblerror.Text = "Please select UOM";
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
return;
}
TextBox textIFM = (TextBox)tblinstallment.FindControl("txtifm" + i.ToString());
quantity = textIFM.Text.Trim();
TextBox textinststage = (TextBox)tblinstallment.FindControl("txtpaymentdesc" + i.ToString());
string Description = textinststage.Text.Trim();
row = table.NewRow();
row["ItemName"] = ItemName;
row["UOM"] = uom;
row["Quantity"] = quantity;
row["Description"] = Description;
table.Rows.Add(row);
}
ViewState["ItemName"] = null;
DataSet ds = new DataSet();
ds.Tables.Add(table);
ViewState["Table"] = ds;
TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;
SaveRequisition(table);
//Response.Redirect("~/Purchase/Requisition.aspx");
}
catch (Exception ex)
{
lblerror.Text = ex.ToString();
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
}
}
public void SaveRequisition(DataTable dt)
{
try
{
if (ViewState["Table"] != null)
{
ds = (DataSet)ViewState["Table"];
int count = ds.Tables[0].Rows.Count;
objConnection = commonfunctions.GetConnection();
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
DataRow[] rowArray = dtright.Select();// dtright is datatable
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Con))
{
bulkCopy.DestinationTableName = "YourTableName";
try
{
bulkCopy.WriteToServer(rowArray);
lblerror.Text = "Data save successfully"; ;
lblerror.ForeColor = Color.Blue;
lblerror.Visible = true;
divform.Visible = false;
txtinstallment.Text = "";
ViewState["Table"] = null;
objConnection.Close();
ddlproject.SelectedIndex = 0;
ddlpaymentplan.SelectedIndex = 0;
}
catch (Exception ex)
{
btnsave.Enabled = true;
myTransaction.Rollback();
objConnection.Close();
lblerror.Text = ex.Message;
lblerror.Visible = true;
}
}
}
}
}
这篇关于我想从表中插入多行到SqlServer但有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
