RETURN 1 RANDOM RECORD TO WEBSITE!

[nwt002]

Below is my original post from another form which will explain what I am trying to do. Another member has given me this query to use:

SELECT TOP 1 key, quote 
FROM Results, (Select Min(key) as MinValue FROM Results) TMin 
WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue)

Whenever I rerun this query in Access, it gives me different records, but on my webpage, whenever I refresh the page it gives me the same record everytime instead of a different random record. I thought there might be some scripting that I might need to add to the webpage to get it to rerun the query everytime the page is refresh. Please let me know if anyone has any suggestions Thanks!




---------------------------------------------------ORIGINAL POST BEGIN

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!

----------------------------------------------------ORIGINAL POST END

 

[BKQc]

Sorry, I didn't see you already started a thread in ASP

So here's my post again:

Maybe now that we have a running query we could transfer this to an ASP/HTML forum? Have you tried running the query many times within the same ASP page? If you try this, don't forget to close your recordset and connection each time. Also, there maybe is a "pooling" or "caching" issue within ASP so you could try something like

SELECT TOP 1 key, quote 
FROM Results, (Select Min(key) as MinValue FROM Results) TMin 
WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) AND 
(key=" & Rnd()*100000 & " OR TRUE)

Don't forget to use Randomize() in your ASP code before calling the VBScript Rnd() function. The effect of this will be to make the query different each time so if there's a caching mechanism, it won't be able to work.

 

[nwt002]

It's ok, i'm just glad i'm getting responses and that we are starting to get somewhere. This is my first time posting anything on this site and i'm pretty impressed on how quick I recieve the responses.

I tried the code above, but I recieve erros both in Access and on the webpage. in Access when i try to run the query, I recieve a "Data type mismatch in criteria expression." error. On the webpage I get:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

I have tried running the query many times within the same asp, but i still get the same results. I'm sorry, but i'm afraid I don't know much about closing recordsets and connections, or Randomize(). I have read some of the other post that have to do with Randomize(), and setting or closing recordsets and connections, but the website that Frontpage generated doesn't have any of the code that I have seen in those post, so i'm not sure where to put it, or what to put. I will post the HTML and script that Frontpage generates whenever I use the "Database Interface Wizard" so you might be able to understand what I'm talking about better. I will have the Database portion of the code in a code box so you can find it easily. It may be hard to read because its all jumbled together. you might want to copy and paste it in Frontpage if you have it so it will color code it to be easier to read.



---------------------------------------------WEBSITE CODE BEGINS

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Results -- View</title>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body bgcolor="#FFFFFF">

<table width="100%" align=left>
<tr>
<td width="50%">
<b><font size=+3 color="#000080"> Results Page </font></b>
</td>
<td width="50%" align=right>
<table>
<tr>
<td>
Results Page
| <a href="editor/database_editor.asp" target="_top">Database Editor</a>
</td>
</tr>
</table>
</td>
</tr>
</table>


<p>
<br clear="all">
<hr>
<p>

<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, (Select Min(key) as MinValue FROM Results) TMin &lt;br&gt;WHERE Key&amp;gt;=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) " b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="5" 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, (Select Min(key) as MinValue FROM Results) TMin  WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) "

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=5
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="44605" --><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="4558" --></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="5" 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;&lt;TR&gt;&lt;TD ALIGN=LEFT VALIGN=MIDDLE COLSPAN=64&gt;&lt;NOBR&gt;&lt;INPUT TYPE=Button VALUE=&quot;  |&lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;   &lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;   &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;|  &quot;&gt;  [1/5]&lt;/NOBR&gt;&lt;BR&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>

</body>

</html>

 

[Creto]

This may get you headed in the right direction, however, I do not know if you can use NEWID() in Access (you can use it on MS SQL).

SELECT     TOP 1 key, quote
FROM         Results
ORDER BY  NEWID()

This basically generates a random unique identifier as an additional field and then sorts by that identifier, resulting in a random record being pulled every time.

As I said, I don't know whether this will work in Access, but it may get you going in the right direction.

 

[BKQc]

It is normal that it doesn't work in Access. The part I added with the " & rnd() & " is to be run by the ASP parser.

To be honest, I never used Frontpage to do my ASP so I don't know what it is really doing behind the scene but the first thing you could try so that we don't change too much the code you have is to replace

fp_sQry="SELECT TOP 1 key, quote  FROM Results, (Select Min(key) as MinValue FROM Results) TMin  WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) "

with

Randomize()
fp_sQry="SELECT TOP 1 key, quote  FROM Results, (Select Min(key) as MinValue FROM Results) TMin  WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) AND 
(key=" & Rnd()*100000 & " OR TRUE)"

Tell me what the result is

 

[nwt002]

I tried adding

SELECT TOP 1 key, quote
FROM Results
ORDER BY NEWID()

and it tells me that "NEWID is an Undefined function. I guess Access doensn't support this function.

I know my earlier post said that the code you last posted didn't work, but I was messing with it yesterday, and when i took out the (")'s and the (&)'s it didn't give me any errors anymore, but it still returned the same record when I refresh the website. I'm not sure if removing those symbols messed up the logic of the code, but i just thougt i'd let you know incase they need to be in there.

I tried adding the Randomize(), but it gives me this:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I keep going back in Access and running the code that we finally got to work, and it really does seem to work well and give me random records, and it hasn't returned all my records yet like the code in my initial post did. Is there some type of script or code that you think I need on the webpage that will update or rerun the query when the page refreshes.

 

[BKQc]

From what I read here, you didn't seem to have try the last piece of code I suggested (modifying the ASP page. The goal of this is to force the request to be different each time though returning different results and not be cached (if caching is the problem). You shouldn't be playing with the query in Access anymore since we defined it was working. Now the problem is at the ASP server and/or at the browser level.

 

[nwt002]

Sorry i wasn't more specific. I reposted the script from above and colored it like i see it in Frontpage. I am only aloud to change the gray sections that say "webbot bot=", and then it updates the red section. I did try and add the last code that you posted (I colored it green so you can find it easier) When i add back the (")s and the (&)'s to the last part of the code i get this error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) AND (key= &Rnd()*100000 &'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

If I remove the second part of the code then i get this error:

Database Results Error
Description: Command text was not set for the command object.
Number: -2147217908 (0x80040E0C)
Source: Microsoft OLE DB Provider for ODBC Drivers

and if i move the Randomize() statement after "fp_sQry=", then I get the error that I mentioned in the last post.

[COLOR=gray]
    <!--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 [COLOR=green]Randomzie()s-sql="SELECT TOP 1 key, quote  FROM Results, (Select Min(key) as MinValue FROM Results) TMin  WHERE Key&amp;gt;=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) AND &lt;br&gt;(key= &Rnd()*100000 &" OR TRUE)" [/color]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 s-sql --><!--#include file="../../_fpclass/fpdblib.inc"-->
[/color]
[COLOR=red]<<% if 0 then %>[/color]

[COLOR=blue]SCRIPT Language[/color]="JavaScript">
[COLOR=red]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>");[/color]
[COLOR=blue]
</SCRIPT>
[/color]
[COLOR=red]
<% end if %>

<%
fp_sQry="SELECT TOP 1 key, quote  FROM Results, (Select Min(key) as MinValue FROM Results) TMin  WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) "

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=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="key"
fp_sMenuValue="key"
fp_iDisplayCols=2
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>[/color]

 

[nwt002]

Just to clarify, I can change the HTML and add additional script and delete anything I want, I just am only able to change the gray part if I want to edit what the wizard has generated for the database results part.

 

[BKQc]

Since you seem to make a big use of webbots and other FP specific stuff, maybe you should simply put it all back to the point where it was working but always showing the same result and transfer (again) to a FP forum... It is true that behind the scene it is ASP but it's almost all generated ASP and so by definition, ASP we shouldn't toy with... Sorry, I don'T think I'll be of use regarding FrontPage...

 

[nwt002]

That is ok. Thanks for getting me this far. I appreciate all the help.