Problem with Access Random Record Generator.

[hollyadams]

Hi I am try to use some code that I found on Daniweb forum to display random records from an Access database on a page. The database has a table called tFact, and two columns called ID, and vFact. I can not get the code to work, and I was hoping someone would help me out? Here is the code:

<script runat=server>
        
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            RandomFactNumber()
            RandomFact()

        End Sub
        
  Sub RandomFactNumber()

        
            strSQL = "SELECT ID FROM tFact"

        Dim objDataReader as OledbDataReader
            
       
        DBconnect()

        objConnection.Open()
        objDataReader  = objCommand.ExecuteReader()

        
        Dim iRecordNumber = 0
        do while objDataReader.Read()=True
            iRecordNumber += 1
        loop

        objDataReader.Close()
        objConnection.Close()

        
        Randomize()
        do
            iRandomFact = (Int(RND() * iRecordNumber))
        loop until iRandomFact <> 0


  End Sub



  
  Sub RandomFact()

            strSQL = "SELECT ID, vFact FROM tFact as @ID, @vFact"

         Dim objDataReader as OledbDataReader
            
        
        DBconnect()

        objConnection.Open()
        objDataReader  = objCommand.ExecuteReader()

        Dim i = 0

        
        do while i<>iRandomFact
            objDataReader.Read()
            i += 1
        loop

        

        
        
            Label1.Text = objDataReader("@ID")
            Label2.Text = objDataReader("@vFact")
        

        objDataReader.Close()
        objConnection.Close()

  End Sub



 
 Sub DBconnect()

     objConnection = New OledbConnection(strConnection)
     objCommand = New OledbCommand(strSQL, objConnection)

 End Sub


    
    Public strDBLocation = DB_Path()
    Public strConnection as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBLocation
    Public objConnection
    Public objCommand
    Public strSQL as string
    Public strRatingimg as Integer
    Public iRandomFact as integer

    
    Function DB_Path()
        
            If InStr(Context.Request.ServerVariables("PATH_TRANSLATED"), "tFact") Then
                DB_Path = System.Web.HttpContext.Current.Server.MapPath("App_Data\QEI.mdb")
            Else
                DB_Path = System.Web.HttpContext.Current.Server.MapPath("App_Data\QEI.mdb")
            End If

    End Function
    
    </script>

Any ideas what I'm doing wrong? Let me know if you need anymore information, I'm pretty new to this stuff. Thank you.

Holly

[Ken Sharpe]

That code is insane. Horribly inefficient -- here's what you need to do instead.

Execute a query that gets a COUNT of all the records.

Get a random number between 1 and that count. We'll call that X.

Select one record at index X-- NOT with ID X, that won't work, INDEX X.

The one record you have will be your random fact.