Syntax help for create table?

[ThienZ]

i checked this sql code with swl enterprise manager and it says "Syntax check successful!". But if i run my code it says unhandled exception, system error on the executenonquery line...

 

string createtbl = "CREATE TABLE  typicals (typical varchar(200),typicalnr int);";
SqlCommand myinsertcommand = new SqlCommand(createtbl, mycon);
myinsertcommand.ExecuteNonQuery();

 

actually i want to use "create table if not exists", but if i check the syntax from this command it won't accept the "if not exists" part...

 

can someone help me?

 

thx in advance.

[himanshubari]

Hey,

 

Try removing the ';' character from your SQL string. I think you dont need that when you run your querries programatically

 

-Himanshu

[Nerseus]

To check for the table before you create, you need to check the sysobjects table. Below is a simple check:

if 0 = (select count(*) from sysobjects where name = 'typicals')
begin
CREATE TABLE  typicals (typical varchar(200),typicalnr int)
end 

 

I'd leave off the semicolon, only because it's not necessary.

 

If you want a more robust "object exists" check, use Enterprise Manager to script out the create/drop statement for you. It does a lot more to check if a table exists including checking the type of the object in the sysobjects table.

 

-ner