DATABASE QUERY RESULTS HELP 1

[nwt002]

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:

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)

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="&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"-->



<<% 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
%>

 

[xwb]

Try putting () after your Rnd

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[COLOR=red]()[/color] + TMin.MinValue)

 

[nwt002]

Thanks for the quick response. I tried that, and the page still returns the same record everytime i refresh.

 

[xwb]

Have you tried checking what Rnd() is returning? Probably something like

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

I don't really know whether that will work or not. We're using Oracle where I'm based.

 

[nwt002]

I couldn't get 'randy' to work, but i added Rnd() to the SELECT statement and it shows the the Rnd() on the page. It is returning 0.533424019813538. Everytime I press refresh, it returns the same Rnd().

 

[xwb]

Have a look at

http://www.adopenstatic.com/faq/randomrecord.asp

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.

 

[nwt002]

I'm trying to use the code from the link you posted but i'm having some trouble. I've never had to open or create database connections or record sets before, so i've tried looking at some other post to see how they do it but i can't seem to get it right. I've tried different things, but i get different errors everytime i try something. What i have so far is posted below. does any of it look close to being correct?

</table>
<p>&nbsp;</p>

[COLOR=red]
<%
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)
%>
[/color]
</body>

 

[xwb]

Try

objconn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes"
...
'Return 3 random records
strSQL="SELECT TOP 3 Key, quote, Rnd(" & cstr(-1 * (intRandomNumber))&"*Key)" & "FROM Results " & "ORDER BY 3"

This is what is known as a DSN-less connection. There are quite a few links on this subject if you google it. DBQ is the name that has been set up for you - same as your former query.

The rest of it looks something like this

' the headings
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>

 

[xwb]

Oops - sorry - no random field. I don't really know what it will be called

field = array ("key", "quote")

 

[nwt002]

I am still recieving this error:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/quotes_interface/Results/results_page.asp, line 99

Line 99 is the line below:

<%
[COLOR=red]line 99 => [/color]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:

[COLOR=red]<%
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"
%>
[/color]

' the headings
field = array ("key", "quote")
[COLOR=blue]<table width=[/color]"100%" [COLOR=blue]border=[/color]"1">
[COLOR=blue]<thead>
<tr>[/color]
[COLOR=red]		
<%
for each f in field
	Response.Write("<td><b>" & f & "</b></td>")
next%>[/color]
[COLOR=blue]
	</tr>
	</thead>
<tbody>[/color]
[COLOR=red]
<%
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
	%>[/color][COLOR=blue]
	</tbody>
	</body>[/color]

 

[xwb]

Try

[COLOR=RED]
Dim objConn
Dim objRS

objConn = Server.CreateObject("ADODB.Connection")
[/COLOR]objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes[COLOR=RED];[/COLOR]"

I think you forgot to create the object

 

[nwt002]

I just tried adding that script but it still gives me the same message for the same line of code.

 

[nwt002]

I tried adding set in front of objConn = Server.CreateObject("ADODB.Connection").

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:

[COLOR=red]
%>[/color]

' the headings
field = array ("Key", "quote")
[COLOR=blue]<table width=[/color]"100%" [COLOR=blue]border=[/color]"1">[COLOR=blue]
	<thead>
		<tr>[/color]
[COLOR=red]		
<%
[b][COLOR=green]line 120 =>[/color][/b]for each f in field
	Response.Write("<td><b>" & f & "</b></td>")
next%>[/color][COLOR=blue]
		</tr>
		</thead>
		<tbody>[/color][COLOR=red]
<%[/color]

 

[nwt002]

ok i also just tried adding the below code. I don't know if any of this is actually helping anything, but now i get this message:

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

<%
Dim objConn
Dim objRS
set objConn=Server.CreateObject("ADODB.Connection")
[COLOR=red]Set objRS = Server.CreateObject("ADODB.Recordset")[/color]
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes;"
[COLOR=green]line 104 =>[/color][COLOR=red]objConn.Open [/color]

 

[xwb]

connstr = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes;"
objConn.open connstr
field = array ("key","quote")
%>

Sorry I sent you the wrong code - field should have been inside the %>

 

[nwt002]

I just made the changes but i still was recieving an error. I did some reading in some other forums and combined that with all the info you gave me and now the page is working. Thanks a lot for all your help. Below is the code that I ended up with:

    <%
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>