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
    New Coder
    Join Date
    Dec 2004
    Location
    Minnesota
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with SQL for Random Record in Access.

    Hello, I have been bouncing around the forum for over an hour now trying to figure out how to return just 1 random record from my Access DB based on only 1 condition ( featured=yes ).

    I can easily generate a random number, but to generate a random # based on the # of records in the DB poses another issue for me. I COULD create another recordset and do a COUNT on it - but then I have 2 recordsets open and I would like to avoid this.

    Here is what I currently have:

    Code:
    ' Initialize ASP RND() function
    Randomize()
    intRandomNumber = Int (1000*Rnd)+1
    strSQL = "SELECT TOP 1 *, Rnd(" & 1 * (intRandomNumber)   & ")  FROM  properties ORDER BY 1"		
    Set RS = cn.Execute(strSQL)
    So above I need to figure out how to use the actual # of records that are flagged as 'featured=yes' instead of the '1000' where I set the intRandomNumber variable.

    And then my next question is how do I figure out the pointer to that record?

    Basically I am just trying to create a Featured Listing that will display a random listing everytime the page is loaded.

    Any help would be great, like I said I have looked through I would bet all of the relavant articles on this forum to no ado.

    Thanks !!!!!
    “That rug really tied the room together. ”

  • #2
    New Coder
    Join Date
    Aug 2006
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is an entire file from a shopping cart random display. Hope this helps.

    Code:
    <%
    Sub Displayrandomproducts
    '***************************************************************
    ' Subroutine to di displays of products base on shop
    ' configuration options
    ' add no product logic and getconfig("xfrontpagerandomfield")
    '******************************************************
    dim sql, colcount, totalcolumncount, maxrecords, count
    dim template, maxcolumns, randomselectfield, decimalpoint, randomrs, randomselectvalue
    shopopendatabaseP conn
    If conn.state<>adStateOpen  then
         shopclosedatabase conn
         exit sub
    end if   
    template="tmp_frontpage.htm"
    count=0
    maxcolumns=getconfig("xfrontpagemaxcolumns")
    maxrecords=getconfig("xfrontpagemaxrecords")
    randomselectfield=getconfig("xfrontpagerandomfield")   ' new
    randomselectvalue=getconfig("xfrontpagerandomvalue")
    'randomselectfield="pother1"
    If maxrecords<>"" and isnumeric(maxrecords) then 
      maxrecords=clng(maxrecords)
    else
      maxrecords=6
    end if
    If maxcolumns<>"" and isnumeric(maxcolumns) then 
      maxcolumns=clng(maxcolumns)
    else
      maxcolumns=3
    end if
    '    
    dim selectfield, selectvalue
    selectfield=getconfig("xfrontpagefield")
    selectvalue=getconfig("xfrontpagevalue")
    decimalpoint=getconfig("xdecimalpoint")
    dim conn
    If ucase(selectvalue)="RANDOM" then 
        FrontpageGetrandomproducts conn, maxrecords, sql, randomselectfield, randomselectvalue
    else     
      Generatefrontpagesql selectfield, selectvalue, sql
    end if  
    If sql="" then 
       shopclosedatabase conn
       exit sub
    end if   
    set randomrs=conn.execute(sql)
    Formatfrontpageheader
    colcount=0
    totalcolumncount=0
    'main loop
    do While Not randomrs.EOF and count<maxrecords
       FormatFrontpageTemplate template,colcount,totalcolumncount,randomrs, maxcolumns
       count=count+1 
       randomrs.movenext
    loop
    '
    FrontpageFillRemainingColumns colcount, totalcolumncount, maxcolumns
    response.write "</center></table>"
    CloseRecordSet randomrs
    shopclosedatabase conn
    end sub
    '**********************************************************************
    ' Template file is tmp_frontpage.htm
    '*********************************************************************
    Sub FormatFrontpageTemplate(template,colcount,totalcolumncount,objrs, maxcolumns)
    dim rc
    if colcount=0 then
        Response.write  FrontRow  & vbcrlf
    end if
    response.write FrontColumn
    ShopTemplateWrite  template, objRs, rc
    Response.write FrontColumnEnd  & vbcrlf
    colcount=colcount+1
    totalcolumncount=totalcolumncount+1
    if colcount>= MaxColumns then
         response.write frontrowend & vbcrlf
         colcount=0
    end if
    End Sub
    
    '*************************************
    Sub FormatFrontpageheader
    ' displays header for categories
    response.write "<center>"
    response.write FrontTable & vbcrlf
    end sub
    
    '*******************************************************************
    ' Generate the sql
    '*******************************************************************
    Sub GenerateFrontpagesql (selectfield,selectvalue, sql)
    dim wherestr, sortstr
    sortstr=getconfig("xsortproducts")
    wherestr="where hide=0"
    If selectfield<>"" and selectvalue<>"" then
          wherestr=wherestr & " and " & selectfield & "='" & selectvalue & "'"
    end if
    sql="select * from products " & wherestr
    If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
           sql=sql & " and (clanguage='" & getsess("language") & "'"
           sql=sql & " or clanguage is null)"
    end if       
    'inventory
    sql = sql & " AND (highercatalogid is null)"      
    If sortstr<>"" then 
       sql=sql & " order by " & sortstr
    end if 
    If getconfig("xdebug")="Yes" then   
      debugwrite sql
    end if  
    end sub
    
    '***********************************************************************
    ' make sure any columns are filled
    '*************************************************************************
    Sub FrontpageFillRemainingColumns (colcount, totalcolumncount, maxcolumns)
    If colcount=0 then exit sub
    If totalcolumncount< maxcolumns then
       response.write FrontRowEnd
       exit sub
    end if 
    Do While Colcount<MaxColumns
       response.write FrontColumn & "&nbsp" & frontcolumnend & vbcrlf
       colcount=colcount+1
    loop
    response.write FrontRowEnd
    end sub
    
    Sub FrontpageGetrandomproducts (conn,maxrecords,sql, randomselectfield,randomselectvalue)
    dim i, catalogid, index, plist, rs, sqlstr, sortstr
    plist=""
    Set rs=Server.CreateObject("ADODB.Recordset")
    sqlstr="SELECT catalogid FROM products where hide=0"
    If randomselectfield<>"" and (randomselectvalue="" or Ucase(randomselectvalue)="NULL") then 
      sqlstr=sqlstr & " and " & randomselectfield & "<>NULL"
    elseif randomselectfield<>"" then
    	sqlstr=sqlstr & " and " & randomselectfield & "='" & Replace(randomselectvalue, "'", "''") & "'"
    end if
    If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
           sqlstr=sqlstr & " and (clanguage='" & getsess("language") & "'"
           sqlstr=sqlstr & " or clanguage is null)"
    end if 
    
    'inventory
    sqlstr = sqlstr & " AND (highercatalogid is null)"
    'debugwrite sqlstr
    rs.Open sqlstr,conn,3,3 
    If rs.eof then 
       closerecordset rs
       sql=""
       exit sub
    end if   
    Dim arrData ' Array to Store Data
    Dim arrSequence ' Array to Hold Random Sequence
    Dim iArrayLooper ' Integer for Looping
    Dim iArraySize ' Size of Data Array
    'VP-ASP 6.08a - Changed CINT below to CLNG
    If getconfig("xmysql")<>"Yes" AND (instr(lcase(xdatabasetype), "mysql") = 0) then 
      iarraysize=clng(rs.recordcount)
    else
      'VP-ASP 6.09 - MYSQL issue with random value  
      'iarraysize=clng(GetRecordcount (conn))
      iarraysize=clng(GetRecordcount (conn,randomselectfield,randomselectvalue))
    end if    
    redim arrdata(iarraysize)
    for i = 0 to iarraysize-1
        arrData(i)=rs(0)
        rs.movenext
    next    
    RS.close
    Set RS = Nothing
    If iarraysize<maxrecords then
       maxrecords=iarraysize
    end if   
    ' Get an array of numbers 0 to array size randomly sequenced.
    arrSequence = Resequencearray(iArraySize)
    for i = 0 to maxrecords-1
       index=arrsequence(i)
       catalogid=arrdata(index)
       if plist<>"" then
         plist=plist & ","
       end if
       plist=plist & catalogid  
    Next
    sql="select * from products where hide=0 and catalogid In (" & plist & ")"
    sortstr=getconfig("xsortproducts")
    If sortstr<>"" then 
       sql=sql & " order by " & sortstr
    end if 
    If getconfig("xdebug")="Yes" then   
      debugwrite sql
    end if  
    end sub
    
    Function ResequenceArray(iArraySize)
    Dim arrTemp()
    Dim I
    Dim iLowerBound, iUpperBound
    Dim iRndNumber
    Dim iTemp
    ' Set array size
    ReDim arrTemp(iArraySize - 1)
    Randomize
    iLowerBound = LBound(arrTemp)
    iUpperBound = UBound(arrTemp)
    For I = iLowerBound To iUpperBound
      arrTemp(I) = I
    Next
    ' Loop through the array once, swapping each value
    ' with another in a random location within the array.
    For I = iLowerBound to iUpperBound
    iRndNumber = Int(Rnd * (iUpperBound - iLowerBound + 1))
    ' Swap Ith element with iRndNumberth element
    iTemp = arrTemp(I)
    arrTemp(I) = arrTemp(iRndNumber)
    arrTemp(iRndNumber) = iTemp
    Next 'I
    ' Return our array
    ResequenceArray = arrTemp
    End Function
    '***********************************************************************
    ' get record count for mysql
    '************************************************************************
    'VP-ASP 6.09 - MySQL issue with randomvalue
    'Function GetrecordCount (conn)
    Function GetrecordCount (conn,randomselectfield,randomselectvalue)
    dim sqlstr, rs, rcount
    sqlstr="select count(catalogid) FROM products where hide=0"
    If randomselectfield<>"" and (randomselectvalue="" or Ucase(randomselectvalue)="NULL") then 
      sqlstr=sqlstr & " and " & randomselectfield & "<>NULL"
    elseif randomselectfield<>"" then
    	sqlstr=sqlstr & " and " & randomselectfield & "='" & Replace(randomselectvalue, "'", "''") & "'"
    end if
    If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
           sqlstr=sqlstr & " and (clanguage='" & getsess("language") & "'"
           sqlstr=sqlstr & " or clanguage is null)"
    end if 
    sqlstr = sqlstr & " AND (highercatalogid is null)"      
    set rs=conn.execute(sqlstr)
    if rs.eof then
       rcount=0
    else
       rcount=rs(0)
    end if
    closerecordset rs
    Getrecordcount=rcount
    End function
    %>
    I know that I know nothing

  • #3
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the 1000 is not the amount of records. It's to change the random seed.

    If you want to limit the recordset then put whatever filter you want in the query.

    Code:
    strSQL = "SELECT TOP 1 *, Rnd(" & 1 * (intRandomNumber)   & ")  FROM  properties WHERE featured = yes ORDER BY 1"


  •  

    Posting Permissions

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