Access SQL doesnt work in ASP page

Results 1 to 2 of 2

Thread: Access SQL doesnt work in ASP page

  1. #1
    Join Date
    Dec 1969

    Default Access SQL doesnt work in ASP page

    When I run this query in Access 2000 query builder it works fine returning a recordset. Then when I try to execute it in my ASP page I get a zero length recordset. Any ideas why?<BR><BR>SELECT *<BR>FROM tblReleases INNER JOIN tblImages ON tblReleases.ReleaseImageID1 = tblImages.ImageID<BR>WHERE ReleaseID IN <BR>(SELECT DISTINCT ReleaseID FROM tblTracks WHERE ArtistName like &#039;*October*&#039;);<BR><BR>Set oRsReleases = Session("DB").Execute(sqlQuery)<BR><BR>Thanks for your help.

  2. #2
    Join Date
    Dec 1969

    Default RE: Access SQL doesnt work in ASP page

    Firstly, lets look at that final line you posted:<BR>Set oRsReleases = Session("DB").Execute(sqlQuery)<BR>You&#039;re storing a connection object in session state? Stop. Don&#039;t ever do this. Your application will grind to a big halt with more than a few concurrent users. This is exceptionally bad practise. Create the object on each page, open it, use it, close it, and destroy it. There is NO reason for any ADO object to persist over the lifespan of a page. None at all.<BR><BR><BR>Next, onto your query. Your problem is that Access may well let you use "*" as wildcards in your query, but that&#039;s not good practise either. Wildcard characters in SQL are "%":<BR>[code language="T-SQL"]<BR>SELECT *<BR> FROM tblReleases<BR> INNER JOIN tblImages<BR> ON tblReleases.ReleaseImageID1 = tblImages.ImageID <BR> WHERE ReleaseID IN <BR> (SELECT DISTINCT ReleaseID FROM tblTracks WHERE ArtistName like &#039;%October%&#039;); <BR>[/code]<BR><BR>Thirdly. Sorry, I know this is preachy. Don&#039;t use "SELECT *". Always just select the fields you want. What happens if someone changes the structure of the DB table? What happens if you only use one field - you don&#039;t want ADO to have to pull back EVERY single field (from BOTH the joined tables!) - it&#039;s extremely inefficient...<BR><BR>If you have any questions on the first bit, PLEASE ASK. It&#039;s very bad practise, and it&#039;s worth nipping it in the bud before you develop a huge application based around it, just to find it falls over.<BR><BR>Craig.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts