#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Houston, TX
    Posts
    13
    Rep Power
    0

    ASP / SQL 2K Rowset position cannot be restarted


    Hey gang-

    Im attempting to grab results from a stored procedure and toss them in a recordset. Unfortunately, I cannot open a recordset that can move forward and backward. I have tried to simply chance the rs.CursorType but this results in a connection timeout..

    Any ideas?
    Code:
    set Conn = Server.CreateObject("ADODB.Connection")
    	Conn.Open ADOCONNSTRING
    
    set cmd = Server.CreateObject("ADODB.Command")
    
    set	cmd.ActiveConnection = Conn
    	cmd.CommandType = adCmdStoredProc
    	cmd.CommandText = "esp_schberth"
    	cmd.CommandTimeOut = 30
    
    	cmd.Parameters.Append cmd.CreateParameter("@fromDate", adDate, adParamInput, 0, pFromDate)
    
    set rs = Server.CreateObject("ADODB.Recordset")
    	rs.ActiveConnection = Conn
    	'rs.CursorLocation = adUseClient
    	'rs.CursorType = adOpenStatic
    	rs.Open cmd
    
    	rs.MoveFirst
    	Do While Not rs.EOF
    		berthCount = berthCount + 1
    		rs.MoveNext
    	Loop
    	
    	rs.MoveFirst
    	Do While Not rs.EOF
    					
    	Loop
    Microsoft OLE DB Provider for SQL Server error '80040e18'

    Rowset position cannot be restarted.

    /dockdev/schResults.asp, line 75
  2. #2
  3. Digitally Challenged
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    280
    Rep Power
    12
    could you post your stored procedure? also what are you trying to do with the @fromdate? if you are passing paramaters to your sp you should have dates inside ' '

    when i call my stored procedures it looks like this

    Code:
    spString = "sp_storedProc '" & datestring & "', " & number
    set rsSP = Conn.Execute(spString)
    Last edited by don_sparko; September 30th, 2003 at 08:57 AM.
    My brain cells are like a storm trooper's armor: useless
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,428
    Rep Power
    4539
    You have thre cursor location and cursor type properties commented out. I think the default recordset is a read-only forward-only unless you specify otherwise.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Houston, TX
    Posts
    13
    Rep Power
    0
    No, i realize I have them commented out. Whenever i add them, the connection times out.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0
    I know this reply is a little late, but for all of those who may still be experiencing this problem like me, here is what I came up with.

    From what I have been able to determine, the Ado Command Objects Execute method returns a ForwardOnly recordset. So recordset methods like MovePrev, Filter, Find will fail. To fix this you will want to open the recordset with the recordset Open method.

    You may be thinking, but how to I pass the parameters to a stored procedure then?

    Here is a little trick I learned.


    set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open ADOCONNSTRING

    set rs = Server.CreateObject("ADODB.Recordset")

    rs.open “{Call esp_schberth(‘” & pFromDate & ”’)}",Conn, adOpenStatic, adLockOptimistic, adCmdText

    rs.MoveFirst
    Do While Not rs.EOF
    berthCount = berthCount + 1
    rs.MoveNext
    Loop

    rs.MoveFirst
    Do While Not rs.EOF

    Loop


    Note:
    1. The options value is adCmdText not adCmdStoredProc
    2. Curvy Brackets {} – Enclose the whole Query String
    3. The Stored Procedure Name can not be enclosed in Brackets [], there for must not contain any spaces
    4. Pass parameters in the order the stored procedure declares them. The format is (‘value1’,’value2’,’value3’). In addition all parameters are passed between single quotes ‘’, requardless if they would be passed that way in a SQL WHERE Clause




    Good Luck

    Karl
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    DC
    Posts
    1
    Rep Power
    0
    Another way to do this which seems to work for me is to set the CursorLocation on the database connection rather than on the recordset.

    Set DBConn = Server.CreateObject("ADODB.Connection")
    DBConn.CursorLocation = 3 'ie. client side
    DBConn.Open [CONNECTSTRING]

    Set rst = Server.CreateObject("ADODB.Recordset")
    rst.ActiveConnection = DBConn
    rst.Cursortype = adOpenStatic

    Gordon
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    1
    Rep Power
    0
    You probably don't know it, but your post saved my life!!! Thank you!!!!!!!
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    1
    Rep Power
    0
    You probably don't know it, but your post saved my life!!!! Thank you!!!!!!!!
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0
    <%NoFlame%> Woooo I'm still programming in VBS, and it's 2013, and to address this post, well if you "EXEC stored_procedure_name" you can go back/forth/sideways in a standard recordset nowadays :

    Set rs = Server.CreateObject ("ADODB.Command")
    rs.ActiveConnection = conn
    rs.CommandText = "EXEC s_reports_crane_view"
    rs.Prepared = true
    rs.Execute

    Sexy Time! <%NoFlame%>

IMN logo majestic logo threadwatch logo seochat tools logo