#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    35
    Rep Power
    12

    string to number conversion trouble


    I'm passing variables via the URL to an ASP page and then using same to query an Access db and I keep getting the "data type mismatch in criteria expression" error. The variable is a milepost reading up to the thousandths. In the Access DB the data shows as field size = double and decimals as auto. I've tried using both the CDbl and the CDec functions to convert my variables to the correct data type prior to doing the SQL query. I had the same problem with two other variables but converted those to text in the db to fix those but don't want to do that with this one. Here is what I have:

    milepostS = Request.QueryString("MILEPOST")
    roadNumberS = Request.QueryString("ROAD_NUMBE")
    sequenceS = Request.QueryString("SEQUENCE")
    milepost = CDbl(milepostS)

    Conn.Open "CRIS_WEB"

    SQL = "SELECT * FROM culverts WHERE ROAD_NUMBE = '" & roadNumberS & "' AND MILEPOST = '" & milepost & "' AND SEQUENCE= '" & sequenceS & "'"

    Set RS = Conn.execute(SQL)

    How should I convert milepostS to the proper type?

    Thanks.
    Jeff
  2. #2
  3. No Profile Picture
    Overly white
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Fresno, CA
    Posts
    83
    Rep Power
    12

    Re: string to number conversion trouble


    The problem is not the variables but your SQL. When looking up numbers you don't use quotes around them. See if this solves the problem.

    Code:
    SQL = "SELECT * FROM culverts WHERE ROAD_NUMBE = " &
    roadNumberS & " AND MILEPOST = " & milepost & " AND
    SEQUENCE= '" & sequenceS & "'"
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Australia
    Posts
    3
    Rep Power
    0
    sounds like that mite be it .. only text matching in the query string will have quotes in the SQL statement.
    But sometimes even with the SQL statement being correct there mite still be a mismatch problem. Some reasons can be, you are parsing a Number (&milespost) but the Field in the database is of Text type so the SQL statement cant match the number in the Database.. or Vice Versa.

    Check the field type in your database and ensure it is in Number Format.

    To format the number of the variable parsed from your page. You can try : Assuming VbScript :-

    milepost = FormatNumber(milepostS)


    However i would first try the SQL given above but do note that all of the statement should be in single line. (just in case u copied and pasted )

    for multiple line SQL (taking the above SQL statement again) may look something like :

    SQL = "SELECT * FROM culverts WHERE ROAD_NUMBE =" _
    & roadNumberS & " AND MILEPOST = " & milepost & " AND _
    SEQUENCE= '" & sequenceS & "'"

    Hope i have helped

    cheers
    Peter Pavlidis
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    35
    Rep Power
    12

    SQL was bad


    Thanks both of you for your replies. It was the SQL statement error that defjamninja pointed out. Egad....
    Jeff

IMN logo majestic logo threadwatch logo seochat tools logo