VB6 ADO Command to SQL Server
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("@ErrorAle开发者_如何学CrtID", 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.
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.
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.
精彩评论