locked
Incorrect syntax near the keyword 'user' RRS feed

Answers

  • cmdStr  = "INSERT INTO Register (datetime, user, tempuser, pass, reenter, email, secques ,dob, first, middle, last, sir, street, city, state, zip, timezone, phone, cell, credentials, secid) VALUES (@datetime, @user, @tempuser, @pass, @reenter, @email, @secques, @dob, @first, @middle, @last, @sir, @street, @city, @state, @zip, @timezone, @phone, @cell, @credentials, @secid);"

    Hello,

    "user" is a reserved word in T-SQL, you should avoid using it for column names. If you want to use it, you have to set it in brackets [ ] to get it working =>

    cmdStr  = "INSERT INTO Register (datetime, [user], tempuser, pass, reenter, email


    Olaf Helper

    Blog Xing

    • Proposed as answer by SathyanarrayananS Thursday, March 21, 2013 2:42 PM
    • Marked as answer by Fanny Liu Thursday, March 28, 2013 8:45 AM
    Thursday, March 21, 2013 2:27 PM

All replies

  • Hi Philosophiae,

    Kindly post error you are getting and table definition to get answer.

    Also make sure your inserting correct values into correspoding columns to avoid truncation and data type mismatch error.

    Try this, i guess error should be because of identity column.

    SET IDENTITY_INSERT Register OFF
    --if you want to explicitly insert value for identity column 
    SET IDENTITY_INSERT Register ON


    Thanks & Regards, sathya

    Thursday, March 21, 2013 2:21 PM
  • cmdStr  = "INSERT INTO Register (datetime, user, tempuser, pass, reenter, email, secques ,dob, first, middle, last, sir, street, city, state, zip, timezone, phone, cell, credentials, secid) VALUES (@datetime, @user, @tempuser, @pass, @reenter, @email, @secques, @dob, @first, @middle, @last, @sir, @street, @city, @state, @zip, @timezone, @phone, @cell, @credentials, @secid);"

    Hello,

    "user" is a reserved word in T-SQL, you should avoid using it for column names. If you want to use it, you have to set it in brackets [ ] to get it working =>

    cmdStr  = "INSERT INTO Register (datetime, [user], tempuser, pass, reenter, email


    Olaf Helper

    Blog Xing

    • Proposed as answer by SathyanarrayananS Thursday, March 21, 2013 2:42 PM
    • Marked as answer by Fanny Liu Thursday, March 28, 2013 8:45 AM
    Thursday, March 21, 2013 2:27 PM
  • Olaf is correct, but I also suggest to avoid using AddWithValue and use more verbose Add syntax to specify type and size for the parameter.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, March 21, 2013 6:29 PM
  • avoid using AddWithValue and use more verbose Add syntax

    Naomi, excatly the other way around; see Parameters.Add => "Note: This API is now obsolete." and Parameters.AddWithValue => Remarks: "AddWithValue replaces the SqlParameterCollection.Add method".

    Add you can easily define the data type by using the fluent interface:

    cmd.Parameters.AddWithValue("@myParam", 123).SqlDbType = System.Data.SqlDbType.Int;


    Olaf Helper

    Blog Xing

    Friday, March 22, 2013 7:24 AM
  • No, it's only 1 particular overload of this method is obsolete. This way

    cmd.Parameters.Add("@myParam", SqlDbType.VarChar, 20).Value = "Some Value";

    is Ok. In my code this is what I use.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    • Edited by Naomi N Friday, March 22, 2013 7:40 PM
    Friday, March 22, 2013 12:59 PM
  • Hello,

    Naomi is absolutely right. It is SqlParameterCollection.Add Method (String, Object) which is obsolete as it is ambiguous.

    http://msdn.microsoft.com/en-us/library/9dd8zze1(v=vs.100).aspx

    I have a "bad" habit : to define most of the properties of a parameter ( Direction, DbType or similar property , IsNullable, Size,...)

    There is something which is annoying in the query : the use of datetime as column name.

    I may go wrong but DateTime is a type of data  http://msdn.microsoft.com/en-us/library/ms187819.aspx and I am not sure that the correct syntax would be [datetime]

    Last annoying thing with the following code line

    cmd.Parameters.AddWithValue("@datetime",TextBox4.Text)

    I hope you are not trying to try to load a string (TextBox4.Text) in a column defined as date and time (The Value property of the DateTimePicker control is the good way ). If datetime represents a date + time, you cannot do any correct comparison between the values of the "datetime" column ( it is too much depending of the format of a date according the language currently used in a country for example 01/10/2013 is it 10th of January as in USA or 1st of October in France or Germany ? )

    It is why the full definition of the table ( including columns and constraints ) is necessary.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Friday, March 22, 2013 6:42 PM