Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003

    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.

  2. #2
    Join Date
    Aug 2003
    Andover, MA
    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

    Assume your parameter is an INT named @my_param

    cmm.Parameters.Append cmm.CreateParameter("@my_param",3,1, 4,client_id)

    [Note: the values of DataTypeEnum and ParameterDriectionEnum can be found at ]

    Hope this helps...

  3. #3
    Join Date
    Sep 2003
    Ahh. Thank you so much. It was the NOCOUNT property.

  4. #4
    Join Date
    Mar 2013

    RAISERROR for Debugging

    I had a similar problem...I was using RAISERROR SEVERITY 0 in my SP for debugging purposes and forgot to remove it.

    Once I removed call worked fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts