SQLCommand, create table

Question

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.

Did you check the text of the SqlCommand through a debugger? — Paul Karam
– Paul Karam, Commented Apr 18, 2018 at 9:07
you may find you need to put [] round the table name not single quotes — BugFinder
– BugFinder, Commented Apr 18, 2018 at 9:07
Check CONSTRAINTPK_'" + tableName+ "' ... That would lead to CONSTRAINTPK_'myTable' — Cleptus
– Cleptus, Commented Apr 18, 2018 at 9:08

jeroenh · Accepted Answer · 2018-04-18 12:36:46Z

2

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);

edited Apr 18, 2018 at 12:36

answered Apr 18, 2018 at 9:11

jeroenh

26.9k11 gold badges77 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MrNobody Over a year ago

I got this error: System.Data.SqlClient.SqlException: 'The definition for column 'CONSTRAINTPK_2018-04-18asd' must include a data type.'

Cleptus Over a year ago

@MrNobody I wonder if you did miss an space BIT NOT NULL, CONSTRAINT_MISSING_SPACE_PK_

MrNobody Over a year ago

@bradbury9 You are right. It work's. Thank you for your feedback. :)

Dmitrii Bychenko · Accepted Answer · 2018-04-18 14:04:47Z

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();
    }
  }

Dan Hebdon · Accepted Answer · 2018-04-18 09:15:22Z

0

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.

answered Apr 18, 2018 at 9:15

Dan Hebdon

1971 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQLCommand, create table

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related