Disconnected RS not working even after following instructions

[BigTeeJay]

Hey,
Here is my code...

  Dim iRetVal, iUID

  Dim conn, cmd, rs
  Set conn = Server.CreateObject("ADODB.Connection")
  Set cmd  = Server.CreateObject("ADODB.Command")
  Set rs   = Server.CreateObject("ADODB.RecordSet")

  iUID = Session("UserID")

  With cmd
    .CommandText = "usp_myProgs"
    .Name = "mp"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("retval", adInteger, adParamReturnValue)  '0
    .Parameters.Append .CreateParameter("uid", adInteger, adParamInput, , iUID)   '1           '3
  End With

  With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockBatchOptimistic 'adLockReadOnly
  End With
  
  conn.Open C_DBCONNECTION
  Set cmd.ActiveConnection = conn  
  Set rs = cmd.Execute

  Set rs.ActiveConnection = Nothing
  conn.Close

  iRetVal = cmd(0).Value
  
  If iRetVal <> -1 Then 'records were returned
    Do Until rs.EOF
      'output table rows
    Loop
  Else
    'output 1 empty row
  End if

  Set cmd = nothing
  Set rs = nothing
End Function

...and here is the error that I get...

Error Type:
ADODB.Recordset (0x800A0E79)
Operation is not allowed when the object is open.
{pagename}.asp, line 38

...now, most of the pages I find end up being the obvious issue of someone not using/specifying the client side cursor, but I've done that, and its still not working.

Any ideas?

Tj

 

[BigTeeJay]

ok, @#)&%$#, I figured out what was causing the problem, now I just have to figure out how to fix it.

I added a rs.close before setting the activecon = nothing (because I had a hunch that it was the cmd obj that was causing the problem).

When I viewed the page, my hunch was confirmed...

Error Type:
ADODB.Recordset (0x800A0E7B)
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.

...so the problem is because I am using the cmd objects result set for my rs object's source. And it wont let me close the connection for the rs because the cmd is still open.

Any ideas on how I could get the result set of a storedproc into a record set, and still capture the output/return values from the stored proc?

Tj

 

[BigTeeJay]

I figured it out! It came across it when trying to find the answer to another problem.

Here is the page that I used (that helped me solve it)...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfmstock/html/fmstocks_dal_sql.asp

...look about center page, and they give an example of how to do it.

I used that example to modify my code as such...

=====================================================================================

  Dim conn, cmd, rs
  Set conn = Server.CreateObject("ADODB.Connection")
  Set cmd  = Server.CreateObject("ADODB.Command")
  Set rs   = Server.CreateObject("ADODB.RecordSet")

  iUID = Session("UserID")

  ' setup cmd/sproc
  With cmd
    .CommandText = "usp_myProgs"
    .Name = "mp"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("retval", adInteger, adParamReturnValue)  '0
    .Parameters.Append .CreateParameter("uid", adInteger, adParamInput, , iUID)   '1
  End With
 
  ' setup recordset (to be disconnected)
  With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
  End With

  conn.Open C_DBCONNECTION
  Set cmd.ActiveConnection = conn  

  'this is the important change!
  rs.Open cmd
  
  iRetVal = cmd(0).Value  'can still get ret/output values!
  
  'do cleanup/disconnection
  set cmd.ActiveConnection = Nothing
  set cmd = Nothing
  set rs.ActiveConnection = Nothing
  conn.close
  
  If iRetVal <> -1 Then 'records were returned
    rs.PageSize = 10
    rs.AbsolutePage = pgNum

    For i = 1 to rs.PageSize
%>
      ' make a table
<%    
      rs.MoveNext
      
      If rs.EOF Then
        Exit For
      End If
    Next
  End If

  Set rs = Nothing
End Function

=====================================================================================

As a bonus... THIS CODE IS MUCH FASTER! And I mean a great deal faster... which is awesome.

Hope this helps someone else (wonder if I can rate my own post as useful so others know to look here

Tj