Re: multiple sql statements in OLE DB command

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 06/18/04 

Date: Thu, 17 Jun 2004 23:13:32 -0400

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" <nett8@NOhotmailSPAM.com> wrote in message
news:Om1g24NVEHA.1888@TK2MSFTNGP11.phx.gbl...
> 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
>
>

Relevant Pages

  • Re: 3 Simple Security SQL Statements
    ... If you run the SELECT in a query window, it generates the list of commands ... There is procedure called sp_executesql that will take a string and execute ... Kalen Delaney, SQL Server MVP ... Builds a list of text commands. ...
    (microsoft.public.sqlserver.security)
  • Re: newbie problem: updating an access db
    ... modifying the code to use SQL Server it still exhibited the same ... changed the path to the supplied access .mdb file in the web.config ... the delete and update commands, so now they just test whether Adnum= ?. ... get this code to work, I don't want to start modifying it to use MSSQL, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: HOW TO SEARCH FOR FILES ON WINDOWS 2008
    ... Does anybody know where Microsoft hid the SEARCH function? ... Some commands and options I find useful - ... They even have examples of how to search a URL or the entire world wide web on their web site. ... SQL Server Table Valued Parameters / Types - Multiple Row Inserts ...
    (microsoft.public.windows.server.general)
  • Re: A desperate plea for help....
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... app in which collects data from a wedge scanner via barcodes. ... I have created the sql connection, data adapter, and data set as ... and DELETE commands with the text strings. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Design for historical data
    ... > types of visits are really that, 3 separate types of visits, which store ... billion rows of page delivery history in a system I helped build (I know ... Good design is important, decent, well balanced hardware (disk, ... Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)