参数化的Insert语句，事务抛出错误

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:

我有一个运行良好的参数化插入语句,我需要添加一个select语句并将其全部包装在一个事务中,以获取插入的一个字段的数据。我不确定我是否已经使用ASP / ADO达到了限制,或者我是否只是语法错误。这是我的代码:

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)

当我运行该代码时,我收到此错误消息,当它尝试设置参数(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?

我有很多ASP和SQL的经验,但没有存储过程的经验。这段代码是如此痛苦地接近存储过程我应该将其移动并称之为好吗?

Thanks.

1 个解决方案

#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;"