Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Posts
    11

    Unanswered: rs error with serverHTMLEncode

    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 ">"

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    try using cstr. Casts it as a string a belive

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

  3. #3
    Join Date
    Mar 2003
    Posts
    11
    no it doesnt work, i get an error now saying invalid use of NULL: 'cstr'

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Could it be it's because there is a null value?

    Either modify the sql to include
    Code:
    where blah='dss' and blah=2 and model is not null
    or try this;
    Code:
    if rs.fields("model")<>"" then
    Response.Write "<option value="""
    Response.Write(server.HTMLEncode(Trim(rs.Fields("Model"))))
    Response.Write """"
    Response.Write ">"
    end if

  5. #5
    Join Date
    Mar 2003
    Posts
    11
    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"
    Last edited by skalag; 03-13-03 at 08:21.

  6. #6
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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;
    Code:
    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

  7. #7
    Join Date
    Mar 2003
    Posts
    11
    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.

  8. #8
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    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.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  9. #9
    Join Date
    Mar 2003
    Posts
    11
    Thanks Frettmaestro, i will try this and see if i can make it work.

  10. #10
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    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):
    Code:
    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...
    Last edited by Frettmaestro; 03-14-03 at 09:43.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  11. #11
    Join Date
    Mar 2003
    Posts
    11
    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...
    Last edited by skalag; 03-14-03 at 10:42.

  12. #12
    Join Date
    Mar 2003
    Posts
    11
    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?

Posting Permissions

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