multiple sql statements in OLE DB command

[Jerry Nettleton]

I'm developing a C# application and using OleDb and ADO.NET to access a MS
Access database. I'm relatively new at this stuff so I'm probably missing
something simple.

I would like to create a table and then alter another table to add a field
and foreign key constraint. But the command fails when I combine the 2 SQL
statements together (CREATE TABLE and ALTER TABLE). Everything works great
when 2 commands are used. I've searched through help but can't find anything
useful (e.g. command expression syntax). Any suggestions?

Here's an example of what I've tried:

BEGIN TRANSACTION
CREATE TABLE Owners (
OwnerID INTEGER IDENTITY (1,1) NOT NULL,
LastName CHAR(40) NULL,
FirstName CHAR(40) NULL,
CONSTRAINT PK_Owners PRIMARY KEY ( OwnerID )
)
ALTER TABLE Property
ADD OwnerID INTEGER NULL,
CONSTRAINT FK_Property_Owners FOREIGN KEY ( OwnerID ) REFERENCES Owners (
OwnerID )
COMMIT


I used SQL Server Enterprise Manager to get the starting point for the these
SQL statements (since I want compatibility with SQL Server; migration
expected later). The following code works when you exeecute 2 separate
commands.

cmd.CommandText =
"CREATE TABLE Owners " +
"( " +
"OwnerID INTEGER IDENTITY (1,1) NOT NULL, " +
"LastName CHAR(80) NULL, " +
"FirstName CHAR(40) NULL, " +
"CONSTRAINT PK_Owners PRIMARY KEY ( OwnerID ) " +
#if false
") " +
#else
")";
cmd.ExecuteNonQuery();
cmd.CommandText =
#endif
"ALTER TABLE PROPERTY " +
"ADD OwnerID INTEGER NULL, " +
"CONSTRAINT FK_Property_Owners FOREIGN KEY ( OwnerID ) REFERENCES Owners
( OwnerID ) ";
cmd.ExecuteNonQuery();


When you change it from "#if false" to "#if true" to execute a single
command with 2 SQL statements, I get the following error message:

Message: Syntax error in CREATE TABLE statement.
NativeError: -538250719
Source: Microsoft JET Database Engine
SQLState: 3000

Any other ideas?

Thanks,
Jerry

 

[Joe Fallon]

SQL Server is capable of accepting multiple commands separated by a
semicolon.
Jet is not. You need to issue them separately.

 

[Jerry Nettleton]

Thanks for the info. Just curious, where is the 'single Jet command'
documented? I found the SQL server command syntax and the semicolon is
optional but I couldn't find the Jet syntax.

Thanks,
Jerry