Random Non-sequential Rows from Access DB

[petemcw]

Hey everyone, I have a problem that I can't seem to figure out.

I am trying to bandaid a client's website that runs ASP.NET and an
Access DB. Basically we're trying to put in a simple banner
advertisement manager, and a randomly rotating banner ad.

Everything is more or less in place except I can't get the banner ad to
rotate randomly. The problem is that the "Autonumber" IDs are
non-sequential. This is obviously because the users can delete banners
they no longer want.

So I am looking for a way to select a random banner from the actual
records, versus generate a random number based on the count of records.
I don't have any experience with Access modules or anything, so I don't
know how to code my own solution using that road. I have thought about
selecting all the IDs, storing an array and pulling a random record
from the array, but I have a feeling there is a better way.

Does anyone have any experience in dealing with this? Any help would be
great!

 

[Pieter Wijnen]

A couple of samples to retrieve a random string value from a table using
VBA:

This should do for a smallish table...
Function GetRandomSmall() As String
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Cnt As Long
Set Conn = New ADODB.Connection
Conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\db1.mdb"
Conn.Open
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn.ConnectionString
Cmd.CommandText = "SELECT COUNT(*) FROM ATEST"
Set Rs = Cmd.Execute
Cnt = Rs.Fields(0).Value
Rs.Close: Set Rs = Nothing
Randomize
Cnt = (Cnt - 1) * Rnd
Cmd.CommandText = "SELECT DATA FROM ATEST"
Set Rs = Cmd.Execute
If Cnt > 0 Then Rs.Move Cnt '+ 1
GetRandomSmall = Rs.Fields(0).Value
Rs.Close: Set Rs = Nothing
Set Cmd = Nothing
Conn.Close: Set Conn = Nothing
End Function

This should do for a biggish table...

Function GetRandomBig() As String
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim MaxID As Long
Set Conn = New ADODB.Connection
Conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\db1.mdb"
Conn.Open
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn.ConnectionString
Cmd.CommandText = "SELECT MAX(ROWNUMBER) FROM ATEST"
Set Rs = Cmd.Execute
If IsNull(Rs.Fields(0).Value) Then GoTo CleanUP 'To avoid eternal loop
MaxID = Rs.Fields(0).Value

Rs.Close: Set Rs = Nothing

Randomize

Cmd.CommandText = "SELECT DATA FROM ATEST WHERE ROWNUMBER = " &
CLng((MaxID - 1) * Rnd + 1)
Set Rs = Cmd.Execute
While Rs.EOF ' No record found, try again
'Parm.Value = (MaxID - 1) * Rnd + 1
Cmd.CommandText = "SELECT DATA FROM ATEST WHERE ROWNUMBER = " &
CLng((MaxID - 1) * Rnd + 1)
Rs.Close
Set Rs = Cmd.Execute
Wend
GetRandomBig = Rs.Fields(0).Value
CleanUP:
Rs.Close: Set Rs = Nothing
Set Cmd = Nothing
Conn.Close: Set Conn = Nothing
End Function



HTH

Pieter

 

[petemcw]

Will the above code work in an ASP.NET page? I have never used ASP, I
just kind of jumped into ASP.NET.

I am using C# as well, but if the above works, it should be hard to
convert it to C#.