Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: please check this 'not null' SQL String

    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.

    Code:
    "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
    M

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry, doesn't work that way.

    You need a condition for each column
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Cheat. Execute:
    Code:
    "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

  4. #4
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    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:
    Code:
    "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"

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Drop the quotes from around the SQL statement for starters

    For the 'As w1' try running this
    Code:
    SELECT id As 'Example'
    FROM vehicles
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    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.

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    nope, sorry, couldn't figure out what I am supposed to do with your example George.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    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

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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).
    George
    Home | Blog

  12. #12
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    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',

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, let's try to solve the problem from a different vector and execute:
    Code:
    "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

  14. #14
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Thanks Pat,
    still getting the same error. here's more of the code (inc. your bit) to give you a bigger picture:

    Code:
      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.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Uncomment your 'response.Write(strSQL) and post the result.
    First glance suggests you have a problem with your BETWEEN statement
    George
    Home | Blog

Posting Permissions

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