RETURN 1 RANDOM RECORD TO WEBSITE!

[nwt002]

Hey all,

I am using Frontpage and have used its 'Database Interface Wizard' to create a page that displays

database results and also a page for me to edit my database from the web. The database that it

created for me is an Access database and will just contain a list of quotes. The two columns in

the DB are 'Key' which is autonumbered, and 'quote' which is just text. Whenever I find quotes

that I like I will be adding them to the database, so the number of records will always be

changing.


OJECTIVE:

I want to have the webpage display just 1 random quote from the database everytime the page is

refreshed. It is ok that it displays the same record more than once, but I would like every record

to have a chance to be displayed.


-----------------------------------
The only thing that I have found so far that kinda works is this:

SELECT TOP 1 Key, quote
FROM Results
ORDER BY Rnd(Key) * Second(Time()) * 1000 mod 1000

I added this to the custom query section of the 'Database Results Wizard' and this appears to

return random records, but the problem is every so often it will return all my records.

------------------------------------

I have tried to give you as much info as I could, so if there is anything else that would be

helpful to know, please let me know. I don't have much experience with databases, querying, or

scripting, so as much info as possible would be helpful. The more detailed the better!
Forgive me if this is not the right forum to post this question. I am not sure if this is more of a

website scripting question or a database question or both, so i will try posting this in some other

forums as well. Thanks for your time!

 

[PHV]

Perhaps this ?
SELECT TOP 1 Key, quote
FROM Results
ORDER BY Rnd(Key) + Second(Time())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nwt002]

I tried putting your query in the page, and it didn't give me any erros, but it returns the same record everytime I refresh the page.

 

[PHV]

Is the connection closed when you refresh the page ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nwt002]

Ummm, I'm not sure. Frontpage generated the code for the webpage when i used the 'Database Interface Wizard', and all the forums i've read about opening and closing connections doesn't seem to use the same code that the wizard generated. Here is the database partion of the code that is generated in case it help you understand what i mean(it may be hard to read because it jumbles it up when it posts it. If you have frontpage it might make it earier to read if you copy it there because it color codes it):

<table width="100%" border="1">
  <thead>
    <tr>
      <td><b>Key</b></td>
      <td><b>quote</b></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="Key,quote" s-columntypes="3,202" s-dataconnection="quotes" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="Key" s-menuvalue="Key" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="Key,quote" s-criteria s-order s-sql="SELECT TOP 1 Key, quote &lt;br&gt;FROM Results &lt;br&gt;ORDER BY Rnd(Key) + Second(Time()) " b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="../../_fpclass/fpdblib.inc" u-dbrgn1="../../_fpclass/fpdbrgn1.inc" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" b-WasTableFormat="TRUE" startspan --><!--#include file="../../_fpclass/fpdblib.inc"-->

<% if 0 then %>

<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>

<% end if %>

<%
fp_sQry="SELECT TOP 1 Key, quote  FROM Results  ORDER BY Rnd(Key) + Second(Time()) "

fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="quotes"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="Key"
fp_sMenuValue="Key"
fp_iDisplayCols=2
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<!--#include file="../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="18357" --><tr>
      <td>

      <!--webbot bot="DatabaseResultColumn" s-columnnames="Key,quote" s-column="Key" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Key&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Key")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="2510" --></td>

      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="Key,quote" s-column="quote" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;quote&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"quote")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="12176" --></td>

    </tr>
    <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="../../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="65064" --></tbody>
</table>
<p>&nbsp;</p>