Simultaneously Insert Multiple Rows Into Database

Each survey has multiple questions (10) and each question has it's own rating. At the moment I can only insert a rating for each question one at a time. I'm trying to create one form so that I can add a rating to each question at the same time.

The issue I'm running into is that the form only enters the rating for the first question and runs into an error at the second instance of: 'throw new ApplicationException("Data error.");'

Each question needs to keep the selected survey ID to identify which survey it belongs to.

===== Code Behind =====

        protected void AddRatingBTN_Click(object sender, EventArgs e)
        {

            if (DropDownListSurvey.SelectedValue == "Select")
            {
                //Label1.Text = "hi";
                return;
            }

            string connectionString = WebConfigurationManager.ConnectionStrings["DBConnectionString1"].ConnectionString;


            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("InsertRating", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@Rating_ID", SqlDbType.Int, 7));
            cmd.Parameters["@Rating_ID"].Direction = ParameterDirection.Output;


            cmd.Parameters.Add(new SqlParameter("@Question_ID", SqlDbType.Int));
            cmd.Parameters["@Question_ID"].Value = 1000;

            cmd.Parameters.Add(new SqlParameter("@Survey_ID", SqlDbType.Int));
            cmd.Parameters["@Survey_ID"].Value = Convert.ToInt32(DropDownListSurvey.SelectedValue.ToString());

            cmd.Parameters.Add(new SqlParameter("@Score", SqlDbType.Char, 5));
            cmd.Parameters["@Score"].Value = Question_1.Text;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
               // txtRating_ID.Text = Convert.ToString(cmd.Parameters["@Rating_ID"].Value);

            }
            catch (SqlException err)
            {
                // Replace the error with something less specific.
                // You could also log the error now.
                throw new ApplicationException("Data error.");
            }
            finally
            {
                con.Close();
            }

            cmd.Parameters.Add(new SqlParameter("@Rating_ID", SqlDbType.Int, 7));
            cmd.Parameters["@Rating_ID"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add(new SqlParameter("@Question_ID", SqlDbType.Int));
            cmd.Parameters["@Question_ID"].Value = 1001;

            cmd.Parameters.Add(new SqlParameter("@Score", SqlDbType.Char, 5));
            cmd.Parameters["@Score"].Value = Question_2.Text;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                // txtRating_ID.Text = Convert.ToString(cmd.Parameters["@Rating_ID"].Value);

            }
            catch (SqlException err)
            {
                // Replace the error with something less specific.
                // You could also log the error now.
                throw new ApplicationException("Data error.");
            }
            finally
            {
                con.Clo开发者_如何转开发se();
            }

            cmd.Parameters.Add(new SqlParameter("@Rating_ID", SqlDbType.Int, 7));
            cmd.Parameters["@Rating_ID"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add(new SqlParameter("@Question_ID", SqlDbType.Int));
            cmd.Parameters["@Question_ID"].Value = 1002;

            cmd.Parameters.Add(new SqlParameter("@Score", SqlDbType.Char, 5));
            cmd.Parameters["@Score"].Value = Question_3.Text;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                // txtRating_ID.Text = Convert.ToString(cmd.Parameters["@Rating_ID"].Value);

            }
            catch (SqlException err)
            {
                // Replace the error with something less specific.
                // You could also log the error now.
                throw new ApplicationException("Data error.");
            }
            finally
            {
                Response.Redirect(Request.RawUrl);
                con.Close();
            }
        }
    }
}

This code brings back some old memories, not really great ones unfortunately. Anyway, I think the problem is that you're adding the parameters multiple times to the same command object. Your first execute works because you have the correct number of parameters. In the prep for the second run, you're adding them again, but the cmd still has the old parameters still there. If you remove the .Add calls after the first time it should work.

You should look into more modern ways of handling data access. Check out LINQ-to-SQL at the very least.

I'll also add a few design pointers. Based on the data you're saving, you probably want to have the inserts wrapped in a single transaction, which is pretty simple. You should also avoid closing and reopening the connection between statements (which won't be possible in a useful transaction anyway). You only need to open the connection on the first call, and close after the last. Last, you should put the last close before the Redirect because Redirect(string) will abort the thread.

If your database supports, you may use

INSERT INTO tablename(field1,field2) VALUES(
 SELECT value1, value2
 UNION ALL
 SELECT value1, value2
 ...
 )

You may consider the below idea:

DbTransaction trans = null;
trans = Con.BeginTransaction();
cmd.Transaction = trans;
... // execute your individual insert statements
cmd.ExecuteNonQuery();
trans.Commit();

in case of error

trans.RollBack();

There a lots of way to achieve you goal, this article has several examples:

Multiple Ways to do Multiple Inserts

I do some work similar to your question but I used stored-procedure in database itself. What I do is setting up a questionnaire master template. For example, each survey has 20 questions and user must select one rating from 1 to 4 of each question. So I have two tables.

Tables

Header (SurveyID, ...) 

Detail (SurveyID, LineID, AnswerID, ...) 

Then I create a stored-procedure. When this procedure is called, it will copy data from template to transaction table using

CREATE PROC ....
AS

BEGIN TRAN 
INSERT INTO DETAIL
(...) 
SELECT ... FROM Master_Detail

INSERT INTO HEADER
(...) 
SELECT ... FROM Master_Header
COMMIT TRAN

I also have foreign key link between header and detail. Hope this can help.