Error when executing multiple SQL statements ASP/VBS

[meumax]

I'm trying to execute the following valid SQL statement:

strSQL = "SET NOCOUNT ON;" & strInsertQuery & ";" & "SELECT @@IDENTITY AS Ident;"

rs = conn.execute(strSQL)

*strInsertQuery is a valid Insert statement

Nothing too taxing one should imagine. I am simply trying to execute an Insert and have it return the auto number it just generated into the recordset "rs".

When my program gets to the execute line it crashes out with the error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'"

I've tried this various ways, using a command object to execute for instance. The key point it seems, is that it cannot execute if there is more than one SQL command.

Yet, according to a tutorial at

http://www.kamath.com/tutorials/tut007_identity.asp

I should be able to do this.

I am using an Access 2000 backend. From what I've read, Access 2000 supports multiple statements and @@Identity.

What's wrong?

 

[cbokowski]

try SET NOCOUNT OFF; before your select for identity.

 

[Dynapen]

It sounds to me like the problem is coming from Access. I haven't heard or read anything about access being able to handle the @@Identity, but it should handle multiple statements.

Best advice to check is to do a response.write/response.end on the SQL value before you execute it. This will force the page to write the SQL script out the the page.

Then you simply copy and paste the SQL statement into a query in Access and tell it to execute. Access will then tell you if it can or can't do what you wanted. But at least there you will get the error message from Access itself. If the SQL statement works, then it could be the driver you are using. Certain providers won't allow, or don't support, certain types of commands.