[skalag]

hi, i have a value passed to a page to define sql query to populate select dropdown, when there is a valid value i get no errors, but when the value is "" (for no selection)i get an error from: response.write(serverHTMLEncode(TRIM... as TYPE MISMATCH...can anyone help?

code
__________________________________________

' increment counter
intRecordCounter = intRecordCounter + 1


Response.Write "<option value="""
Response.Write(server.HTMLEncode(Trim(rs.Fields("M odel"))))
Response.Write """"
Response.Write ">"

[rhs98]

try using cstr. Casts it as a string a belive

Response.Write "<option value="""
Response.Write(server.HTMLEncode(Trim(cstr(rs.Fields("Model")))))
Response.Write """"
Response.Write ">"

[skalag]

no it doesnt work, i get an error now saying invalid use of NULL: 'cstr'

[rhs98]

Could it be it's because there is a null value?

Either modify the sql to include

where blah='dss' and blah=2 and model is not null

or try this;

if rs.fields("model")<>"" then
Response.Write "<option value="""
Response.Write(server.HTMLEncode(Trim(rs.Fields("Model"))))
Response.Write """"
Response.Write ">"
end if

[skalag]

yes rhs 98, there are null values that are giving me trouble, i am working on a database that is not mine has quite a few null values in a number field used as date (1996, 1983, etc), when i search and select from DB it ignores the records with Null values..i have just found that about 50% of records are being ignored by my seasrch.How can i fix this? PS. Your little if statement fixed the response.write problem

when i use sql like this it ignores the records with NULL, is there any way around this cos i need to select these records, it seems like maybe the rs does have the records but cant write them or why else did it error?...i am a newbie at this of a few months only so im not sure what is happening...pleasse help!

SQL="SELECT Model, Make, Year, Colour, NewVehicle, Retailprice, Doors, Extras, Gears, StockNo FROM VEHICLE WHERE Model LIKE '%" & Request.Form("Hmodel") & "%' " & "AND Make Like '%" & Request.Form("Hmake") & "%' " & "AND Colour Like '%" & Request.Form("Hcolour") & "%' " & "AND Year Like '%" & Request.Form("Hyear") & "%' ORDER by MAKE ASC"

[rhs98]

if a record is matched by your query, then it will be returned even if it has nulls

May I suggest though that if you are not searching on all the fields that you not inculde them in the query. I.e. build the sql statement using if statements;

if request.form("x")<>"" then
  query=query& ' and x='"&request.form("x")&"'"
end if

it may help. Also removing the nulls from your database might be a good idea depending on if they are meant to be there

[skalag]

thanks RHS98, but it took me along time to figure out the above sql, so i dont know how to do as you say...and im also confused about these null values, i did a test page and it displayed all the records, leaving a blank for the null year fields, but then it stopped doing this and only displayed those fields with values and i dont know how i made this happen or where the other null records have gone.

[Frettmaestro]

You can do something like this:

SQL="SELECT IsNull(Model, 'unknown') AS Model, Make, Year, Colour,...

Doing it like this will make model = "unknown" if it holds a null-value.

[skalag]

Thanks Frettmaestro, i will try this and see if i can make it work.

[Frettmaestro]

Hmm, looks like I missed part of the question here. The method I just provided might not work after all.

If you insert these two lines in you code right before the sql-statement gets executed
Response.Write(SQL)
Response.end
and then run the page. You will see that the sql-query is not really looking good (you will have alot of empty '%%' in it if all of your fields are not filled out). It's the logic in your sql-statement that makes it reject the null/emty values so you need to change your logic to something that works. This works (I clipped this from a previous post of mine on another forum):

SQL = "SELECT * FROM myTable " & _
  "WHERE myTableID IS NOT NULL " '<- make sure that this is true no matter what! Important!
IF TRIM(Request.Form("Model")) <> "" THEN
  SQL = SQL & "AND Model LIKE '%" & TRIM(Request.Form("Model")) & "%' "
END IF
IF TRIM(Request.Form("Make")) <> "" THEN
  SQL = SQL & "AND Make LIKE '%" & TRIM(Request.Form("Make")) & "%' "
END IF
...
Set RS = Conn.Execute(SQL)

This way your sql will be built only serching the fields that the user has set values for. Hopefully you understand what happens...if not ask, and I will try to explain better...

[skalag]

i got it working, thanks Frettmaestro...it will help clean up my sql problems on other pages...

...also will this work for me? I will need to be more concise about my needs..I am performing a search by 4 select option drop downs, each determining the next drop downs RS of options, i had it working reasonably ok(still not right) but then tried to improve and now it works less well...i need to allow user to select between 1 and 4 search parameters before displaying the results, but some of the DB fields are necessarily and temporarily blank or null but i still need to select these for display...im a novice and need directions...if anyone wants to look at full code and offer good suggestions id be grateful...

[skalag]

that all works great Frettmaestro, it has tidied up my version of IF & SQL statements, and is much more efficient...but my problem now is that the search will narrow down to only one option and if this has a null value then it crashes..I need to convert null to a 'unknown' or something as you said, how can i do this?