Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Raleigh, NC
    Posts
    484
    Thanks
    0
    Thanked 0 Times in 0 Posts

    asp/sql random record display

    I am trying to randomly select 3 records from an Access database and display them. I know what the problem is so far, but I'm unsure of how to fix it. I'm calling rs.RecordCount before I declare what rs is equal to.... here is the code so far:

    Code:
    <%
    	sql = ""
    	sql = sql & "SELECT LICENSE_NO,COMPANY,FNAME,LNAME,CITY,PHONE "
    	sql = sql & "FROM members "
    		
    		MaxNumber = (rs.RecordCount) 'Highest Number from Record Count
    		Randomize 'To make sure its random
    		intrandomNum1 = Int(MaxNumber * Rnd) + 1
    		Randomize 'To make sure its random
    		intrandomNum2 = Int(MaxNumber * Rnd) + 1
    		Randomize 'To make sure its random
    		intrandomNum3 = Int(MaxNumber * Rnd) + 1
    		
    	sql = sql & "WHERE LICENSE_NO = '" & intrandomNum1 & "' "
    	sql = sql & "OR LICENSE_NO = '" & intrandomNum2 & "' "
    	sql = sql & "OR LICENSE_NO = '" & intrandomNum3 & "' "
    	sql = sql & "ORDER BY LNAME asc "
    	Response.write sql
    	Set rs = oConn.Execute(sql)
    %>
    I need to change the Where and Or statements to
    where record = (not LICENSE_NO = ) but I am also unsure of the syntax for this too...

    Anyone know the proper syntax?
    -WebMark Art
    Programming is 80% thinking and 20% spelling

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i think you need to use 2 querys:

    sql="select max(LICENCE_NO) as uboundval from members"

    The use the rs.Fields('uboundval').Value inside your randomisations, but add the generated integer to a collection
    Like
    Code:
    dim coll
    coll = "999999999999"   'just to get a start for the collection
    MaxNumber = (rs.Fields("uboundval").Value    'Highest Number from the table
    Randomize 'To make sure its random
    do while i <= 3
    	coll = coll & ", " & (Int(MaxNumber * Rnd) + 1)
    	i = i + 1
    loop
    Then your second select:
    sql="SELECT LICENSE_NO,COMPANY,FNAME,LNAME,CITY,PHONE FROM members WHERE LICENSE_NO In (" & coll & ")"

    The main advantage is that the recordsets that are returned from the db, will be much smaller --> for the first select, only one variale-value pair and for the second one, just 3 records with 6 values.

    If you have a big table, then selecting the whole table just to get a count will generate unnescecary db-trafic.

    If you do want to only use 1 select, then you best dump the table into an array (using GetRows)+ close the recordset and connection AND then use "Int(maxnumber*Rnd)" as the arraykey for the records you need.

  • #3
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    Code:
    Dim IntRandom(3), I, J
    Randomize
    
    
    For I = 0 to 3
    
         IntRandom(I) = Int(8 * Rnd() + 1)
    
         For J = 0 to 3
    
              If IntRandom(I) = IntRandom(J) and IntRandom(I) <> 0 and I <> J Then
    
                   I = I -1
    
                   Exit For
    
              End If
    
         Next
    
    Next
    
    
    For I = 0 to 3
    
         Response.Write IntRandom(I) & "<br>"
    
    Next
    I don't know if this is helpful, but this randomize script will allow you to select 3 different random numbers.
    And if you have 3 different random numbers you can select 3 different random records...


  •  

    Posting Permissions

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