DATABASE QUERY RESULTS HELP
DATABASE QUERY RESULTS HELP
(OP)
I have been posting this issue on a few different forums now to try and get it resolved. We've come close but we think the rest may be a frontpage issue. If anyone can't help or has any suggestions please let me know.
I've used the Frontpage 'Database Interface Wizard' to create a results page and a Database editor. My objective is to have the 'results page' display 1 random record from the database whenever the page is refreshed. at first I thought is was just something with the query, but i've been working with another member and he has created a query for me that seems to work well. The query he gave me is:
When I run the query in Access, it works great and returns a single random record everytime I rerun the query. The issue i am having is, on the webpage, whenever I refresh it returns one record, but it's the same record everytime. It's like the query isn't rerunning. I've tried clearing the cache but it still returns the same record. I last place I posted this was the ASP forum and the member I was working with said that even though it is an asp page, it is asp that has been generated by Frontpage, so maybe someone here might be able to help me. I will post the database portion of the webpage script below incase it will help, and the query part is colored green. Thanks for your time and if you need anymore info from me please let me know:
<!--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 FROM Results, (Select Min(key) as MinValue FROM Results) TMin WHERE Key>=(((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="0" botid="0" u-dblib="../../_fpclass/fpdblib.inc" u-dbrgn1="../../_fpclass/fpdbrgn1.inc" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region.</font></td></tr>" b-WasTableFormat="TRUE" startspan s-sql --><!--#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
%>
I've used the Frontpage 'Database Interface Wizard' to create a results page and a Database editor. My objective is to have the 'results page' display 1 random record from the database whenever the page is refreshed. at first I thought is was just something with the query, but i've been working with another member and he has created a query for me that seems to work well. The query he gave me is:
CODE
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)
FROM Results, (Select Min(key) as MinValue FROM Results) TMin
WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue)
When I run the query in Access, it works great and returns a single random record everytime I rerun the query. The issue i am having is, on the webpage, whenever I refresh it returns one record, but it's the same record everytime. It's like the query isn't rerunning. I've tried clearing the cache but it still returns the same record. I last place I posted this was the ASP forum and the member I was working with said that even though it is an asp page, it is asp that has been generated by Frontpage, so maybe someone here might be able to help me. I will post the database portion of the webpage script below incase it will help, and the query part is colored green. Thanks for your time and if you need anymore info from me please let me know:
<!--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 FROM Results, (Select Min(key) as MinValue FROM Results) TMin WHERE Key>=(((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="0" botid="0" u-dblib="../../_fpclass/fpdblib.inc" u-dbrgn1="../../_fpclass/fpdbrgn1.inc" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region.</font></td></tr>" b-WasTableFormat="TRUE" startspan s-sql --><!--#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
%>
RE: DATABASE QUERY RESULTS HELP
CODE
RE: DATABASE QUERY RESULTS HELP
RE: DATABASE QUERY RESULTS HELP
CODE
RE: DATABASE QUERY RESULTS HELP
RE: DATABASE QUERY RESULTS HELP
Basically what they're saying is that you should use rnd from vbscript: not rnd from SQL. Get vbscript to generate the rnd then multiply it by the range returned by sql.
RE: DATABASE QUERY RESULTS HELP
CODE
<p> </p>
<%
Dim objConn
Dim objRS
objConn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=/fpdb/quotes.mdb"
'Initialize ASP RND() function
Randomize()
intRandomNumber = Int(1000*Rnd)+1
'Return 3 random records
strSQL="SELECT TOP 3 Key, quote, Rnd(" & -1 * (intRandomNumber)&"*Key)" & "FROM Results " & "ORDER BY 3"
objConn.Open
Set objRS=objConn.Execute(strSQL)
%>
</body>
RE: DATABASE QUERY RESULTS HELP
CODE
...
'Return 3 random records
strSQL="SELECT TOP 3 Key, quote, Rnd(" & cstr(-1 * (intRandomNumber))&"*Key)" & "FROM Results " & "ORDER BY 3"
The rest of it looks something like this
CODE
field = array ("key", "quote", "random")
<table width="100%" border="1">
<thead>
<tr>
<%
for each f in field
Response.Write("<td><b>" & f & "</b></td>")
next%>
</tr>
</thead>
<tbody>
<%
if rs.eof then
Response.Write ("<tr><td colspan=<" & UBound(field) & "align=left width=""100%"">No records returned.</td></tr>")
else
do while not rs.eof
Response.Write ("<tr>")
for each f in field
Response.Write ("<td>" & rs(f) & "</td>")
next
Response.Write ("</tr>")
rs.MoveNext
loop
end if
%>
</tbody>
</body>
RE: DATABASE QUERY RESULTS HELP
CODE
RE: DATABASE QUERY RESULTS HELP
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/quotes_interface/Results/results_page.asp, line 99
Line 99 is the line below:
CODE
line 99 => objconn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes"
'Initialize ASP RND() function
Randomize()
intRandomNumber = Int(1000*Rnd)+1
All the code that I have added to the page is below:
CODE
objconn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes"
'Initialize ASP RND() function
Randomize()
intRandomNumber = Int(1000*Rnd)+1
'Return 3 random records
strSQL="SELECT TOP 3 Key, quote, Rnd(" & cstr(-1 * (intRandomNumber)) &"*Key)" & "FROM Results " & "ORDER BY 3"
%>
' the headings
field = array ("key", "quote")
<table width="100%" border="1">
<thead>
<tr>
<%
for each f in field
Response.Write("<td><b>" & f & "</b></td>")
next%>
</tr>
</thead>
<tbody>
<%
if rs.eof then
Response.Write("<tr><td colspan=<" & UBound(field) & "align=left width=""100%"">No records returned.</td></tr>")
else
do while not rs.eof
Response.Write("<tr>")
for each f in field
Response.Write("<td>" & rs(f)& "</td>")
next
Response.Write("</tr>")
rs.MoveNext
loop
end if
%>
</tbody>
</body>
RE: DATABASE QUERY RESULTS HELP
CODE
Dim objConn
Dim objRS
objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes;"
RE: DATABASE QUERY RESULTS HELP
RE: DATABASE QUERY RESULTS HELP
I'm not sure if that is right, but I get a different message now. The message I get now is:
' the headings field = array ("Key", "quote")
Microsoft VBScript runtime error '800a01c3'
Object not a collection
/quotes_interface/Results/results_page.asp, line 120
The code for line 120 is:
CODE
%>
' the headings
field = array ("Key", "quote")
<table width="100%" border="1">
<thead>
<tr>
<%
line 120 =>for each f in field
Response.Write("<td><b>" & f & "</b></td>")
next%>
</tr>
</thead>
<tbody>
<%
RE: DATABASE QUERY RESULTS HELP
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x9fc Thread 0xb60 DBC 0x3277014 Jet'.
/quotes_interface/Results/results_page.asp, line 104
CODE
Dim objConn
Dim objRS
set objConn=Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes;"
line 104 =>objConn.Open
RE: DATABASE QUERY RESULTS HELP
CODE
objConn.open connstr
field = array ("key","quote")
%>
RE: DATABASE QUERY RESULTS HELP
CODE
Dim objConn
Dim objRS
Dim svrpath
Dim strSQL
Dim objrs2
svrpath=server.MapPath("/")
'Initialize ASP RND() function
Randomize()
intRandomNumber = Int(1000*Rnd)+1
'Return 3 random records
'strSQL="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)"
strSQL="SELECT TOP 1 Key, quote, author, Rnd(" & cstr(-1 * (intRandomNumber)) &"*Key)" & "FROM Results " & "WHERE post='Yes'" & "ORDER BY 4"
set objConn=Server.CreateObject("ADODB.Connection")
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & svrpath & "\fpdb\quotes.mdb;Persist Security Info=False"
'connstr="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes;"
objConn.Open connstr
Set objRS=Server.CreateObject("ADODB.Recordset")
objrs.open strSQL, objconn
' the headings
field = array("author")
field2 = array ("quote")
%></span></font></p>
<p><font color="black"><span class="huge"><u><b>Thoughts To Think About</b></u>
<table width="360" border="0" height="150" align="center">
<tbody align="center">
<%
if objrs.eof then
Response.Write("<tr><td colspan=<" & UBound(field) & "align=center width=""100%"">No records returned.</td></tr>")
else
do while not objrs.eof
Response.Write("<tr>")
'for each f in field2
'Response.Write("<td colspan=1 style=color:blue align=center height=""10"" width=""100%"">" & objrs(f) & "</td>" & "</tr>")
'Next
'for each f in field
'Response.Write("<td colspan=1 style = color:black align=center height= ""10"" width=""100%"">" & "-" & " " & objrs(f) & "</td>" & "</tr>")
for each f in field2
Response.Write("<td colspan=1 style=color:blue align=center height=""10"" width=""100%"">" & objrs(f))
Next
for each f in field
Response.Write("<p>" & "<b>" & "<font color=black>" & "-" & " " & objrs(f) & "</font>" & "</b>" & "</td>" & "</tr>")
next
objrs.MoveNext
loop
objRS.close
objConn.close
end if
%>
</tbody>
</table>