Parameterized Insert statement with transaction throwing an error

Question

I had a parametrized insert statement that was working well and I needed to add a select statement and wrap it all in a transaction in order to get data for one of the fields for the insert. I'm not sure if I've hit a limitation with ASP/ADO or if I've just got something syntactically wrong. Here's my code:

set oSQLCommand = Server.CreateObject("ADODB.Command")
with oSQLCommand
   .ActiveConnection = conn
   .CommandType = 1
   .CommandText = "set nocount on " &_
               "begin transaction " &_
                  "declare @docid integer " &_
                  "begin " &_
                     "set @docid = (SELECT MAX(id+1) AS docid FROM draft_main) " &_
                     "INSERT INTO draft_details (id, main_id, blah) " &_
                     "VALUES ( ?, @docid, ?)" &_
                  "end " &_
               "commit"
   .Parameters(0).value = c_id
   .Parameters(1).value = "blah blah"
   .execute
end with
set oSQLCommand = nothing

When I run that code, I get this error message that's triggered when it tries to set the value of Parameter(0)

    Microsoft OLE DB Provider for SQL Server (0x80004005)
    Syntax error or access violation

Any idea what's wrong?

I have plenty of experience with ASP and SQL but none with stored procedures. Is this code so painfully close to a stored procedure I should just move it over and call it good?

Thanks.

Answer 1

Try putting ; after each individual statement. e.g.

 .CommandText = "set nocount on; " &_
               "begin transaction; " &_
                  "declare @docid integer; " &_
                  "begin " &_
                     "set @docid = (SELECT MAX(id+1) AS docid FROM draft_main); " &_
                     "INSERT INTO draft_details (id, main_id, blah) " &_
                     "VALUES ( ?, @docid, ?);" &_
                  "end ;" &_
               "commit;"