Simultaneously Insert Multiple Rows Into Database

Question

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.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 = 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();
            }
        }
    }
}

that code is so non DRY that I am going to refuse to answer it on principle... — kmcc049
– kmcc049, Commented Aug 31, 2011 at 0:48

Michael Yoon · Accepted Answer · 2011-08-31 01:10:25Z

2

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.

edited Aug 31, 2011 at 1:10

answered Aug 31, 2011 at 0:54

Michael Yoon

1,60611 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nick Over a year ago

It worked! Let me get this straight 'cmd.Parameters.Add' is adding the parameters (columns) in the table and cmd.Parameter is adding the variable to be inserted? Such as: INSERT INTO Table_1 (Parameter_1, Parameter_2) values ('Variable_1', 'Variable_2').

Michael Yoon Over a year ago

Sort of like that. Calling .Add adds a Parameter to the Parameter collection, which is basically an array that gets checked during your stored procedure call. It has no value by default in the version you're using, so you have to set it with your following line. That element doesn't disappear, so it gets sent every time you execute the command. Since it's still there you can change the value by pulling out that parameter and changing its value.

SRK · Accepted Answer · 2012-09-19 06:08:41Z

2

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

answered Sep 19, 2012 at 6:08

SRK

211 bronze badge

Comments

Richard Sayakanit · Accepted Answer · 2011-09-07 02:08:01Z

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.

Collectives™ on Stack Overflow

Simultaneously Insert Multiple Rows Into Database

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related