mbt masai
 
Welcome !
         

                                
After experiencing a lot of down time, We decided to move this site to CrystalTech.com. CrystalTech.com is powered by only the finest Windows servers providing the best performance, reliability, and value anywhere.

 Fetching Backward Error When Trying to Pick Random Database Entry

Author Message
mcgibbon

  • Total Posts : 7
  • Scores: 0
  • Reward points : 0
  • Joined: 12/3/2009
  • Status: offline
Fetching Backward Error When Trying to Pick Random Database Entry Thursday, December 03, 2009 10:06 AM (permalink)
0
I am trying to pick a random entry in a database using asp with vbscript.  I keep getting the error:

Rowset does not support fetching backward.

If anyone has any suggestions on what is wrong with the attached code I would greatly appreciate your input.

Thanks,
Andy

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../../Connections/connCountriesJET.asp" -->
<%
Dim rs
Dim rs_cmd
Dim rs_numRows

Set rs_cmd = Server.CreateObject ("ADODB.Command")
rs_cmd.ActiveConnection = MM_connCountriesJET_STRING
rs_cmd.CommandText = "SELECT ID from countries"
rs_cmd.Prepared = true

Set rs = rs_cmd.Execute
rs_numRows = 0

recCount=rs.RecordCount
rs.close

set rs=nothing


'Randomize forces the RND() function to use a new sequence of random numbers
randomize

'generate the random number based on the record count
rec = int(rnd * recCount)

Set rs_cmd = Server.CreateObject ("ADODB.Command")
rs_cmd.ActiveConnection = MM_connCountriesJET_STRING
rs_cmd.CommandText = "SELECT * from countries"
rs_cmd.Prepared = true

Set rs = rs_cmd.Execute
rs_numRows = 0

rs.MoveFirst

'use the Move property of the RecordSet object to move to the random number
rs.Move rec
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<table>
<tr><td><%= rs("Country") %></td></tr>
<tr><td><%= rs("Capital") %></td></tr>
<tr><td><%= rs("Currency") %></td></tr>
<tr><td><%= rs("Population") %></td></tr>
<tr><td><%= rs("Language") %></td></tr>
<tr><td><%= rs("Religion") %></td></tr>

</table>
</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>


#1
    ebgreen

    • Total Posts : 8088
    • Scores: 95
    • Reward points : 0
    • Joined: 7/12/2005
    • Status: offline
    Re:Fetching Backward Error When Trying to Pick Random Database Entry Thursday, December 03, 2009 10:32 AM (permalink)
    0
    What if you change the recordset cursortype to 3?
    "... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
    Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
    http://www.visualbasicscript.com/m_47117/tm.htm
    #2
      mcgibbon

      • Total Posts : 7
      • Scores: 0
      • Reward points : 0
      • Joined: 12/3/2009
      • Status: offline
      Re:Fetching Backward Error When Trying to Pick Random Database Entry Thursday, December 03, 2009 10:38 AM (permalink)
      0
      Could you explain how to do that and what does that mean exactly?  Thanks so much for your help.
      #3
        ebgreen

        • Total Posts : 8088
        • Scores: 95
        • Reward points : 0
        • Joined: 7/12/2005
        • Status: offline
        Re:Fetching Backward Error When Trying to Pick Random Database Entry Thursday, December 03, 2009 10:50 AM (permalink)
        0
        Set rs = rs_cmd.Execute
        rs_numRows = 0
        rs.CursorType = 3  ' Sets cursor type to adOpenStatic
        rs.MoveFirst
        "... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
        Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
        http://www.visualbasicscript.com/m_47117/tm.htm
        #4
          mcgibbon

          • Total Posts : 7
          • Scores: 0
          • Reward points : 0
          • Joined: 12/3/2009
          • Status: offline
          Re:Fetching Backward Error When Trying to Pick Random Database Entry Thursday, December 03, 2009 11:01 AM (permalink)
          0
          I am now getting the following error:

          ADODB.Recordset error '800a0e79'
          Operation is not allowed when the object is open.
          /RR/_DATA/country_facts_randomizer_test.asp, line 35

          #5
            ebgreen

            • Total Posts : 8088
            • Scores: 95
            • Reward points : 0
            • Joined: 7/12/2005
            • Status: offline
            Re:Fetching Backward Error When Trying to Pick Random Database Entry Thursday, December 03, 2009 11:17 AM (permalink)
            0
            So create the RS and do the query from an existing RS. As an example:

            set conn=Server.CreateObject("ADODB.Connection")
            conn.Provider="Microsoft.Jet.OLEDB.4.0"
            conn.Open(Server.Mappath("northwind.mdb"))
            set rs = Server.CreateObject("ADODB.recordset")
            sql="SELECT * FROM Customers"

            rs.CursorLocation = adUseClient
            rs.CursorType = 3
            rs.LockType = adLockBatchOptimistic

            rs.Open sql, conn
            "... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
            Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
            http://www.visualbasicscript.com/m_47117/tm.htm
            #6
              mcgibbon

              • Total Posts : 7
              • Scores: 0
              • Reward points : 0
              • Joined: 12/3/2009
              • Status: offline
              Re:Fetching Backward Error When Trying to Pick Random Database Entry Friday, December 04, 2009 3:52 AM (permalink)
              0
              I am now getting the following error:

              ADODB.Recordset error '800a0bb9'
              Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
              /RR/_DATA/country_facts_randomizer_test.asp, line 34

              Here is the code as it exists now:

              <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
              <!--#include file="../../Connections/connCountriesJET.asp" -->
              <%
              Dim rs
              Dim rs_cmd
              Dim rs_numRows

              Set rs_cmd = Server.CreateObject ("ADODB.Command")
              rs_cmd.ActiveConnection = MM_connCountriesJET_STRING
              rs_cmd.CommandText = "SELECT ID from countries"
              rs_cmd.Prepared = true

              Set rs = rs_cmd.Execute
              rs_numRows = 0

              recCount=rs.RecordCount
              rs.close

              set rs=nothing


              'Randomize forces the RND() function to use a new sequence of random numbers
              randomize

              'generate the random number based on the record count
              rec = int(rnd * recCount)

              set conn=Server.CreateObject("ADODB.Connection")
              conn.Provider="Microsoft.Jet.OLEDB.4.0"
              conn.Open(Server.Mappath("..\..\..\..\JALCdatabases\countries.mdb"))
              set rs = Server.CreateObject("ADODB.recordset")
              sql="SELECT * FROM Countries"

              rs.CursorLocation = adUseClient
              rs.CursorType = 3
              rs.LockType = adLockBatchOptimistic

              rs.Open sql, conn

              'use the Move property of the RecordSet object to move to the random number
              rs.Move rec
              %>


              #7
                ebgreen

                • Total Posts : 8088
                • Scores: 95
                • Reward points : 0
                • Joined: 7/12/2005
                • Status: offline
                Re:Fetching Backward Error When Trying to Pick Random Database Entry Friday, December 04, 2009 7:44 AM (permalink)
                0
                Which specific line is 34?

                Do you ever define the constants anywhere (adUseClient or adLockBatchOptimistic)?
                "... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
                Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
                http://www.visualbasicscript.com/m_47117/tm.htm
                #8
                  mcgibbon

                  • Total Posts : 7
                  • Scores: 0
                  • Reward points : 0
                  • Joined: 12/3/2009
                  • Status: offline
                  Re:Fetching Backward Error When Trying to Pick Random Database Entry Friday, December 04, 2009 9:50 AM (permalink)
                  0
                  rs.CursorLocation = adUseClient
                  #9
                    mcgibbon

                    • Total Posts : 7
                    • Scores: 0
                    • Reward points : 0
                    • Joined: 12/3/2009
                    • Status: offline
                    Re:Fetching Backward Error When Trying to Pick Random Database Entry Friday, December 04, 2009 9:51 AM (permalink)
                    0
                    I had not defined them.  I tried swapping out the number for each of them.  I believe they were all 3 and that didn't help.
                    #10
                      ebgreen

                      • Total Posts : 8088
                      • Scores: 95
                      • Reward points : 0
                      • Joined: 7/12/2005
                      • Status: offline
                      Re:Fetching Backward Error When Trying to Pick Random Database Entry Friday, December 04, 2009 10:08 AM (permalink)
                      0
                      Put this somewhere near the top:

                      Const adUseClient = 2
                      Const adLockBatchOptimistic = 4
                      Const adOpenStatic = 3


                      Were you putting those others in there just because of the sample code that I showed? If so, I don't know if you need them or not, they were just part of the first clip that I found that showed how to open a query from an existing recordset. Please go to the web and read about these options and understand them and decide for yourself how you need to code it in your situation.
                      "... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
                      Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
                      http://www.visualbasicscript.com/m_47117/tm.htm
                      #11

                        Online Bookmarks Sharing: Share/Bookmark

                        Jump to:

                        Current active users

                        There are 0 members and 1 guests.

                        Icon Legend and Permission

                        • New Messages
                        • No New Messages
                        • Hot Topic w/ New Messages
                        • Hot Topic w/o New Messages
                        • Locked w/ New Messages
                        • Locked w/o New Messages
                        • Read Message
                        • Post New Thread
                        • Reply to message
                        • Post New Poll
                        • Submit Vote
                        • Post reward post
                        • Delete my own posts
                        • Delete my own threads
                        • Rate post

                        2000-2012 ASPPlayground.NET Forum Version 3.8
                        mbt shoes www.wileywilson.com