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
LVL 3
Andy GreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnModigCommented:
Hi Andy.
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.OLEDB.4.0;Data Source="C:\MyWebsite\MyDatabase.mdb;"
'create a recordset
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn

'...do whatever you want with the data

'close the recordset
rs.close
set rs=nothing
--------------------------
Regards,
John
0
Andy GreenAuthor Commented:
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
0
JohnModigCommented:
Very strange indeed. How do you call the query in your ASP pages? May I see some of your code?
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Andy GreenAuthor Commented:
Hi John

Here is the code


<!--#include file="../../Connections/adovbs.inc" -->
<!--#include file="../../Connections/DataConn.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("County")
intServiceID = request.querystring("Service")
            
            '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.ServicetypeID = tblServiceXRef.ServiceTypeID "
            sql = sql & "WHERE (((tblCompanies.Live)=True) AND ((tblCounty.CountyID)=" & intCountyID & ") AND ((tblServiceType.ServicetypeID)= " & intServiceID & ")) "
            sql = sql & "ORDER BY  Rnd([tblCompanies.CompanyID]); "            
            
            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.Connection")
      DSNLessConn="DRIVER={Microsoft Access Driver (*.mdb)}; "
      'DSNLessConn=DSNLessConn & "DBQ= c:\user\database\system.mdb"
      DSNLessConn=DSNLessConn & "DBQ=" & server.mappath("..\..\Database\Wedding.mdb")
      WD_Conn.Open DSNLessConn

to display I'm just using movenext & loop.

Andy
0
JohnModigCommented:
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?
0
Andy GreenAuthor Commented:
OK, how do I pass the 2 parameters in?

This seems a lot of work for 125 points. :-)

Andy
0
Andy GreenAuthor Commented:
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
0
JohnModigCommented:
Pass two parameters like this:
-----------------------
rstLocalServices.open "MyQuery 'parameter1','parameter2'", WD_Conn,adopenKeyset
-----------------------
Notice the single quotes around the two parameters. Also notice the comma. It should be after the first parameter and not before.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.