-1

Getting the following error when tried inserting data in multiple tables.

Incorrect syntax near the keyword 'User'

Button Click Code:

 private void buttonSave_Click(object sender, EventArgs e) {
   SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleTest1.Properties.Settings.SampleTestDBConnectionString"].ConnectionString);
   SqlCommand cmd = conn.CreateCommand();
   try {
    UserId = UserId + 1;

    cmd.CommandText = "INSERT INTO [User](User_Id,Name,Gender,Is_Active,Created_Date,Activated_Date) values(@userid,@name,@gender,@isactive,@createdate,@activedate)";
    conn.Open();
    cmd.Parameters.AddWithValue("@userid", SqlDbType.Int).Value = UserId;
    cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = textBoxName.Text;
    cmd.Parameters.AddWithValue("@gender", SqlDbType.VarChar).Value = textBoxGender.Text;
    cmd.Parameters.AddWithValue("@isactive", SqlDbType.Bit).Value = "True";
    cmd.Parameters.AddWithValue("@createdate", SqlDbType.Date).Value = System.DateTime.Today;
    cmd.Parameters.AddWithValue("@activedate", SqlDbType.DateTime).Value = System.DateTime.Now;
    cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();

    cmd.CommandText = "INSERT INTO User_Details(User_Id,Mobile,Address,Job_Contract) values(@userid,@mobile,@address,@jobcontract)";
    cmd.Parameters.AddWithValue("@userid", SqlDbType.Int).Value = UserId;
    cmd.Parameters.AddWithValue("@mobile", SqlDbType.VarChar).Value = textBoxMobile.Text;
    cmd.Parameters.AddWithValue("@address", SqlDbType.VarChar).Value = textBoxAddress.Text;
    cmd.Parameters.AddWithValue("@jobcontract", SqlDbType.VarChar).Value = textBoxJobContract.Text;
    cmd.ExecuteNonQuery();
    conn.Close();
   }

I have declared UserId value as

static int UserId = 100;

Not sure what went wrong. Please do comment if more details required.

REFERENCES:

LINK 1 : ASP.NET C# Insert data into multiple table

LINK 2 : Insert into two tables at once.

LINK 3 : Getting Syntax error in Insert statement

LINK 4 : Insert Data into two tables simultaneously in SQL Server

Community
  • 1
  • 1
Bharath theorare
  • 524
  • 7
  • 27
  • 1
    Possible duplicate of [Getting syntax error in INSERT INTO statement](http://stackoverflow.com/questions/37640847/getting-syntax-error-in-insert-into-statement) – Salah Akbari Jun 11 '16 at 19:54
  • 2
    The `User` is a reserved keyword you should enclose it in square brackets like the example in duplicate answer. – Salah Akbari Jun 11 '16 at 19:55
  • `User` is a reserved word. To use reserved words as table or column names, enclose them in square brackets. In this case, change `dbo.User` to `dbo.[User]` and it should work. – Zenilogix Jun 11 '16 at 19:57
  • 2
    I tried these as well dbo.User , [User] and [SampleTestDB].[dbo].[User] But none worked. I am also pretty sure that my schema is dbo only. Still not sure what went wrong. – Bharath theorare Jun 11 '16 at 20:02
  • 1
    You are using in a wrong way AddWithValue. The second parameter of AddWithValue is the value that you want to pass. In this way all of your parameters are created of type Integer and then you assign (at the returned parameter instance) a value sometime of different type. First fix as explained the User keyword problem then change all your AddWithValue to a simple Add. By the way, disposable objects like the connection and the command should be enclosed in a using statement. As you write it now you are at memory leak risk – Steve Jun 11 '16 at 20:06
  • 2
    @Steve - I tried the above comments about `User` as `dbo.[User]`,`[User]` and `[SampleTestDB].[dbo].[User]` but none worked. Also I tried changing my code as you recommended with `using` statement and changed `AddWithValue` to `Add`.Still the same problem persist. – Bharath theorare Jun 11 '16 at 20:16
  • If you try that commandtext in the Sql Server Management Studio (of course putting costant values instead of parameters) does it work? – Steve Jun 11 '16 at 20:19
  • Uhm, a bit field should be set with 1 or 0 not with "True" or "False" – Steve Jun 11 '16 at 20:21
  • 1
    @Steve - I tried manually entering rows in SSMS. It enters successfully. But bit field should be set as `True` or `False` only and not 1 or 0. It throws error in SSMS. – Bharath theorare Jun 11 '16 at 20:25
  • But after you add the square brackets around the User table do you get the same syntax error? Because the command text is correct as far as I can tell – Steve Jun 11 '16 at 20:31
  • Did you *Clean* and *Rebuild* your project after this changes? – Salah Akbari Jun 11 '16 at 20:34
  • I tried now. Still the same condition. Also I restarted my VS2015. I'm using .Net 4.6 framework. – Bharath theorare Jun 11 '16 at 20:38
  • Is it still same error is showing 'Incorrect syntax near ' or error is changed? – Akhilesh Jun 11 '16 at 20:39
  • I tried inserting `User` table alone. Used suggested comments, like `dbo.[User]`, `[User]` and `[SampleTestDB].[dbo].[User]`. All these cases works perfectly. But when inserting to multiple tables simultaneously, the problem remains the same. – Bharath theorare Jun 11 '16 at 20:44
  • Try with using for both command like string sqltext= ""INSERT INTO dbo.User(User_Id,Name,Gender,Is_Active,Created_Date,Activated_Date) values(@userid,@name,@gender,@isactive,@createdate,@activedate)"; using(SqlCommand query= new SqlCommand(sqltext)) { query.Connection=openCon; query.Parameters.Add("@userid",SqlDbType.VarChar,30).Value=name; ..... openCon.Open(); openCon.Close(); } – Akhilesh Jun 11 '16 at 20:54
  • Try two using block for both different query ! May be it helps ! – Akhilesh Jun 11 '16 at 20:54
  • Can you show your `User` table schema? Namely, how `user_id` is defined. – Alex Kudryashev Jun 11 '16 at 21:41
  • Is the error showing up on the first `cmd.ExecuteNonQuery();` or the second one? – Peter4499 Jun 12 '16 at 03:14

2 Answers2

0

User is reserved keyword so you need to use it like below

Insert into [User] (columns) values (@Values);

Hope it helps!

Akhilesh
  • 171
  • 2
  • 20
  • 1
    I tried these as well dbo.User, [User] and [SampleTestDB].[dbo].[User] But none worked. Still not sure what went wrong. – Bharath theorare Jun 11 '16 at 20:04
  • Try to check with parameter.Add instead of Parameter.Addwithvalue ! – Akhilesh Jun 11 '16 at 20:13
  • This link will help you -- http://stackoverflow.com/questions/5445663/difference-between-adding-parameters-to-stored-procedure-in-sql-server-2005 – Akhilesh Jun 11 '16 at 20:14
  • 1
    I tried your suggestion (as Steve mentioned in comment too) but the same error persist. Also tried with Transaction Query to insert to table 1(User) and then table 2(User_Details). Still the error is same. – Bharath theorare Jun 11 '16 at 20:27
0

Well, its the database design logic where I finally tried solving this problem.

  • I used most of the suggested edits in comments as dbo.[User], [User] and [SampleTestDB].[dbo].[User]in INSERT INTO statement.
  • Tried with usingstatements and changed AddWithValueto Add
  • Tried using a transaction query to insert into [User]table and then [User_Details]table.
  • Of course, tried cleaning up and rebuild the solution as I was using multiple instance and versions of Visual Studio at same time.

ISSUE:

I declared the User_Id in the [User]table as Primary,NOT NULL,Unique and in [User_Details]table as Primary,NOT NULL and used auto-incremental index.

In the button-click code, I just used static int which conflicted the INSERT statement of [User]table as it contained Unique property.

SOLUTION:

Solved the problem by droping and recreating the [User] table with User_Id contraints same as [User_Details] table. (Removed Unique constrain in [User] table)

Bharath theorare
  • 524
  • 7
  • 27