Andy Green
asked on
Recors no randomising on web page (With Access db)
Hi Experts.
I have an Access database, and a website (dev only at this time) and I'm getting a parculiar problem and wondered if anyone else has come across it.
I have a query that hits the database and should return the records in a randomised fashion. If I run the query from the database it works, but the records always come back in the same order from the website. If I write the sql to the page and run it in Access the records do ranomise so ithe SQL generated from the page is working. I have placed every bit of 'do not cache' code I can find in the page, and the connection is set to nothing and closed every time.
I have tried different cursors all to no avail.
Can anyone offer and help.
Andy
I have an Access database, and a website (dev only at this time) and I'm getting a parculiar problem and wondered if anyone else has come across it.
I have a query that hits the database and should return the records in a randomised fashion. If I run the query from the database it works, but the records always come back in the same order from the website. If I write the sql to the page and run it in Access the records do ranomise so ithe SQL generated from the page is working. I have placed every bit of 'do not cache' code I can find in the page, and the connection is set to nothing and closed every time.
I have tried different cursors all to no avail.
Can anyone offer and help.
Andy
ASKER
Hi John
This is exactly what I'm doing, the sql runs and returns randomised records, and each time I rerun the query I get a differtent order, but when run from the web page they always return in the same order no matter what I do (I've even appended the data and time to the url so the can be no caching and still I get the same order.
I'm using ASP (Classic) and just looping trhough the records and displaying them.
Strange eh!
ANdy
This is exactly what I'm doing, the sql runs and returns randomised records, and each time I rerun the query I get a differtent order, but when run from the web page they always return in the same order no matter what I do (I've even appended the data and time to the url so the can be no caching and still I get the same order.
I'm using ASP (Classic) and just looping trhough the records and displaying them.
Strange eh!
ANdy
Very strange indeed. How do you call the query in your ASP pages? May I see some of your code?
ASKER
Hi John
Here is the code
<!--#include file="../../Connections/ad ovbs.inc" -->
<!--#include file="../../Connections/Da taConn.asp "-->
<%
'Anti cache
response.expires = 0
response.ExpiresAbsolute = Now() -1
response.addheader "pragma", "no-cache"
response.addheader "cache-control","private"
response.CacheControl = "no-cache"
intCountyID = request.querystring("Count y")
intServiceID = request.querystring("Servi ce")
'Local Recordset
sql = "SELECT tblCompanies.CompanyID, tblServiceType.CategoryID, tblCompanies.CompanyName, tblCompanies.ContactName, tblCompanies.Address1, tblCompanies.Address2, tblCompanies.Town, tblCompanies.PostCode, tblCompanies.Coverage, tblCompanies.Email, tblCompanies.Website, tblCompanies.Telephone, tblCompanies.JoiningDate, tblCompanies.PromoText, tblCompanies.Imagepath, tblCompanies.Notes, tblCounty.CountyName, tblServiceType.Servicetype , tbllevel.LevelID, tbllevel.LevelName, tblCompanies.RankingSeed "
sql = sql & "FROM tblServiceType INNER JOIN (tblCounty INNER JOIN (((tbllevel INNER JOIN tblCompanies ON tbllevel.LevelID = tblCompanies.LevelID) INNER JOIN tblCountyXref ON tblCompanies.CompanyID = tblCountyXref.CompanyID) INNER JOIN tblServiceXRef ON tblCompanies.CompanyID = tblServiceXRef.CompanyID) ON tblCounty.CountyID = tblCountyXref.CountyID) ON tblServiceType.Servicetype ID = tblServiceXRef.ServiceType ID "
sql = sql & "WHERE (((tblCompanies.Live)=True ) AND ((tblCounty.CountyID)=" & intCountyID & ") AND ((tblServiceType.Servicety peID)= " & intServiceID & ")) "
sql = sql & "ORDER BY Rnd([tblCompanies.CompanyI D]); "
response.write sql
set rstLocalServices = server.createobject("adodb .recordset ")
rstLocalServices.open sql, WD_Conn,adopenKeyset
this is my connection
set WD_Conn= Server.CreateObject("ADODB .Connectio n")
DSNLessConn="DRIVER={Micro soft Access Driver (*.mdb)}; "
'DSNLessConn=DSNLessConn & "DBQ= c:\user\database\system.md b"
DSNLessConn=DSNLessConn & "DBQ=" & server.mappath("..\..\Data base\Weddi ng.mdb")
WD_Conn.Open DSNLessConn
to display I'm just using movenext & loop.
Andy
Here is the code
<!--#include file="../../Connections/ad
<!--#include file="../../Connections/Da
<%
'Anti cache
response.expires = 0
response.ExpiresAbsolute = Now() -1
response.addheader "pragma", "no-cache"
response.addheader "cache-control","private"
response.CacheControl = "no-cache"
intCountyID = request.querystring("Count
intServiceID = request.querystring("Servi
'Local Recordset
sql = "SELECT tblCompanies.CompanyID, tblServiceType.CategoryID,
sql = sql & "FROM tblServiceType INNER JOIN (tblCounty INNER JOIN (((tbllevel INNER JOIN tblCompanies ON tbllevel.LevelID = tblCompanies.LevelID) INNER JOIN tblCountyXref ON tblCompanies.CompanyID = tblCountyXref.CompanyID) INNER JOIN tblServiceXRef ON tblCompanies.CompanyID = tblServiceXRef.CompanyID) ON tblCounty.CountyID = tblCountyXref.CountyID) ON tblServiceType.Servicetype
sql = sql & "WHERE (((tblCompanies.Live)=True
sql = sql & "ORDER BY Rnd([tblCompanies.CompanyI
response.write sql
set rstLocalServices = server.createobject("adodb
rstLocalServices.open sql, WD_Conn,adopenKeyset
this is my connection
set WD_Conn= Server.CreateObject("ADODB
DSNLessConn="DRIVER={Micro
'DSNLessConn=DSNLessConn & "DBQ= c:\user\database\system.md
DSNLessConn=DSNLessConn & "DBQ=" & server.mappath("..\..\Data
WD_Conn.Open DSNLessConn
to display I'm just using movenext & loop.
Andy
Ok, so you are in fact using the sql written in ASP. Try to call the Access query from ASP instead.
Change this line:
-----------------------
rstLocalServices.open sql, WD_Conn,adopenKeyset
-----------------------
To something like this:
-----------------------
rstLocalServices.open "MyQuery", WD_Conn,adopenKeyset
-----------------------
...and you will get all the fields from your Access query instead. See what I mean?
Change this line:
-----------------------
rstLocalServices.open sql, WD_Conn,adopenKeyset
-----------------------
To something like this:
-----------------------
rstLocalServices.open "MyQuery", WD_Conn,adopenKeyset
-----------------------
...and you will get all the fields from your Access query instead. See what I mean?
ASKER
OK, how do I pass the 2 parameters in?
This seems a lot of work for 125 points. :-)
Andy
This seems a lot of work for 125 points. :-)
Andy
ASKER
This has something to do with using Randomize, but I still cant make it work. It's because the Rnd() is inside the SQL, so I'd have thought it would not be needed.
On On.
Andy
On On.
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So if I understand you correctly, your database query works, right? So why not just simply run the query from your website?
Assuming you are using ASP, the code would be something like this:
--------------------------
'name of the database query
sql = "MyQuery"
'connectionstring
conn = "Provider=Microsoft.Jet.OL
'create a recordset
set rs = Server.CreateObject("ADODB
rs.Open sql,conn
'...do whatever you want with the data
'close the recordset
rs.close
set rs=nothing
--------------------------
Regards,
John