×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DATABASE QUERY RESULTS HELP

thread256-1153687

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:

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:


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

RE: DATABASE QUERY RESULTS HELP

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() + TMin.MinValue)

RE: DATABASE QUERY RESULTS HELP

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

RE: DATABASE QUERY RESULTS HELP

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.

RE: DATABASE QUERY RESULTS HELP

(OP)
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().

RE: DATABASE QUERY RESULTS HELP

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.

RE: DATABASE QUERY RESULTS HELP

(OP)
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>


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

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>

RE: DATABASE QUERY RESULTS HELP

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

CODE

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

RE: DATABASE QUERY RESULTS HELP

(OP)
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

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

Try

CODE


Dim objConn
Dim objRS

objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=quotes;"
I think you forgot to create the object

RE: DATABASE QUERY RESULTS HELP

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

RE: DATABASE QUERY RESULTS HELP

(OP)
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


%>

' 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

(OP)
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")
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

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

RE: DATABASE QUERY RESULTS HELP

(OP)
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>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close