How do I stop mysql rounding up?

[Largest of Gregs]

I am having a small issue with prices being rounded up when inserted into the db.

Product price is £22.07

It is stored in the products table as £22.07

When I display the price on a page it shows as £22.07.

When I process a transaction is appears into the db as £22.1

The value being inserted into the db is...

FormatNumber(item.PricePerUnit,2)

How can I stop it being rounded up?

[dalamarthedark]

What's the datatype of the column?

[Largest of Gregs]

decimal

[Zero]

Where is FormatNumber from? Doesn't appear be a MySQL function.

What does 'processing' a transaction involve?

Also, post your full insert (or update) statement.

[dalamarthedark]

what's the precision of the decimal field? For a financial field it should be (18, 2) or have a precision of 4 to prevent rounding errors with VAT.

[Largest of Gregs]

i think its 19,4

I'll post more info when i get into work tomorrow

[Largest of Gregs]

Originally Posted by Zero

Where is FormatNumber from? Doesn't appear be a MySQL function.

Completely forgot about this sorry, Format number is an asp function to determine how many decimal places to use.

Turns out that using 2 places was the problem.

I changed
FormatNumber(item.PricePerUnit,2) to
FormatNumber(item.PricePerUnit,3)

Cheers again!

[Zero]

But why does that fix it? Looks like there is a deeper issue here.

[dalamarthedark]

FormatNumber looks like VBScript on the client side?

[Largest of Gregs]

Im not 100% sure why it is doing it, but I use the format number in my 'insert' statement...

strSQL = "INSERT INTO TRANSACTIONS_ITEMS(TransactionID,ProductName,Finish,Quantity,Price,ProductID,VAT,sectionCode) VALUES(" & _
                    transactionID & "," & _
                    "'" & AddSlashes(item.ProductName) & "'," & _
                    "'" & item.GetValue("Finish") & "'," & _
                    item.QuantityInBag & "," & _
                    FormatNumber(item.PricePerUnit,2) & "," & _
                    item.GetValue("PID")  & "," & _
                    item.GetValue("VAT") & "," & _
                    "'" & item.GetValue("sectionCode") & "')"

foirmat number,2 seems to set a price of £4.211 to £4.20 on insert and formatnumber,3 seems to set it to £4.21

[Woolies]

that to me sounds like formatNumber is using significant figures rather than decimal places.

I'd check what it does to a price of £12.888
(rounding to three significant figures would give an end result after SQL insert of 12.90 I think, which is still wrong even if working for 4.211)

[Zero]

Log your strSQL, see what actually is before executing for both those cases.

[Largest of Gregs]

on a bit of a deadline so don't have time for testing at the moment.

Problem is solved, main thing.

Thanks again to all who offered help

[m0nkeymafia]

Originally Posted by Woolies

that to me sounds like formatNumber is using significant figures rather than decimal places.

I'd check what it does to a price of £12.888
(rounding to three significant figures would give an end result after SQL insert of 12.90 I think, which is still wrong even if working for 4.211)

Originally Posted by Largest of Gregs

on a bit of a deadline so don't have time for testing at the moment.

Problem is solved, main thing.

Thanks again to all who offered help

Its not really solved until you have tested it with other cases...

[Zero]

http://pragprog.com/the-pragmatic-pr...ts/coincidence