SQLException Occured: Incorrect syntax near ' '

Question

I get

SqlException: incorrect syntax near nvarchar
Incorrect syntax near 'ID'

in my code. Please can somebody help me solve it?

My code is

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
   partial class Form2 : Form
   {
       public Form2()
       {
           InitializeComponent();
       }

       SqlCommand cmd;
       SqlConnection con = new SqlConnection(@"Data Source=HAIER-PC;Initial Catalog=PDCS;Integrated Security=True");
       SqlDataAdapter SDA;

       private void button3_Click(object sender, EventArgs e)
       {
            con.Open();
            cmd = new SqlCommand("INSERT INTO CusUtil(Customer ID, Age, Experience, Preferred Alternatives, Outer Shell, Base Gasket, Vent, Vent Type, Impact Absorbent Liner, Eyeport Gasket, Face Shield, Comfort Liner, Chin Strap, Weight, Estimated Price)
                                  VALUES(@Customer ID, @Age, @Experience, @Preferred Alternatives, @Outer Shell, @Base Gasket, @Vent, @Vent Type, @Impact Absorbent Liner, @Eyeport Gasket, @Face Shield, @Comfort Liner, @Chin Strap, @Weight, @Estimated Price)", con);
           cmd.Parameters.Add("@Customer ID", textBox1.Text);
           cmd.Parameters.Add("@Age", comboBox1.SelectedItem.ToString());
           cmd.Parameters.Add("@Experience", comboBox2.SelectedItem.ToString());
           cmd.Parameters.Add("@Preferred Alternatives", comboBox3.SelectedItem.ToString());
           cmd.Parameters.Add("@Outer Shell", textBox2.Text);
           cmd.Parameters.Add("@Base Gasket", textBox3.Text);
           cmd.Parameters.Add("@Vent", textBox4.Text);
           cmd.Parameters.Add("@Vent Type", textBox5.Text);
           cmd.Parameters.Add("@Impact Absorbent Liner", textBox6.Text);
           cmd.Parameters.Add("@Eyeport Gasket", textBox7.Text);
           cmd.Parameters.Add("@Face Shield",textBox8.Text);
           cmd.Parameters.Add("@Comfort Liner",textBox9.Text);
           cmd.Parameters.Add("@Chin Strap",textBox10.Text);
           cmd.Parameters.Add("@Weight",textBox11.Text);
           cmd.Parameters.Add("@Estimated Price",textBox12.Text);

           cmd.ExecuteNonQuery();
           con.Close();
       }
    }
}

The error occurs at ExecuteNonQuery. The code simply save the data into a SQL Server database.

Dont think you can have spaces in column names, at least not unless it is surrounded by [ ] (for MSSQL at least and I think MYSQL is the same). This is the error I beleive: Customer ID There is space after Customer, so your error is saying bad by "ID" because of the space — Brad, Nov 09 '18 at 17:39
See https://stackoverflow.com/questions/14190798/how-to-select-a-column-name-with-a-space-in-mysql — Rui Jarimba, Nov 09 '18 at 17:41
you should use backtics for column name with space ` (in windows is ALT + 96) — ScaisEdge, Nov 09 '18 at 17:45
@bradbury9 That may be it, It has been a while for MySQL but I know you cant have spaces. Thanks — Brad, Nov 09 '18 at 17:46
Do you realize you have a line-break character after `Initial` in your connection string? Does that actually work? — Rufus L, Nov 09 '18 at 18:06
Why do you think, you need spaces here `"@Outer Shell"?` Why do you need such collaborative names? you can do `@1 @2 @3 . . . .` If you created this design - change it but if this is created for you, for column name - use `[Impact Absorbent Liner]` — T.S., Nov 09 '18 at 18:11
Guys, the code obviously for SQL SERVER. It uses `SqlCommand` not `MySqlCommand`. So, all the **ticks** suggestions are invalid — T.S., Nov 09 '18 at 18:13

ScaisEdge · Answer 1 · 2018-11-09T20:01:57.260

1

In mysql the use of space in column name is allowed but the column name must be wrapped with backticks .. so just for let the user see the command with backtics

but as suggested by Uuerdo .. the space in parameter should be not used ... try using an underscore (or camel case)

`

 cmd = new SqlCommand("INSERT INTO CusUtil(`Customer ID`, 
        Age,Experience,`Preferred Alternatives`,`Outer Shell`,`Base 
        Gasket`,Vent,`Vent Type`,`Impact Absorbent Liner`,`Eyeport Gasket`
        ,`Face Shield`,`Comfort Liner`,`Chin Strap`,Weight,`Estimated 
        Price`)VALUES(@Customer_ID,@Age,@Experience,@Preferred_Alternatives,
     @Outer_Shell,@Base_Gasket,@Vent,@Vent_Type,@Impact_Absorbent_Liner,
      @Eyeport_Gasket,@Face_Shield,@Comfort_Liner,
   @Chin_Strap,@Weight,@Estimated_Price)",con);

in SQLSERVER use [] instead of ``

edited Nov 09 '18 at 20:01

answered Nov 09 '18 at 17:50

ScaisEdge

131,976
10
91
107

The spaces in the parameter tokens probably need removed as well. – Uueerdo Nov 09 '18 at 18:08
This is actually about Sql server, not my sql – T.S. Nov 09 '18 at 18:15
In sql server , its `[ ]`, not `\` \`` – T.S. Nov 09 '18 at 18:18
The brackets remove the syntax error but another error is occurring i.e. Must declare the scalar variable "@". – annam jamshed Nov 09 '18 at 18:24
@annamjamshed did you fix the spaces in your named paramters... in the query and the add parameter calls? – Uueerdo Nov 09 '18 at 18:27
yeah i did that – annam jamshed Nov 09 '18 at 18:31
@annamjamshed Well, we can not know unless you fix the question and post code that actually doesn't work – T.S. Nov 09 '18 at 18:32

user1597990 · Answer 2 · 2018-11-09T18:29:06.847

-1

COuld you please add [ ] - square braces for column with spaces and remove spaces on Parameters. that will help, because all depends the mode you set on mySQL

edited Nov 09 '18 at 18:29

answered Nov 09 '18 at 18:12

user1597990

181
3
13

This is not an answer but a comment. And this is not my sql but sql server. And yes, braces needed. – T.S. Nov 09 '18 at 18:17
@T.S. yes its sql server i'd put a wrong tag by mistake – annam jamshed Nov 09 '18 at 18:20

SQLException Occured: Incorrect syntax near ' '

2 Answers2