Pulling Random Records from Access

[kirstybandm]

Hi,
Me again,
Ok. On the website I've created I have a Product Spotlight Area on the right side of the screen that shows random products.

Each time the page changes or refreshes it shows another product from my Spotlight table which I'm really pleased with. Unfortunately, it works too well. In my Access database I have an empty record at the bottom I can't delete because its there automatically.

Unfortunately my randomise code picks any field in that table and shows it including the empty field. When it picks the empty field I get this error on the side of the page:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/Products/Menu_Guitars.asp, line 362

Below is my code for the randomise:
<%
Randomize Timer
Dim rsProduct
Dim rsProduct_numRows

Set rsProduct = Server.CreateObject("ADODB.Recordset")
rsProduct.ActiveConnection = MM_Bandm_STRING
rsProduct.Source = "SELECT ID, Headline, Story, Image FROM Spotlight ORDER BY ID DESC"
rsProduct.CursorType = 0
rsProduct.CursorLocation = 2
rsProduct.LockType = 1
rsProduct.Open()

rsProduct_numRows = 0

varrandomSpotlight = int(rnd()*(rsProduct.Fields.Item("ID").Value))+1

Dim rsSpotlight__MMColParam
rsSpotlight__MMColParam = "1"
If (varrandomSpotlight) <> 0 Then
rsSpotlight__MMColParam = varrandomSpotlight
End If
%>
<%
Dim rsSpotlight
Dim rsSpotlight_numRows

Set rsSpotlight = Server.CreateObject("ADODB.Recordset")
rsSpotlight.ActiveConnection = MM_Bandm_STRING
rsSpotlight.Source = "SELECT * FROM Spotlight WHERE ID = " + Replace (rsSpotlight__MMColParam, "'", "''") + ""
rsSpotlight.CursorType = 0
rsSpotlight.CursorLocation = 2
rsSpotlight.LockType = 1
rsSpotlight.Open()

rsSpotlight_numRows = 0
%>

Is there a way to stop it picking up the empty record? Or a way to delete the record in Access (I can't just delete the row as it is automatically generated and won't let me).

If you know a lot of ASP this probably seems really easy but to be honest I don't and I managed the Randomise Timer because of a post I did a while ago in the Macromedia Forums and help from Murray 'TMM' (very appreciated).
You can see the post at:

http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=189&threadid=1085900

I hope this is simple. You can see the problem if you go to
www.bandm.co.uk
and just keep refreshing until the error appears.

Thanks

Kirsty

[DanInManchester]

The record you are referring to does not exists. It is more of a user interface thing than a record. Even when you start typing into Access this record does not exist until it is commited. so if the user starts typing and then presses escape the record is never added.

It seems odd that this would cause a problem and I would think the problem lies else where. Could you highlight which line is causing the problem?

I tend to use this technique for selecting random records :

select a list of all the primary keys I want to include for my random selection.

create a random number from 1 to the number of records.
get the primary key that corresponds to the random number and select this record.

e.g.

selection of primary keys :
PK record number
3 1
5 2
6 3
8 4
4 5
2 6

random number 1 to 6 ..... 4

get the full record for record number 4 - primary key 8

The reason I only select primary keys is because it is a rapid and small select with an index. then once I know the record(s) I can do a PK lookup on the full record which again is fast and avoids pulling all the records details accross before I know which record I want.