SQLCommand insert not working for dataset with more than one row.

[Noppers]

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 dataset. If I have only one row, the 2 tables are updated as expected. But if I have more than one row in the objCartDT, 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, " +
"Order_Type, Dept_ID, Loc_ID) VALUES (@Order_ID, @Create_Date, @Ordered_By, @Order_Status, " +
"@Order_Type, @Dept_ID, @Loc_ID)";

strNewOrder_Item = "INSERT INTO [ORDER_ITEM] ([ORDER_ITEM_ID], [ORDER_ID], [QTY_ORDERED], [DRUG_ID], [NOTES], [COST]) " +
"VALUES (@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.ConnectionStrin gs["myConnStr"].ConnectionString);

cmdNewOrder = new SqlCommand(strNewOrder, objConn);
cmdNewOrder.Parameters.AddWithValue("@Order_ID", varOrder_ID); //varOrder_ID
cmdNewOrder.Parameters.AddWithValue("@Create_Date" , varDateTime);
cmdNewOrder.Parameters.AddWithValue("@Ordered_By", userID); //get userid
cmdNewOrder.Parameters.AddWithValue("@Order_Status ", "Unfulfilled");
cmdNewOrder.Parameters.AddWithValue("@Order_Type", "Order");
cmdNewOrder.Parameters.AddWithValue("@Dept_ID", DropDownList1.SelectedValue);
cmdNewOrder.Parameters.AddWithValue("@Loc_ID", DropDownList2.SelectedValue);

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.Parameters.AddWithValue("@ORDER_ ITEM_ID", varOrder_Item_Id);
cmdNewOrder_Items.Parameters.AddWithValue("@ORDER_ ID", varOrder_ID);
cmdNewOrder_Items.Parameters.AddWithValue("@QTY_OR DERED", varQty_Ordered);
cmdNewOrder_Items.Parameters.AddWithValue("@DRUG_I D", varDrug_Id);
cmdNewOrder_Items.Parameters.AddWithValue("@NOTES" , varNotes);
cmdNewOrder_Items.Parameters.AddWithValue("@COST", varCost);


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

}

[Noppers]

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.Parameters.AddWithValue("@ORDER_ ITEM_ID", varOrder_Item_Id);
cmdNewOrder_Items.Parameters.AddWithValue("@ORDER_ ID", varOrder_ID);
cmdNewOrder_Items.Parameters.AddWithValue("@QTY_OR DERED", varQty_Ordered);
cmdNewOrder_Items.Parameters.AddWithValue("@DRUG_I D", varDrug_Id);
cmdNewOrder_Items.Parameters.AddWithValue("@NOTES" , varNotes);
cmdNewOrder_Items.Parameters.AddWithValue("@COST", varCost);


cmdNewOrder_Items.ExecuteNonQuery();

cmdNewOrder_Items.Parameters.Clear();

}