Your code is rather confused. You load the results of the stored procedure into a
DataTable
, then execute the command again and read through the results you've already loaded one at a time.
The error is thrown because you have an active
SqlDataReader
reading rows from the connection, and you're trying to execute another command on the same connection.
Since you've already loaded the rows, you don't need the data reader at all.
if (e.CommandName == "DoPlus")
{
string PID = e.CommandArgument.ToString();
using (SqlConnection con = new SqlConnection(CS))
using (SqlCommand cmd = new SqlCommand("SP_getUserCartItem", con) { CommandType = CommandType.StoredProcedure })
{
cmd.Parameters.AddWithValue("@PID", PID);
cmd.Parameters.AddWithValue("@UserID", UserID);
con.Open();
DataTable dt = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dt);
}
foreach (DataRow row in dt.Rows)
{
int myQty = row.Field<int>("Qty");
int PQuantity = row.Field<int>("PQuantity");
if (myQty < PQuantity)
{
int updateQty = row.Field<int>("Qty");
using (SqlCommand myCmd = new SqlCommand("SP_UpdateCart", con) { CommandType = CommandType.StoredProcedure })
{
myCmd.Parameters.AddWithValue("@Quantity", updateQty + 1);
myCmd.Parameters.AddWithValue("@CartPID", PID);
myCmd.Parameters.AddWithValue("@UserID", UserID);
myCmd.ExecuteNonQuery();
}
BindProductCart();
BindCartNumber();
}
else if (myQty > PQuantity)
{
divQtyError2.Visible = true;
}
}
}
}