Error when inserting multiple rows in SQL table

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?

Answers


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

Need Your Help

Redirecting with javascript after adding googlemap location to sql database

php javascript html sql google-maps

I'm quite new to javascript (okay, I've spent only two days using it!). One of the things I want to do on my site is allow users to add a marker to a google map. I've been reading up on the googlem...

Splitting a line from a textfile into two separate Arrays

java arrays string file split

I must apologize in advance if this question has been answered, I have not been able to find an answer to this problem on the forum thus far.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.