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 wont 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

Id leave off the semicolon, only because its 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