Error in create table command- Incorrect syntax near

Question

I want to create a table name based on session's variable name

(session variable is email id of a student and sid of that student will be the table name which I want to create)

NOTE: sid is fetching fine..

Here's the code

SqlConnection con99 = new SqlConnection(con100);
con99.Open();
String email = Session["sname"].ToString();
SqlCommand cmd = new SqlCommand("select id from students where email='" 
+ email + "'", con99);
sid = cmd.ExecuteScalar().ToString(); //as sid is int
using (SqlCommand command = new SqlCommand("CREATE TABLE " + sid + 
"(sno int, qpname nvarchar(500), mobtained int, tmarks int);", con99))
command.ExecuteNonQuery();        
con99.Close();

here is the error message pic

variable sid is also being created-pic

Don't you need a pair of ' ' around sid? Like this, 'sid'... — Brian
– Brian, Commented Oct 12, 2016 at 23:25
@Brian i added "CREATE TABLE " + 'sid' + "(sno int, qpna........) as u said and it shows error-Too many characters in character literal — Jai hind
– Jai hind, Commented Oct 12, 2016 at 23:28
Or, try, + "sid" + since you are casting it to a string already? — Brian
– Brian, Commented Oct 12, 2016 at 23:31

Crowcoder · Accepted Answer · 2016-10-13 01:12:45Z

2

You must put brackets around your table name if you are naming it with a number. So your script must end up like this when evaluated:

CREATE TABLE [2] (sno int, qpname nvarchar(500), mobtained int, tmarks int);

Frankly, I'm surprised that is allowed. It may be a feature of newer versions of SQL Server.

A better way to create database objects is with the SQL Server Management Objects (SMO) libraries. Much less chance to make syntax errors:

An example from here

Server srv = new Server("(local)");
Database db = srv.Databases["AdventureWorks2012"];

Table tb = new Table(db, "Test Table");
Column col1 = new Column(tb, "Name", DataType.NChar(50));
Column col2 = new Column(tb, "ID", DataType.Int);

tb.Columns.Add(col1); 
tb.Columns.Add(col2); 
tb.Create();

answered Oct 13, 2016 at 1:12

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

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

4 Comments

Jai hind Over a year ago

[2] is different.. i want to create a table naming the value of sid @Crowcoder

Crowcoder Over a year ago

Using brackets is how you name things that do not follow naming standards. For instance, if you have a space in the name, or you use a number. You can't use just "2". It is impossible.

Jai hind Over a year ago

using this - new SqlCommand("CREATE TABLE" +[sid]+ "(sno int, q... it gives an error " Invalid expression term '[' "

Jai hind Over a year ago

wow! Crowcoder that worked! thanks a lot ..how to marks your answer correct now?

JBrooks · Accepted Answer · 2016-10-13 01:24:46Z

0

First one is an error, next 2 are not:

create table 2 (id int)

create table [2] (id int)

create table ID_2 (id int)

See the section called "The first character must be one of the following" here.

answered Oct 13, 2016 at 1:24

JBrooks

10k2 gold badges31 silver badges32 bronze badges

1 Comment

Jai hind Over a year ago

not understood what you are trying to say.. i want a table naming value of sid not 2 @JBrooks

Collectives™ on Stack Overflow

Error in create table command- Incorrect syntax near

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related