Why can the Rowset position cannot be restarted

In Classic ASP, I was a stored proc and I need to use the recordset twice so I used rs.MoveFirst but It keep gives me an error message:" Rowset position cannot be restarted"
Why?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
<%Dim cmd
      Set cmd = Server.CreateObject("ADODB.Command")
      With cmd
    .ActiveConnection = db
    .Commandtext = "SelectStatusTypeOnlyName"
    .CommandType = adcmdStoredProc
      .Parameters.Append .CreateParameter("@BoardTypeID", adInteger,adParamInput)
      .Parameters("@BoardTypeID").Value = BoardTypeID
   
      
End with
Set rs = cmd.Execute()
Set cmd = Nothing%>

<%Do Until rs.EOF%>
<%=rs("test")%>
<%rs.MoveNext
i=i+1
Loop%>

<%rs.MoveFirst
Do Until rs.EOF%>
<%=rs("test")%>
<%rs.MoveNext
i=i+1
Loop%>
erin027Asked:
Who is Participating?
 
jmundsackConnect With a Mentor Commented:
When you do a Command.Execute the recordset returned defaults to a "forward-only" cursor type, which prohibits .MoveFirst and .MovePrevious.  You need to create a recordset object, set the cursor type to static or dynamic, and then pass the command object as the source on the Recordset.Open:

      Set cmd = Server.CreateObject("ADODB.Command")
      With cmd
        .Commandtext = "SelectStatusTypeOnlyName"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@BoardTypeID", adInteger,adParamInput)
        .Parameters("@BoardTypeID").Value = BoardTypeID
      End With

      Set rst = Server.CreateObject("ADODB.Recordset")
      rst.Open cmd, db, adOpenStatic, adLockBatchOptimistic

0
 
erin027Author Commented:
Thank you.
Explained clear and easy!
0
All Courses

From novice to tech pro — start learning today.