Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    9

    Red face Unanswered: How to get recordset in ASP from MSSQL stored procedure

    I have stored procedure with couple input parameters (MS SQL) which creates temporary table and returns recordset as the result from that tamporary table. Stored procedure works ok on its own.
    I need to get that recordset in ASP but it doesn't work !?!

    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = Conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "PROC_DrawingTest"

    cmd.Parameters.Append(cmd.CreateParameter("jobno", adVarChar, adParamInput, 20, Session("JobNumber")))
    cmd.Parameters.Append(cmd.CreateParameter("strdwgs ", adVarChar, adParamInput, 1000, sqlstr))

    Set rec = cmd.Execute()

    If not rec.EOF then
    - here I have an error

    Error Type:
    ADODB.Recordset (0x800A0E78)
    Operation is not allowed when the object is closed.

    Please, help!

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    how about a call to rec.Open() before you read from it?

  3. #3
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    I suppose if the recordset is returned as the return value , there is no need to call the "Open" method.

    Instead try this :


    Set rec = cmd.Execute()
    if not rec nothing then
    If not rec.EOF then
    - here I have an error
    end if
    Error Type:
    ADODB.Recordset (0x800A0E78)
    Operation is not allowed when the object is closed.
    In GOD we believe. Everything else we Test!

  4. #4
    Join Date
    Nov 2004
    Posts
    9
    Thank you, guys!
    The problem is solved now. I've added SET NOCOUNT ON and SET NOCOUNT OFF within stored procedure for INSERT and SELECT and it works just 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
  •