C# insert statement

Question

I am kinda new to C# and .NET applications. I work with visual studio and have this table :

CREATE TABLE [dbo].[Table] (
    [Id]    INT           NOT NULL,
    [Meno]  NVARCHAR (50) NULL,
    [Datum] DATETIME      NULL,
    [Cas]   INT           NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

And now I have a simple insert

public void NahrajSkore(string cas)
        {
            using (var con = new SqlConnection(cns)) 
            {
                con.Open();

                var inset = new SqlCommand("Insert into Table values(@name,@date,@res)", con);
                inset.Parameters.Add("@name", SqlDbType.VarChar);
                inset.Parameters.Add("@date", SqlDbType.DateTime);
                inset.Parameters.Add("@res", SqlDbType.Int);
                inset.Parameters["@name"].Value = _hrac.MenoHraca;
                inset.Parameters["@date"].Value = DateTime.Today;
                inset.Parameters["@res"].Value = int.Parse(cas);
                inset.ExecuteNonQuery();

            }
        }

BUt this method gives me error :

Incorrect syntax near the keyword 'Table'

What am I doing wrong ?

Don't use `Table` as a name for your table. Since you did, as you did in query, use `[Table]`. — Mat J, Nov 14 '14 at 09:32

Soner Gönül · Accepted Answer · 2014-11-14T09:38:24.413

4

Table is a reserved keyword in T-SQL.

You should use it with square brackets like [Table]

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]). However, the best solution is to change the name to a nonreserved word.

You can use using statement for SqlCommand as well and you can define and add values of your parameters in a single line like;

using (var con = new SqlConnection(cns)) 
{
    using(var inset = new SqlCommand("Insert into [Table] values(@name,@date,@res)", con))
    {
         inset.Parameters.Add("@name", SqlDbType.VarChar).Value = hrac.MenoHraca;
         inset.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Today;
         inset.Parameters.Add("@res", SqlDbType.Int).Value = int.Parse(cas);
         con.Open();
         inset.ExecuteNonQuery();
    }
 }

Also specifying column names in your SqlCommand is always recommended like;

"Insert into [Table](NameColumn, DateColumn, ResColumn) values(@name,@date,@res)"

edited Nov 14 '14 at 09:38

answered Nov 14 '14 at 09:32

Soner Gönül

97,193
102
206
364

Wouldn't it also be better to [specify the column names](http://www.tutorialspoint.com/sql/sql-insert-query.htm)? – BCdotWEB Nov 14 '14 at 09:39
@BCdotNET Exactly. Updated my answer. – Soner Gönül Nov 14 '14 at 09:43
Okay I updatet it like you say. But now it says about primary key being null. How can I change it using visual studio to make it auto-increment ? – Lubos Mudrak Nov 14 '14 at 09:48
@XOOLOOO You can't change it on Visual Studio. You can change it on SQL Server. Read [Auto increment primary key in SQL Server Management Studio 2012](http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012) – Soner Gönül Nov 14 '14 at 09:50
@SonerGönül Oh thanks for that, but actualy I can update this from Visual Studio so thanks :) – Lubos Mudrak Nov 14 '14 at 09:54
1

There is also a [`inset.Parameters.AddWithValue()`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx) method which makes the syntax a little bit nicer. – Oliver Nov 14 '14 at 10:15
1

@Oliver **No**. Please don't use `AddWithValue` method. It _may_ cause some unexpected problems. [Read Can we stop using `AddWithValue()` already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – Soner Gönül Nov 14 '14 at 11:19
@SonerGönül: Thanks for the enlightening. I will never recommend this again. – Oliver Nov 14 '14 at 12:35

score 1 · Answer 2 · answered Nov 14 '14 at 09:32

1

I believe Table is a reserved word in SQL :)
Try using another name for your table or use [Table] instead.

answered Nov 14 '14 at 09:32

Milen

8,697
7
43
57

score 0 · Answer 3 · edited Nov 14 '14 at 10:02

0

Please try to use the different name for the table to resolve this issue.

For ex:
using(var inset = new SqlCommand("Insert into Student values(@name,@date,@res)", con))

edited Nov 14 '14 at 10:02

Milen

8,697
7
43
57

answered Nov 14 '14 at 09:44

Code Geek

143
1
6

C# insert statement

3 Answers3