Error when inserting multiple rows in SQL table

Question

I am trying to insert multiple records into SQL database. SQL table contains smalldatetime column. Every record is for one hour period, so when you want to insert timespan for, eg. 5 hours, you fill in 5 records into database.

Code looks something like this:

int flag = 0;
try{
   if (String.Empty.Equals(tboxFromTime.Text)){
      MessageBox.Show("Error!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
      return;
   }
   DateTime n, m;
   if (!DateTime.TryParse(tboxFromTime.Text, out n)){
      MessageBox.Show("Error!");
      return;
   }
   FormMain.connection.Open();
   command = new SqlCommand();
   command.Connection = FormMain.connection;
   string classParam, personParam, startParam;
   classParam = "class";
   personParam = "person";
   startParam = "start";

   command.CommandText = "INSERT INTO Reservations (resClassID, resPersonID, resStart) VALUES (@class, @person, @start)";
   command.Parameters.AddWithValue("@class", int.Parse(cboxRoomID.Text.Substring(4)));
   command.Parameters.AddWithValue("@person", int.Parse(cboxPersonSelection.Text.Substring(0, 2).Trim()));
   command.Parameters.AddWithValue("@start", DateTime.Parse(cboxWeekDate.Text + " " + n.ToShortTimeString() + ":00"));
   if (!String.Empty.Equals(tboxToTime.Text)){
      if (!DateTime.TryParse(tboxToTime.Text, out m)){
         MessageBox.Show("Error!");
         return;
      }
      else if(DateTime.Compare(m.AddHours(-1), n) > 0){
             TimeSpan timeDiff = m - n;
             int intDiff = timeDiff.Hours;
             for (int i = 1; i < intDiff; i++){
                 classParam = "class" + i.ToString();
                 personParam = "person" + i.ToString();
                 startParam = "start" + i.ToString();
                 command.CommandText += ", " + String.Format("(@{0}, @{1}, @{2})", classParam, personParam, startParam);

                 command.Parameters.AddWithValue(String.Format("@{0}", classParam), int.Parse(cboxRoomID.Text.Substring(4)));
                 command.Parameters.AddWithValue(String.Format("@{0}", personParam), int.Parse(cboxPersonSelection.Text.Substring(0, 2).Trim()));
                 command.Parameters.AddWithValue(String.Format("@{0}", startParam), DateTime.Parse(cboxWeekDate.Text + " " + n.AddHours(i).ToShortTimeString() + ":00"));
             }
         }
     }

   flag = command.ExecuteNonQuery();
   if (flag == 1) { MessageBox.Show("Uspelo!"); }
   }

When executing for one hour period it all goes well, but for any timespan it cracks. Command Text string for a few hours is like this:

INSERT INTO Reservations (resClassID, resPersonID, resStart) VALUES (@class, @person, @start), (@class1, @person1, @start1), (@class2, @person2, @start2), (@class3, @person3, @start3)

but, again I get this type of error message:

Incorrect syntax near ','.

Can anyone figure out what is the problem?

YvesR · Accepted Answer · 2012-09-08 12:46:11Z

2

The created sql syntax is not correct. You have to insert each value for its own.

INSERT INTO Reservations (resClassID, resPersonID, resStart) VALUES (@class, @person, @start)
INSERT INTO Reservations (resClassID, resPersonID, resStart) VALUES (@class1, @person1, @start1)
INSERT INTO Reservations (resClassID, resPersonID, resStart) VALUES (@class2, @person2, @start2)
INSERT INTO Reservations (resClassID, resPersonID, resStart) VALUES (@class3, @person3, @start3)

Or

INSERT INTO Reservations
SELECT @class, @person, @start
UNION
SELECT @class1, @person1, @start1
UNION
SELECT @class2, @person2, @start2
UNION
SELECT @class3, @person3, @start3

answered Sep 8, 2012 at 12:46

YvesR

6,2829 gold badges46 silver badges75 bronze badges

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

Comments

Agent007 · Accepted Answer · 2012-09-08 13:17:32Z

I don't think you need this line:

 command.CommandText += ", " + String.Format("(@{0}, @{1}, @{2})", classParam, personParam, startParam);

and also

command.Parameters.AddWithValue(String.Format("@{0}", classParam), int.Parse(cboxRoomID.Text.Substring(4)));                   
command.Parameters.AddWithValue(String.Format("@{0}", personParam), int.Parse(cboxPersonSelection.Text.Substring(0, 2).Trim()));       
command.Parameters.AddWithValue(String.Format("@{0}", startParam), DateTime.Parse(cboxWeekDate.Text + " " + n.AddHours(i).ToShortTimeString() + ":00"));

shold be replaced by your original statements with different parameter values like:

command.Parameters.AddWithValue("@class", int.Parse(cboxRoomID.Text.Substring(4)));          
command.Parameters.AddWithValue("@person", int.Parse(cboxPersonSelection.Text.Substring(0, 2).Trim()));        
command.Parameters.AddWithValue("@start", DateTime.Parse(cboxWeekDate.Text + " " + n.AddHours(i).ToShortTimeString() + ":00"

Hope this will resolve the issue.

Collectives™ on Stack Overflow

Error when inserting multiple rows in SQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related