OLE DB Provider for Jet 4.0 - multi-line SQL?

Hi,

 This is my first post, after much lurking and searching for clarity.

I've been porting a C# app (asp.net web application, with a windows service,
both using a MDB file) from .NET 1.1 over to .NET 2.0 under VS 2005. I've been
taking the opportunity to move OleDbDataAdapters with SQL in the code-behind
files over to ObjectDataSources and Typed Datasets in a DAL project, shared by
the two main projects.

Some of my data objects use foreign keys, and are inserted in response to a
single event, so I was using the DataAdapter.RowUpdated() handler to pull the ID
of the newly minted parent row with SELECT @@IDENTITY and using that to populate
the foreign key fields in the related child rows.

Now that I'm using TableAdapters in the DAL, I tried to follow ScottGu's example
of adding a SELECT @@IDENTITY statement in the same command as the INSERT INTO,
but this throws an exception, complaining about characters found after the SQL
command.

Is this a limitation of the Jet Provider? Can it only handle a single SQL
statement per command? I did put a semicolon after the INSERT, so that's not the
issue.

Any ideas?

 Thanks,

    Martyn

May be this article will help you.

http://support.microsoft.com/kb/815629

 Good luck.

Thanks for the response Bruce. That pretty much covers how I was doing it

Trouble is that with a TableAdapter I couldn't work out how to trap a
"RowUpdated" event in any useful way. I'm probably missing something
about the 2.0 framework in this area - I've only just started using it.

For anyone else with the same issue, the way I fixed it was as follows:

Add the two queries separately, an INSERT and a SELECT @@IDENTITY
The designer adds these to the CommandCollection and creates methods for them in
the TableAdapter class
Hit "View Code" to get to the non-Designer .cs file. This'll have a
partial stub for the top level Dataset class.
Make a new partial stub for the TableAdapter you want
Add a composite method that finds the two commands in the CommandCollection
(don't use indices as these change), I copied the autogenerated insert method
and added a acouple of things:

            foreach (System.Data.OleDb.OleDbCommand command in
this.CommandCollection)
            {
                if (command.CommandText.ToUpper().Contains("INSERT"))
                {
                    insertCommand = command;
                }

                if
(command.CommandText.ToUpper().Contains("@@IDENTITY"))
                {
                    idCommand = command;
                }
            }


At the business end of the method, you can execute the insert NonQuery then the
ID Scalar, returning the ID value as you exit:

            object returnValue = null;
            try {
                int recordsAffected = (int)insertCommand.ExecuteNonQuery();
                if (recordsAffected > 0)
                {
                    returnValue = idCommand.ExecuteScalar();
                }
            }


If anyone notices any stupid gotchas I've incurred and haven't spotted yet, just
shout. It seems to work fine about now. Obviously, if you've got a few INSERT
queries in the TableAdapter, you'll need to be more specific in the search than
I've been here.

Thanks again,

     Martyn