Results 1 to 2 of 2

Thread: Pulling Random Records from Access

  1. #1
    Join Date
    Dec 2005
    Posts
    15

    Pulling Random Records from Access

    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/we...readid=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

  2. #2
    Join Date
    Jan 2006
    Location
    Manchester England UK
    Posts
    225
    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.

Similar Threads

  1. writing a record set to a access table
    By numbenator in forum Client & Server Side Scripting (PHP, ASP, JavaScript)
    Replies: 1
    Last Post: 31 Aug 2005, 09:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •