Solved

Recors no randomising on web page (With Access db)

Posted on 2007-04-09
8
168 Views
Last Modified: 2010-03-20
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
0
Comment
Question by:Andy Green
  • 4
  • 4
8 Comments
 
LVL 11

Expert Comment

by:JohnModig
ID: 18875147
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
 
LVL 3

Author Comment

by:Andy Green
ID: 18877384
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
 
LVL 11

Expert Comment

by:JohnModig
ID: 18878504
Very strange indeed. How do you call the query in your ASP pages? May I see some of your code?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:Andy Green
ID: 18878654
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
 
LVL 11

Expert Comment

by:JohnModig
ID: 18878696
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
 
LVL 3

Author Comment

by:Andy Green
ID: 18878752
OK, how do I pass the 2 parameters in?

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

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 18878775
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
 
LVL 11

Accepted Solution

by:
JohnModig earned 125 total points
ID: 18884078
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 55
1 FROM DUAL wont work with additional columns ?? 4 37
SQL NULL vs Blank 26 36
sql server computed columns 11 31
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question