CODEWITHSUNDEEP
c#sqlsqlcommand
MrNobody
MrNobody

Reputation: 1

SQLCommand, create table

I want to create table in my database after button click. In Button_Click function I have a code

SqlConnection conn = new SqlConnection(@"MyConnectionString");

conn.Open();

SqlCommand cmd = new SqlCommand("CREATE TABLE '" + tableName+ "' (IdPy INT IDENTITY(1,1), Question NVARCHAR (MAX) NOT NULL, IsChecked BIT NOT NULL,  CONSTRAINTPK_'" + tableName+ "' PRIMARY KEY(Id) )", conn);

cmd.ExecuteNonQuery();
conn.Close();

tableName is my String variable (its value 2018-04-18 asd - yes, I want the table with such a name). And I have an error after button click:

System.Data.SqlClient.SqlException: 'Incorrect syntax near '2018-04-18 asd'.'

I think that the problem is in my SqlCommand. I would be gratefull if you could help me solve that problem.

Upvotes: 0

Views: 5257

Answers (3)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

You should escape ([...] in case of MS SQL) table and constraint names:

  //DONE: wrap IDisposable into using
  using(SqlConnection conn = new SqlConnection(@"MyConnectionString")) {
    conn.Open();

    //DONE: Make sql readable. Can you see that you've skipped CONSTRAINT keyword?
    string sql = 
      $@"CREATE TABLE [{tableName}] (
           -- Fields
           IdPy      INT IDENTITY(1,1),
           Question  NVARCHAR (MAX)     NOT NULL, 
           IsChecked BIT                NOT NULL,

           -- Constraints
           --DONE: Constraint key word (optional in some RDBMS) added 
           CONSTRAINT [CONSTRAINTPK_{tableName}] PRIMARY KEY(Id) 
         )";

    //DONE: wrap IDisposable into using
    using (qlCommand cmd = new SqlCommand(sql, conn)) {
      cmd.ExecuteNonQuery();
    }
  }

Upvotes: 2

jeroenh
jeroenh

Reputation: 26772

It looks like the tableName variable is 2018-04-18 asd. If that really is the correct table name, you need to escape it (and the constraint) in square brackets:

SqlCommand cmd = new SqlCommand("CREATE TABLE [" + tableName + "] (IdPy INT IDENTITY(1,1), Question NVARCHAR (MAX) NOT NULL, IsChecked BIT NOT NULL, CONSTRAINT [CONSTRAINTPK_" + tableName+ "] PRIMARY KEY(Id) )", conn);

Upvotes: 2

Dan Hebdon
Dan Hebdon

Reputation: 201

It might be easier to identify issues with your SQLCommand by using a string variable and parameterised string formatting. An example: 

string query = "CREATE TABLE @tablename (IdPy INT IDENTITY(1,1), 
Question NVARCHAR (MAX) NOT NULL, IsChecked BIT NOT NULL,  
CONSTRAINTPK_@tablename PRIMARY KEY(Id) )";

string param = new {@tablename = txttable.txt(example)};

SqlCommand cmd = new SqlCommand(query, param, conn);

This might help step through to make sure that the variable you have to inspect more concise.

Upvotes: 0

Related Questions