Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Posts
    7

    Unhappy Unanswered: How to I combine these two

    I have a form on an ASP page that allows a user to input into one or more text boxes and perform a search.

    I use a whereclause, so my select statement look like this:-

    "SELECT * FROM RegistryDatabase " + whereclause

    This works fine, but now I've been told that when searching the database(which is basically a list of engineering drawings) only the highest revision of each drawing should be returned.
    I can do this by using this select statement:-

    "SELECT * FROM RegistryDatabase where RegistryDatabase.revision=(select max(revision) from RegistryDatabase self where self.whole_number = RegistryDatabase .whole_number)

    My problem is I can't get them to work together. Is it even posible to combine them into one SELECT statement.

    Can anyone help!
    Regards
    Pete

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can combine them, but you have to be careful about whether or not the new query is actually going to return the row(s) you want

    it all depends on whether the condition that you pass in via the form is true for all revisions of a drawing

    if it's true for some revision, but not the latest revision, what then?

    anyhow, try this --
    Code:
    SELECT * 
      FROM RegistryDatabase 
     where RegistryDatabase.revision =
           ( select max(revision) 
               from RegistryDatabase self 
              where self.whole_number = 
                    RegistryDatabase.whole_number )
       and whereclause
    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Feb 2002
    Posts
    7

    Unhappy How do I combine these two

    Hi Rudy
    I've tried that already and get an error:-

    Microsoft VBScript runtime error '800a000d'
    Type mismatch: '[string: "SELECT* FROM Regist"]'

    My whereclause is built dynamically and would look something like this if all 3 text boxes are used:-

    Where DrawingSeries like '%56%' and DrawingNumber like '%76006%' and DrawingTitle like '%control%'

    Like I said before, both select statements work fine on their own , but not together?

    Pete

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to be honest, i don't do asp, but i know how to track down your problem

    you are assembling a string as your query, then executing it

    before you execute it, display the string

    the generated sql will have a syntax error in it

    post it here if you're not sure


    rudy

  5. #5
    Join Date
    Feb 2002
    Posts
    7
    Hello Rudy

    I get :-

    Microsoft VBScript runtime error '800a000d'

    Type mismatch: '[string: "SELECT * FROM Regist"]'

    /Database/TMP939o46uzdx.asp, line 88

    if i use "and whereclause" as in your previous example.

    If I use + whereclause I get :-

    SELECT * FROM RegistryDatabase where RegistryDatabase.revision=(select max(revision) from dbo.RegistryDatabase self where self.whole_number = RegistryDatabase.whole_number) where Drawing_Series = 'xxxxxx' and Drawing_number = 'yyyyyy' and Drawing_Title = 'zzzzzz'
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'where'.

    /Database/TMP97ryp6uzjs.asp, line 93

    Hope this helps
    Pete

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have two WHERE keywords --

    SELECT * FROM RegistryDatabase where RegistryDatabase.revision=(select max(revision) from dbo.RegistryDatabase self where self.whole_number = RegistryDatabase.whole_number) where Drawing_Series = 'xxxxxx' and Drawing_number = 'yyyyyy' and Drawing_Title = 'zzzzzz'

    the second one should be and


    rudy

  7. #7
    Join Date
    Feb 2002
    Posts
    7

    You're a Super Star

    Rudy You're a Super Star!

    I Got it working!!!

    I tried changing the code that creates my whereclause to get rid of the extra "where" but it started getting a bit messy. To be honest there's a lot more than 3 text boxes, some with multi-word any order and other nasty bits of code.

    Took the cowards way out and used :-

    + Replace(whereclause, "where", " and ")

    I know its a bit naughty, but hey, it works.

    Thanks again

    Pete
    (The Master)

Posting Permissions

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