Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP 3.0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 16th, 2007, 05:51 AM
Registered User
Join Date: Jan 2007
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL problem


This code isn't mine but I have been asked to look at amending it.

The page displays a number of pics the references are held in an access DB. I want to display them in a particular order using the order by statement. It also has a search function.

My problem is that the 'order by' must go after the 'where' and I can't figure out where to put it.

Currently the pics are displayed in the order they were entered into the DB. They each have pattern numbers which I would like to use to display.

I have made several attempts using 'order by patternNo'. I can get the pics to display in the correct order but then it messes up the search function. I can get the search function to display in order but not general display. I obviously want both to display in order of patternNo.

Here is the URL
I have put in a couple of response.writes to help.

Any advice would be great.



strSQL = "SELECT PatternID,c.Category,PatternNo,PatternName,Descrip t " & _
            "FROM Pattern p " & _
            "INNER JOIN Category c ON p.CategoryID=c.CategoryID "

    strWhere = "WHERE "

    If strCategoryID <> "0" Then _
            strWhere = strWhere & "p.CategoryID = " & strCategoryID & " AND "

    If strChkNew = "1" Then _
        strWhere = strWhere & "New=-1 AND "

    If strChkOffer = "1" Then _
        strWhere = strWhere & "Offer=-1 AND "

    If not request("txtPat") = "" Then _
        strWhere = strWhere & "PatternNo like '%" & request("txtPAT") & "%' AND "

    If Len(strWhere) > Len("WHERE ") Then
        strWhere = Left(strWhere,Len(strWhere)-4)
        strSQL = strSQL & strWhere

    End If

  #2 (permalink)  
Old December 16th, 2007, 07:33 AM
Registered User
Join Date: Jan 2007
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts

Sorted it myself :-)


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
sql problem shrisangeeta Classic ASP Databases 2 May 24th, 2006 09:58 AM
SQL Problem pannet1 BOOK: Access 2003 VBA Programmer's Reference 4 April 16th, 2005 10:03 PM
SQL problem Lexus SQL Language 2 January 20th, 2005 09:25 AM
SQL problem Clive Astley Access VBA 4 October 19th, 2004 10:42 AM
SQL Problem tjw Access 1 November 12th, 2003 09:22 PM

All times are GMT -4. The time now is 10:19 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.