Executing insert even when there's an error

Question

I'm inserting data into 2 tables from a textbox and gridview; however, when I get an error, data is still being inserted into one of the tables. What I want is once I get the error then the data shouldn't insert at all.

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        foreach (DataRow dr in dt.Rows)
        {
            cmd.Parameters.AddWithValue("@loadSheetNum", lblSheet.Text);
            cmd.Parameters.AddWithValue("@invoiceNum", dr["Invoice #"]);
            cmd.Parameters.AddWithValue("@invoiceQty", dr["Invoice Qty"]);
            cmd.Parameters.AddWithValue("@custName", dr["Customer Name"]);
            cmd.Parameters.AddWithValue("@invoiceWeight", dr["Total Invoice Weight"]);
            cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = 1;
        }
        cmd.ExecuteNonQuery();
    }
    using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection))
    {
        for (int i = 0; i < ContentPlaceHolder1.Controls.Count; i++)
        {

            Control ctrl = ContentPlaceHolder1.Controls[i];
            if (ctrl is TextBox)
            {
                TextBox txt = (TextBox)ctrl;

                //txt.TextMode = System.Web.UI.WebControls.TextBoxMode.Number;
                value = txt.Text;
                int parsedValue;
                if (!int.TryParse(value, out parsedValue))
                {
                    lblError.Text = "Please enter only numeric values for number";
                    return;
                }
                else
                {
                    comm.CommandType = CommandType.StoredProcedure;

                    comm.Parameters.AddWithValue("@loadSheetNum", lblSheet.Text);
                    comm.Parameters.Add("@Number", SqlDbType.NVarChar).Value = value;
                }
            }
            comm.ExecuteNonQuery();
        }
    }
}

Answer 1

There are many problems in your code. The first one is the AddWithValue inside the for loop. In this way you continue to add parameters to the command at each loop but only the first set of parameters required by your stored procedure will be used, resulting in an insert of the same values at each loop.

So you should change both loops to handle the parameters in this way:

using (SqlConnection connection = new SqlConnection(...))
{
    connection.Open();
    using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@loadSheetNum", SqlDbType.NVarChar);
        ... add all the other parameters, but don't give them a value
        foreach (DataRow dr in dt.Rows)
        {
            cmd.Parameters["@loadSheetNum"].Value = lblSheet.Text; 
            ... set the value to all other parameters
            cmd.ExecuteNonQuery();
        }
    }

You should do the same for the other command, but when you are sure about the value to insert then call immediately the ExecuteNonQuery

using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection))
{
      comm.CommandType = CommandType.StoredProcedure;
      // This never changes inside the loop so keep it outside
      comm.Parameters.Add("@loadSheetNum", SqlDbType.NVarChar).Value = lblSheet.Text);

      // This changes inside the loop so set the value inside the loop
      comm.Parameters.Add("@Number", SqlDbType.NVarChar)
      for (int i = 0; i < ContentPlaceHolder1.Controls.Count; i++)
      {
            Control ctrl = ContentPlaceHolder1.Controls[i];
            if (ctrl is TextBox)
            {
                TextBox txt = (TextBox)ctrl;
                value = txt.Text;
                int parsedValue;
                if (!int.TryParse(value, out parsedValue))
                {
                    lblError.Text = "Please enter only numeric values for number";
                    return;
                }
                else
                {
                     comm.Parameters["@Number"] = value;
                     comm.ExecuteNonQuery();
                }
           }
      }
}

It is importat to know if you want to avoid the whole insertion code in case you have a non valid number in your inputs. In this case you need to apply a Transaction to your inserts and confirm or rollback the inserts in case of errors

Just add this

using (SqlConnection connection = new SqlConnection(...))
{
    connection.Open();
    using (SqlTransaction ts = connection.BeginTransaction())
    {
        using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection, ts))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            .......
        }
        using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection, ts))
        {
            .....
            if (!int.TryParse(value, out parsedValue))
            {
                lblError.Text = "Please enter only numeric values for number";
                 ts.Rollback();
                 return;
            }
            .....
        }
        // Before exiting from the SqlConnection using block call the 
        ts.Confirm();
    }
}

Answer 2

You could accomplish not committing changes from your first command when there's an issue in your second command through the use of transactions.

Here's an example:

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    connection.Open();
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection, transaction))
        {
            // First command
        }
        using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection, transaction))
        {
            // Second command
            // .. omitted
            if(!int.TryParse(value, out parsedValue)){
                lblError.Text = "Please enter only numeric values for number";
                return; // Since we haven't committed the transaction, it will be rolled back when disposed.
            }
            // .. omitted
        }
        transaction.Commit(); // Both commands execute without error, commit the transaction.
    }
}