|
|
SQLCommand insert not working for dataset with more than one row. |
|
Author |
Message |
noppers
|
Posted: .NET Framework Data Access and Storage, SQLCommand insert not working for dataset with more than one row. |
Top |
I am trying to insert data into 2 tables, Order and Order_Item, in a transaction. Everything works fine if I only have 1 row in my objCartDT datatable. If I have only one row, the 2 tables are updated as expected. But if I have more than one row in the objCartDT datatable, something fails, and I can't figure out what (but I suspect it has to do with the params in conjunction with "cmdNewOrder_Items.ExecuteNonQuery();" and the looping I'm trying to do. Maybe not. I will paste the function that is called on button click (to submit the order). I am using a MS Sql server database.
public void btnSubmitOrder_Click(object sender, EventArgs e) { SqlConnection objConn; SqlTransaction objTran; SqlCommand cmdNewOrder; SqlCommand cmdNewOrder_Items; String strNewOrder; String strNewOrder_Item; //Create new Order_ID.// string varOrder_ID = Guid.NewGuid().ToString();
string varDateTime = Convert.ToString(DateTime.Now);
//Create SQL strings.// strNewOrder = "INSERT into [Order] (Order_ID, Create_Date, Ordered_By, Order_Status, " +
strNewOrder_Item = "INSERT INTO [ORDER_ITEM] ([ORDER_ITEM_ID], [ORDER_ID], [QTY_ORDERED], [DRUG_ID], [NOTES], [COST]) " +
string varOrder_Item_Id; int varQty_Ordered; string varDrug_Id; string varNotes; decimal varCost; //Create connection string objConn.// objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString); cmdNewOrder = new SqlCommand(strNewOrder, objConn);
cmdNewOrder_Items = new SqlCommand(strNewOrder_Item, objConn);
//Open the objConn connection.// objConn.Open(); //Begin the transaction using the connection object.// objTran = objConn.BeginTransaction(); //Assign the transaction to the commands.// cmdNewOrder.Transaction = objTran; cmdNewOrder_Items.Transaction = objTran; int counter = 0; try { //Insert new order row into Order table.// cmdNewOrder.ExecuteNonQuery();
//Insert rows from dataset objCartDT into the Order_Item table.// foreach (DataRow objDR in objCartDT.Rows) { counter++; varOrder_Item_Id = (string)objDR["Order_Item_Id"]; varQty_Ordered = (int)objDR["Qty_Ordered"]; varDrug_Id = (string)objDR["Drug_Id"]; varNotes = (string)objDR["Notes"]; varCost = (Decimal)objDR["Cost"];
cmdNewOrder_Items.ExecuteNonQuery();
}
objTran.Commit();
SuccessfulOrder = "1"; }
catch { objTran.Rollback();
SuccessfulOrder = "2"; }
finally { //Close connection.// objConn.Close(); //Clear out the session table objCartDT in the objDS DataSet.// objCartDT.Rows.Clear(); gvCart.DataSource = objCartDT; gvCart.DataBind(); }
Response.Redirect("neworder.aspx Successful=" + SuccessfulOrder + "&counter=" + counter); }
.NET Development23
|
|
|
|
|
noppers
|
Posted: .NET Framework Data Access and Storage, SQLCommand insert not working for dataset with more than one row. |
Top |
I figured it out.
I added 1 line at the end of the loop to clear out all the SqlParameter objects from the SqlParameterCollection:
cmdNewOrder_Items.Parameters.Clear();
So, the loop would look like this:
foreach (DataRow objDR in objCartDT.Rows)
{
counter++;
varOrder_Item_Id = (string)objDR["Order_Item_Id"];
varQty_Ordered = (int)objDR["Qty_Ordered"];
varDrug_Id = (string)objDR["Drug_Id"];
varNotes = (string)objDR["Notes"];
varCost = (Decimal)objDR["Cost"];
cmdNewOrder_Items.ExecuteNonQuery();
cmdNewOrder_Items.Parameters.Clear();
}
|
|
|
|
|
|
|