Unanswered: Problem with Returning a recordset from a stored procedure in ADO/VBScript
I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.
Here is my code:
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()
if not logRS.EOF then
My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.
A good place to start looking is the ADO Connection Error collection. Check to see if Connect.Errors.Count > 0. If so, you will probably find your problem there.
Also, you can try adding SET NOCOUNT ON at the beginning of your SP, and SET NOCOUNT OFF at the end, before you return your recordset. Sometimes the command object stops asking for data when it gets the "X records affected" messages.
Finally, if that doesn't work, try being more explicit with your parameter naming. A good (and more readable) approach would be to use the CreateParameter function.
CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As ADO_LONGPTR], [Value]) As Parameter