Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Odd issue with decimal recordsets and math functions

Results 1 to 3 of 3
Share This Thread →
  1. #1
    sqlconfused is offline Contributing User
    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
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    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

  3. #3
    sqlconfused is offline Contributing User
    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.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates