-2

I want to insert multiple pieces of data into a SQL Server database as shown below, but when I run this code, I get a syntax error

Incorrect syntax near the keyword 'union'.
Incorrect syntax near ''.
Incorrect syntax near ''.
Incorrect syntax near ''.

Code:

SqlCommand cmd2 = new SqlCommand("INSERT INTO [rampDB].[dbo].[Answers]([AssessmentID],[questionID],[result],[comment]) SELECT('"
        + assessmentid + "1.1a" + RadioButtonList1.SelectedItem.Value.ToString() + TextBox1.Text + "'union'"
        + "'SELECT'" + assessmentid + "1.1b" + RadioButtonList2.SelectedItem.Value.ToString() + TextBox2.Text + "'union'"
        + "'SELECT'" + assessmentid + "1.1c" + RadioButtonList3.SelectedItem.Value.ToString() + TextBox3.Text + "'union'"
        + "'SELECT'" + assessmentid + "1.1d" + RadioButtonList4.SelectedItem.Value.ToString() + TextBox4.Text + "'union'"
        + "'SELECT'" + assessmentid + "1.1e" + RadioButtonList5.SelectedItem.Value.ToString() + TextBox5.Text
        + "')", sqlConn);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3838676
  • 3
  • 1
  • 3
  • Please post the actual string you are passing to the SQL Command (with all the variables replaced). In other words, rewrite the code to build the SQL statement into a temporary variable: String temp = "INSERT INTO ...." + ... Then put that SQL statement into the question instead. – Brad Jul 14 '14 at 22:12
  • 6
    a) Try printing out the actual SQL command. We can only guess what is in those variables; b) Use prepared statements. This code is vulnerable to SQL injection. – Eric J. Jul 14 '14 at 22:12
  • 2
    Also, please give your input fields names. `TextBox3` isn't a good name. – Dai Jul 14 '14 at 22:13
  • 1
    You need commas and spaces in your statement, you've run it all together – Les Jul 14 '14 at 22:14
  • 1
    Anything could be entered into "textbox", so a user could wreak havoc on your application. – Les Jul 14 '14 at 22:16
  • There's lack of spaces and single quotes all over the place. Best option is to write a line after the one you have above like this: System.Diagnostics.Debug.WriteLine(cmd2.CommandText) This will show you what you are doing wrong. – Steve Jul 14 '14 at 22:48

2 Answers2

0

Relpace "'union'" with this--> "union"

Amir Jalilifard
  • 2,027
  • 5
  • 26
  • 38
0

Multiple-insert syntax for SQL Server is possible with only INSERT, like so:

INSERT INTO rampDB.dbo.Answers (
    assessmentID, QuestionId, Result, Comment
) VALUES
( @r1v1, @r1v2, @r1v3, @r1v4 ),
( @r2v1, @r2v2, @r2v3, @r2v4 ),
( @r3v1, @r3v2, @r3v3, @r3v4 )

That said, the best way is to use a single INSERT with parameters, which is then executed for each row.

Dai
  • 141,631
  • 28
  • 261
  • 374