#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    49
    Rep Power
    2

    Odd issue with decimal recordsets and math functions


    Hi.

    So here's the situation. I have a database with GPS coordinates. They are stored as nchar data (a string).

    example:
    table1 (latitude, longitude)
    44.23,-78.44
    46.443,-78.443

    I also have a second table with GPS coordinates stored as varchar(10) data - which is also a string.

    table2 (latitude, longitude)
    43.33,-78.44
    45.46,-78.223

    I'm not sure how I ended up with two different types of the same data, but I did. I call a script that calculates the distance between your home city (table1) to a destination city (table 2). It does this with a mathematical formula between the two sets of GPS coordinates.

    Example:
    SELECT latitude,longitude FROM table1

    v1 = objrs("latitude")
    v2 = objrs("longitude")

    SELECT latitude, longitude FROM table 2
    v3 = objrs("latitude")
    v4 = objrs("longitude")

    (Result: v1 = 44.23, v2= -78.44, v3 =43.33, v4= -78.44)

    I could then add, subtract, multiply, etc.

    Tonight I changed table2 from varchar(10) to decimal(10,8) format. All non-numeric characters were removed, all whitespaces removed.

    After this change, my search results were all null. I tested the code and found that whenever I tried to perform a math equation between v1,v2 and v3,v4 it would result in a null value.

    My first question is how is it that I could do math functions between NCHAR and VARCHAR strings which aren't actual numerical values but I can't do a math function between an NCHAR string and a DECIMAL value?

    Now here's the weird Twilight Zone results...

    The problem is in the decimal table (table2) not the nchar table.

    For example:
    strSQL = "SELECT * FROM table2"

    v1 = (objrs("latitude"))
    v2 = (objrs("longitude"))
    h = v1 * 3

    results in a "type mismatch" error.

    I'm at a loss to understand how I can multiply an nchar value but I can't multiple a decimal value.

    To solve the issue I do this:
    SELECT * FROM table2"

    w1 = cdbl(objrs("latitude"))
    w2 = cdbl(objrs("longitude"))

    h = w1 * 3
    response.write h

    and it works!

    I am very confused tonight. Why am I converting decimal records for them to work ... as decimal values, when it should be the nchar being converted into decimals, no?
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,587
    Rep Power
    278
    nchar is Fixed-length string when you multiplying nchar to decimal system give you error because it is not -78.44 it is " -78.44 ", so convert second table to decimal and everything should work...
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    49
    Rep Power
    2
    yes I understand that but I'm more concerned/annoyed as to why converting the DECIMAL fields using CDBL works, when I think it should be the string being converted into a decimal that would be the fix.

    It's like taking a string vs a decimal and converting the decimal to double precision, still leaving me with a string multiplied by a numeric variable. It works, I'm just more bothered as to why.

Similar Threads

  1. Passing recordsets to user defined functions
    By Sneakz in forum Microsoft Access Help
    Replies: 3
    Last Post: December 19th, 2009, 06:54 AM
  2. ASP Math Functions
    By dcampos in forum ASP Development
    Replies: 1
    Last Post: August 6th, 2004, 05:07 PM
  3. Decimal Places Issue
    By Beagles in forum Microsoft Access Help
    Replies: 3
    Last Post: July 29th, 2004, 03:59 PM
  4. Math Functions From Access DB
    By joshua in forum ASP Development
    Replies: 3
    Last Post: June 11th, 2004, 12:45 PM
  5. using math functions and drawing graph
    By mediso in forum ASP Development
    Replies: 2
    Last Post: October 15th, 2003, 12:19 PM

IMN logo majestic logo threadwatch logo seochat tools logo