Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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 :-)

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

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