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

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 --rudy
http://rudy.ca/

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

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

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

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

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)