Member Avatar for 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

  • 2 Contributors
  • 1 Reply
  • 106 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Ken Sharpe

Dani AI

Generated

posted a working-but-broken script and is right to suggest the COUNT+index approach — the original code has a few concrete bugs that make it fail and a couple of logic pitfalls to avoid.

Key problems in the posted script (brief):

  • Invalid SQL and misuse of parameter/alias syntax in the second query.
  • Accessing columns with an incorrect name syntax (use dr("ID"), not dr("@ID")) and not calling Read() before reading fields.
  • Off‑by‑one and fragile randomness: Rnd() + loop-until-not-zero excludes the first row and is unnecessary. Use System.Random (or a shared Random with a lock) and pick a 0‑based index.
  • Connections/readers never wrapped in Using blocks — risk of leaks on exceptions.

A simple, robust pattern that follows ’s idea but avoids the pitfalls:

  • Read the list of IDs into a small array/list.
  • Pick a random index with a thread-safe Random.
  • Fetch the single row by ID with a parameterized query.

Example VB.NET implementation:

' class-level
Private Shared rnd As New System.Random()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then ShowRandomFact()
End Sub

Private Sub ShowRandomFact()
    Dim ids As New List(Of Integer)()

    Using cn As New OleDbConnection(strConnection)
        cn.Open()
        Using cmd As New OleDbCommand("SELECT ID FROM tFact", cn)
            Using dr As OleDbDataReader = cmd.ExecuteReader()
                While dr.Read()
                    ids.Add(Convert.ToInt32(dr("ID")))
                End While
            End Using
        End Using
    End Using

    If ids.Count = 0 Then Return

    Dim idx As Integer
    SyncLock rnd
        idx = rnd.Next(ids.Count) ' 0-based
    End SyncLock

    Dim chosenId = ids(idx)
    Dim fact As String = Nothing

    Using cn As New OleDbConnection(strConnection)
        cn.Open()
        Using cmd As New OleDbCommand("SELECT vFact FROM tFact WHERE ID = ?", cn)
            cmd.Parameters.AddWithValue("?", chosenId)
            fact = Convert.ToString(cmd.ExecuteScalar())
        End Using
    End Using

    Label1.Text = chosenId.ToString()
    Label2.Text = fact
End Sub

Troubleshooting notes:

  • Verify the connection string and DB path; .mdb uses the Jet provider, .accdb needs the ACE provider. On 64‑bit hosts, Jet won’t work (use ACE or force x86).
  • Test the SELECTs in Access first to confirm IDs and vFact exist.
  • Use parameterized queries (OleDb uses ? placeholders), dispose connections/readers, and avoid building SQL with user input.
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.