If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
multiple sql statements in OLE DB command
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 |
#2
|
|||
|
|||
multiple sql statements in OLE DB command
SQL Server is capable of accepting multiple commands separated by a
semicolon. Jet is not. You need to issue them separately. -- Joe Fallon Access MVP "Jerry Nettleton" wrote in message ... 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 |
#3
|
|||
|
|||
multiple sql statements in OLE DB command
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 "Joe Fallon" wrote in message ... SQL Server is capable of accepting multiple commands separated by a semicolon. Jet is not. You need to issue them separately. -- Joe Fallon Access MVP "Jerry Nettleton" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|