VB6 ADO 命令到 SQL Server
我在针对 SQL Server 2005 数据库运行 VB6 中的 ADO 命令时遇到莫名其妙的错误。
这里有一些代码来演示这个问题:
Sub ADOCommand()
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Cmd As ADODB.Command
Dim ErrorAlertID As Long
Dim ErrorTime As Date
Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=database;Data Source=server"
Conn.CursorLocation = adUseClient
Conn.Open
Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenStatic
Rs.LockType = adLockReadOnly
Set Cmd = New ADODB.Command
With Cmd
.Prepared = False
.CommandText = "ErrorAlertCollect"
.CommandType = adCmdStoredProc
.NamedParameters = True
.Parameters.Append .CreateParameter("@ErrorAlertID", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@CreateTime", adDate, adParamOutput)
Set .ActiveConnection = Conn
Rs.Open Cmd
ErrorAlertID = .Parameters("@ErrorAlertID").Value
ErrorTime = .Parameters("@CreateTime").Value
End With
Debug.Print Rs.State ''// Shows 0 - Closed
Debug.Print Rs.RecordCount ''// Of course this fails since the recordset is closed
End Sub
所以这段代码不久前还可以工作,但现在它在最后一行失败并出现错误:
Run-time error '3704': Operation is not allowed when the object is closed
为什么它被关闭?我刚刚打开它,SP 返回行。
我运行了一个跟踪,这就是 ADO 库实际提交到服务器的内容:
declare @p1 int
set @p1=1
declare @p2 datetime
set @p2=''2010-04-22 15:31:07:770''
exec ErrorAlertCollect @ErrorAlertID=@p1 output,@CreateTime=@p2 output
select @p1, @p2
从我的查询编辑器中将其作为单独的批处理运行会产生:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '2010'.
当然有一个错误。看看里面的双单引号。到底是什么原因造成的?我尝试使用 adDBDate 和 adDBTime 作为日期参数的数据类型,它们给出了相同的结果。
当我设置参数 adParamInputOutput 时,我得到以下信息:
declare @p1 int
set @p1=default
declare @p2 datetime
set @p2=default
exec ErrorAlertCollect @ErrorAlertID=@p1 output,@CreateTime=@p2 output
select @p1, @p2
将其作为单独的批处理运行会产生:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'default'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'default'.
到底是什么? SQL Server 不支持这种语法。在实际的 SP 执行语句中只能使用 DEFAULT 关键字。
我应该注意到,从上面的语句中删除额外的单引号可以使 SP 正常运行。
……天啊。我刚刚想通了。我想无论如何还是值得发帖的。
I'm getting an inexplicable error with an ADO command in VB6 run against a SQL Server 2005 database.
Here's some code to demonstrate the problem:
Sub ADOCommand()
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Cmd As ADODB.Command
Dim ErrorAlertID As Long
Dim ErrorTime As Date
Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=database;Data Source=server"
Conn.CursorLocation = adUseClient
Conn.Open
Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenStatic
Rs.LockType = adLockReadOnly
Set Cmd = New ADODB.Command
With Cmd
.Prepared = False
.CommandText = "ErrorAlertCollect"
.CommandType = adCmdStoredProc
.NamedParameters = True
.Parameters.Append .CreateParameter("@ErrorAlertID", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@CreateTime", adDate, adParamOutput)
Set .ActiveConnection = Conn
Rs.Open Cmd
ErrorAlertID = .Parameters("@ErrorAlertID").Value
ErrorTime = .Parameters("@CreateTime").Value
End With
Debug.Print Rs.State ''// Shows 0 - Closed
Debug.Print Rs.RecordCount ''// Of course this fails since the recordset is closed
End Sub
So this code was working not too long ago but now it's failing on the last line with the error:
Run-time error '3704': Operation is not allowed when the object is closed
Why is it closed? I just opened it and the SP returns rows.
I ran a trace and this is what the ADO library is actually submitting to the server:
declare @p1 int
set @p1=1
declare @p2 datetime
set @p2=''2010-04-22 15:31:07:770''
exec ErrorAlertCollect @ErrorAlertID=@p1 output,@CreateTime=@p2 output
select @p1, @p2
Running this as a separate batch from my query editor yields:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '2010'.
Of course there's an error. Look at the double single quotes in there. What the heck could be causing that? I tried using adDBDate and adDBTime as data types for the date parameter, and they give the same results.
When I make the parameters adParamInputOutput, then I get this:
declare @p1 int
set @p1=default
declare @p2 datetime
set @p2=default
exec ErrorAlertCollect @ErrorAlertID=@p1 output,@CreateTime=@p2 output
select @p1, @p2
Running that as a separate batch yields:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'default'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'default'.
What the heck? SQL Server doesn't support this kind of syntax. You can only use the DEFAULT keyword in the actual SP execution statement.
I should note that removing the extra single quotes from the above statement makes the SP run fine.
... Oh my. I just figured it out. I guess it's worth posting anyway.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
答案是,存储过程需要在顶部设置
SET NOCOUNT ON
,因为 ADO 在收到“受影响的行”响应后立即停止,并且存储过程在最终选择之前有一条更新语句。我不知道为什么跟踪显示的 ADODB 语法在单独提交时无法正确运行,但显然 ADODB 库的查询没有出现错误。整个问题是缺少
SET NOCOUNT ON
。The answer is that the stored procedure needed
SET NOCOUNT ON
at the top, since ADO stops the moment it gets a "Rows Affected" response back, and the stored procedure has an update statement before the final select.I have no clue why the trace is showing syntax from ADODB that doesn't run correctly when submitted on its own, but clearly the ADODB library was NOT getting an error with its queries. The whole problem was
SET NOCOUNT ON
missing.除了 @ErikE 提到的之外,如果您的存储过程包含 PRINT 语句(即使仅用于调试),ADO 同样会产生一头牛,产生与您所看到的完全相同的错误。
In addition to what @ErikE mentioned, if your sproc contains PRINT statements (even if only left for debugging), ADO will likewise have a cow, producing the same exact error you are seeing.