Create table programmatically (vb.net, sql server)

[eyla]

hi guys,

I have these 3 tables

CREATE TABLE MUSICIANS
(SSN CHAR(10) NOT NULL WITH DEFAULT,
NAME CHAR(30) NOT NULL WITH DEFAULT,
PRIMARY KEY (SSN));
CREATE UNIQUE INDEX IXMUSICIANS ON MUSICIANS
(SSN);


CREATE TABLE INSTRUMENTS
(INSTRID CHAR(10) NOT NULL WITH DEFAULT,
DNAME CHAR(10) NOT NULL WITH DEFAULT,
KEY CHAR(05) NOT NULL WITH DEFAULT,
PRIMARY KEY (INSTRID));
CREATE UNIQUE INDEX IXINSTRUMENTS ON INSTRUMENTS
(INSTRID);

CREATE TABLE PLAYS
(SSN CHAR(10) NOT NULL WITH DEFAULT,
INSTRID CHAR(10) NOT NULL WITH DEFAULT,
PRIMARY KEY (SSN, INSTRID),
FOREING KEY (SSN) REFERENCES MUSICIANS,
FOREING KEY (INSTRID) REFERENCES INSTRUMENTS);
CREATE UNIQUE INDEX IXPLAYS ON PLAYS
(SSN,INSTRID);

I need vb code to ceate these 3 tables in one click.

I used this code to create one table but I'm getting error with it.

cmd = New SqlCommand()
cmd.Connection = conn

' cmd.CommandText = _
'"CREATE TABLE MUSICIANS" & "(SSN CHAR(10) NOT NULL WITH DEFAULT," & "NAME CHAR(30) NOT NULL WITH DEFAULT," & "PRIMARY KEY (SSN));" & "CREATE UNIQUE INDEX IXMUSICIANS ON MUSICIANS" & "(SSN);"
cmd.ExecuteNonQuery()

the error was "Error: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'with'"

waiting for your help guys.

thank you
Eyla.

 

[ss7thirty]

That means there is an error in your SQL code the WITH DEFAULT part you should take out and it will work. I think you are specifying the default incorrectly. Possibly take out the WITH word and specify a value and it should work. Here is a link that goes into depth about the create table command. There are a lot of different ways to write it based on constraints you want to make, etc.

http://www.sqlite.org/lang_createtable.html

 

[eyla]

as you said man, it was syntax error.
I removed WITH DEFAULT and it works OK

Thank's alot for your help.

Eyla