SQL error: Incorrect syntax near the keyword 'User'

Question

I am using SQL to insert data to SQL Database file using C# as follows.

    String cs = System.Configuration.ConfigurationManager.ConnectionStrings["connection1"].ConnectionString;
    SqlConnection conn = new SqlConnection(cs);
    String sql = "INSERT INTO User (login, password, status) " + 
            "VALUES (@login, @password, @status)";
    SqlCommand comm = new SqlCommand(sql, conn);

    comm.Parameters.Add("@login", System.Data.SqlDbType.VarChar);
    comm.Parameters.Add("@password", System.Data.SqlDbType.VarChar);
    comm.Parameters.Add("@status", System.Data.SqlDbType.Bit);

    try
    {
        conn.Open();
        Console.WriteLine(conn.ToString());
        comm.ExecuteNonQuery();
        conn.Close();
        return true;
    }
    catch (Exception ex)
    {
        throw (ex);
    }
    finally
    {
        conn.Close();
    }

I am getting the following error when command is executing.

Incorrect syntax near the keyword 'User'.: INSERT INTO User (login, password, status) VALUES (@login, @password, @status)

How can I solve this please?

edit: missed parameter values added..

    comm.Parameters["@login"].Value = this.Username;
    comm.Parameters["@password"].Value = this._password;
    comm.Parameters["@status"].Value = this.Status;

Psalterium

How is that a C# error? Sounds like a SQL error. – Freewill

@CodeInChaos - re-tagged; better? – Lashawna

@Marc I was talking about the topic not the tags. – Freewill

possible duplicate of Simple SELECT statement fails with syntax error, why? – Jaquith

Answer 1

User is a reserved keyword, so you must use square brackets to make it explicit that you mean the object named "User" it, i.e. use [User] instead of User.

Lashawna

Answer 2

User is a t-sql reserved keyword. Enclosing it in square brackets should solve this. E.g INSERT INTO [User]

Escapade

Answer 3

run your query against the database. You can use the declare sql keyword to define your variables and give them values. If you need to figure out the variables values, set a breakpoint at conn.Open and then use the locals window to see what values you are passing in. Another tool at your disposal is the Sql Profiler. You can start a trace then run your program. You should be able to see the query as executed in the profile after the code you have posted has run.

All of this should help you to figure out what is wrong with your sql when the exception does not provide enough information.

The Sql Server Management Studio should have highlighted the User keyword in your sql statement, easily showing that you need brackets around it like so: [User]

Property

Agreed. It always amazes me when people write the query outside of SSMS, which (while not perfect) is an excellent tool for this. Especially if you enable options like stats-IO and actual-query-plan. – Lashawna

"The Sql Server Management Studio should have highlighted the User keyword in your sql statement, easily showing that you need brackets around it like so: [User]" I agree with this. Or VS should point it out. – Psalterium

@Psalterium - well, unless you write a plugin, that is an opaque string to Visual Studio. – Lashawna

@Marc - not all strings are opaque in Visual Studio, such as the one used with printf and friends in f#. It does type checking between its format specifiers and parameters, and will throw a compiler error if they do not match. – Property

@Psalterium - while it would be nice, there is not really any way for the c# editor to predict how you are going to use that sql statement before you reach the point of execution. Therefore it cannot generate a compiler error. It just sends it to sql server and reports back what sql server tells you. – Property

@Charles actually I suspect it could mKe an inspired guess in a lot of cases. I wonder if I should try to write a plugin – Lashawna

Answer 4

User is a sql reserved keyword. Enclosing it in square brackets should solve this. E.g INSERT INTO [User]

easily showing that you need brackets around it like so: [User]

Controversial