Thousand separator insert via ODBC

aip00
aip00 Member Posts: 5
edited 2005-08-04 in Dynamics AX
I'm having an issue where I'm trying to insert a record to SQL via ODBC. The SQL table field is setup as type 'money'. When I tried to insert this field with a value over a thousand from Axapta, it gave me an error message. It turned out when I specified the field from Axapta, it added a thousand separator in it. So the statement like this:

ustr = 'INSERT INTO SKU_LOC';
ustr += ' (BATCHID, CUSTOMER_ID, LOC_ID, SKU, EVENT_ID, VENDOR_ID_PRIMARY, PRICE_REG_SALE)';
ustr += strfmt(' VALUES (\'%1\', \'%2\', \'%3\', \'%4\', \'%5\', \'%6\', %7), ', batchId, skuTable.CustomerId, skuTable.LocId, skuTable.SKU, skuTable.EventId, skuTable.VendorIdPrimary, skuTable.PriceRegSale);

failed because the skuTable.PriceRegSale adds a ',' (comma) to it. The ODBC sees it as an extra column (and that is exactly what the error message is).
I set the Extended Data Type of this field. thousand separator to 'No'. In Axapta, the form shows no thousand separator.

How can I pass this value without the thousand separator?

Comments

  • aip00
    aip00 Member Posts: 5
    Here is the solution.
    Add this to convert the value from Axapta in the INSERT statement:

    CAST(CONVERT(real, \'%7\', 0) as money)

    ;where %7 is the field with the thousand separator.

    Thanks.

Categories