DATABASE QUERY RESULTS HELP 1

nwt002 · Nov 18, 2005

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:

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)

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:



<<% 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 · Nov 18, 2005

Try putting () after your Rnd

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

nwt002 · Nov 18, 2005

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

xwb · Nov 18, 2005

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

Code:

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 · Nov 18, 2005

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 · Nov 18, 2005

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 · Nov 18, 2005

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?

Code:

</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 · Nov 18, 2005

Try

Code:

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

Code:

' 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 · Nov 18, 2005

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

Code:

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

nwt002 · Nov 21, 2005

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:

Code:

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

Code:

[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 · Nov 21, 2005

Try

Code:

[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 · Nov 21, 2005

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

nwt002 · Nov 21, 2005

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:

Code:

[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 · Nov 21, 2005

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

Code:

<%
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 · Nov 21, 2005

Code:

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 · Nov 23, 2005

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:

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>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

DATABASE QUERY RESULTS HELP 1

nwt002

MIS

xwb

Programmer

nwt002

MIS

xwb

Programmer

nwt002

MIS

xwb

Programmer

nwt002

MIS

xwb

Programmer

xwb

Programmer

nwt002

MIS

xwb

Programmer

nwt002

MIS

nwt002

MIS

nwt002

MIS

xwb

Programmer

nwt002

MIS

Similar threads

Part and Inventory Search

Sponsor