the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.
"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"
Any ideas how I could implement this more robustly?
cheers
MSorry, doesn't work that way.
You need a condition for each column|||Cheat. Execute:
"SELECT id, make, model
, CAST(workToBeDone1 AS VARBINARY(10)) AS w1
, CAST(workToBeDone2 AS VARBINARY(10)) AS w2
, CAST(workToBeDone3 AS VARBINARY(10)) AS w3
, CAST(workToBeDone4 AS VARBINARY(10)) AS w4
, CAST(workToBeDone5 AS VARBINARY(10)) AS w5
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"If the Cast() columns do not ALL show NULL as their value, then you have data in the offending column(s). Empty strings, and sometimes even the constant "NULL" have been known to sneak into tables when you do not expect them!
-PatP|||thanks guys.
I'm sure my version was working fine until the database seemed to put something invisible into the columns.
I tried your code Pat but it returns "ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal."
What does the 'as w1' part do?
my code looks like this:
"SELECT id, make, model, CAST(workToBeDone1 AS VARBINARY(10)) AS w1, CAST(workToBeDone2 AS VARBINARY(10)) AS w2, CAST(workToBeDone3 AS VARBINARY(10)) AS w3, CAST(workToBeDone4 AS VARBINARY(10)) AS w4, CAST(workToBeDone5 AS VARBINARY(10)) AS w5 FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"|||Drop the quotes from around the SQL statement for starters ;)
For the 'As w1' try running this
SELECT id As 'Example'
FROM vehicles|||thanks georgev
sorry, I missed a crucial bit re the quotes: SQLstring="Select..."
I'll have a play with your example and see if I get it.|||nope, sorry, couldn't figure out what I am supposed to do with your example George.|||Run the thing in QA and see if you notice something.
Basically it's giving the column an alias http://doc.ddart.net/mssql/sql70/sa-ses_3.htm - scroll down to columns_alias :p|||can't use QA on this, I have to run scripts on pages on the server.
Not sure why I need aliases.
My database columns seem to contain invisible data, is there a way to discover if the columns have any meaningful data in them? NULL seems to be a bit flakey
I need to find cars that need work done - i.e. someone has inputted something like: 'replace tyres' in one of the workToBeDone fields for a Volvo. but my search is returning every car in the database because it is seeing something in the columns. (I think!).
I tried casting as varchar(255) - made no difference|||The "as W1" simply assigns an alias to the column as GeorgeV observed. It appears that your ADO implementation doesn't like the aliases.
If Query Anylyzer (or its equivalent) is available, then I'd use it instead of writing/changing code to support your ADO implementation. Operative word being "should", you should be able to simply drop the column names and move on without them.
-PatP|||And by drop the column names we don't mean physically dropping the columns... Just remove the "As ..." from your SQL statement.
The reason the aliases were applied in the first place because as soon as you perform any function on a column it loses the reference to the column name (because it's not the same as the column data any more!). The Aliases allow us to access the columns by referenec in ADO (or so I believe).|||I dropped the aliases, but it made no difference, I'm still getting:
'Item cannot be found in the collection corresponding to the requested name or ordinal',|||Ok, let's try to solve the problem from a different vector and execute:"SELECT id, make, model
, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"-PatP|||Thanks Pat,
still getting the same error. here's more of the code (inc. your bit) to give you a bigger picture:
Set linkRS = Server.CreateObject("ADODB.Recordset")
salePrice = request.Form("salePrice")
make=request.Form("make")
model2show=request.Form("model2show")
salePrice=request.Form("salePrice")
fuel=request.Form("fuel")
sold=request.Form("sold")
workOutstanding=request.Form("workOutstanding")
notOnWebsite=request.Form("notOnWebsite")
strSQL="SELECT id, make, model, model2show, registration, price FROM vehicles WHERE price BETWEEN "& salePrice &""
if make <> "" then strSQL = strSQL & " AND make = '" & make & "'"
if fuel <> "" then strSQL = strSQL & " AND fuel = '" & fuel & "'"
if model2show <> "" then strSQL = strSQL & " AND model2show = '" & model2show & "'"
if sold = "yes" then strSQL = strSQL & " AND sold = 'yes'"
if workOutstanding = "yes" then strSQL = "SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"
if notOnWebsite = "yes" then strSQL = strSQL & " AND active = 'no'"
strSQL = strSQL & " ORDER BY make"
'response.Write(strSQL)
linkRS.Open strSQL, oConn, 2, 3
if (linkRS.BOF and linkRS.EOF) then
response.Write("<p class=""inputRed"">No vehicles to display - try selecting fewer parameters</p>")
else
linkRS.moveFirst
Do while not linkRS.eof
make = linkRS("make")
'etc.
'etc.
most of this works fine, but the error message is odd because those fields do exist.|||Uncomment your 'response.Write(strSQL) and post the result.
First glance suggests you have a problem with your BETWEEN statement|||here you go:
SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL ORDER BY make|||Maybe it contain spaces, try this
where coalesce(workToBeDone1,workToBeDone2,workToBeDone3 ,workToBeDone4,workToBeDone5,'') != ''|||thanks,
same error msg tho'
AS P6, [7] AS P7, 
AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12