OLEDB for Old school DBASE or Foxpro databases, SUM with 2 decimal places
-
I'm back to the old foxpro databases using OLEDB. I'm suppose to pull all the items in a Sales Orders, and go back into the history database file, and get the total QYT and AMOUNT purchased in the past as well. I can do the math, but wow it's slow!, and I have an issue at the end with formatting the number with 2 decimal places for the cents. So I'm not sure if I need to fix it in the database call, or try and fix it when presenting the number in the PDF. So $85.30 appears as $85.3 In the past I have had trouble, but not for years since. The other one i.FPRICE works fine, so I'm scratching my head on this one. Here's what I have
SELECT
i.FCUSTNO
, i.FORDQTY
, i.FITEMNO
, i.FDESCRIPT
, i.FPRICE
, (SELECT SUM(s.FSHIPQTY) FROM ARTRS01H.dbf s WHERE s.FCUSTNO = i.FCUSTNO AND s.FITEMNO = i.FITEMNO) AS FSHIPQTY
, (SELECT SUM(s.FAMOUNT) FROM ARTRS01H.dbf s WHERE s.FCUSTNO = i.FCUSTNO AND s.FITEMNO = i.FITEMNO) AS FAMOUNT
FROM SOTRS01.dbf i
WHERE i.FSONO=@FSONOThe reader
If Not (reader.IsDBNull(5)) Then sIRi(rC).FATD_SHIPQTY = reader.GetValue(5)
If Not (reader.IsDBNull(6)) Then sIRi(rC).FATD_TOTAL = reader.GetValue(6)And in ASP.Net to create a PDF of the Order Confirmation
Dim lbl_item_FATD_TOTAL As Label
lbl_item_FATD_TOTAL = New Label("", 373, currentY, 45, 12, Font.Courier, 9, TextAlign.Right)
lbl_item_FATD_TOTAL.Text = String.Format("{0:c}", sSOI(idx).FATD_TOTAL)
currentPage.Elements.Add(lbl_item_FATD_TOTAL)Any Suggestions would be cool.
-
I'm back to the old foxpro databases using OLEDB. I'm suppose to pull all the items in a Sales Orders, and go back into the history database file, and get the total QYT and AMOUNT purchased in the past as well. I can do the math, but wow it's slow!, and I have an issue at the end with formatting the number with 2 decimal places for the cents. So I'm not sure if I need to fix it in the database call, or try and fix it when presenting the number in the PDF. So $85.30 appears as $85.3 In the past I have had trouble, but not for years since. The other one i.FPRICE works fine, so I'm scratching my head on this one. Here's what I have
SELECT
i.FCUSTNO
, i.FORDQTY
, i.FITEMNO
, i.FDESCRIPT
, i.FPRICE
, (SELECT SUM(s.FSHIPQTY) FROM ARTRS01H.dbf s WHERE s.FCUSTNO = i.FCUSTNO AND s.FITEMNO = i.FITEMNO) AS FSHIPQTY
, (SELECT SUM(s.FAMOUNT) FROM ARTRS01H.dbf s WHERE s.FCUSTNO = i.FCUSTNO AND s.FITEMNO = i.FITEMNO) AS FAMOUNT
FROM SOTRS01.dbf i
WHERE i.FSONO=@FSONOThe reader
If Not (reader.IsDBNull(5)) Then sIRi(rC).FATD_SHIPQTY = reader.GetValue(5)
If Not (reader.IsDBNull(6)) Then sIRi(rC).FATD_TOTAL = reader.GetValue(6)And in ASP.Net to create a PDF of the Order Confirmation
Dim lbl_item_FATD_TOTAL As Label
lbl_item_FATD_TOTAL = New Label("", 373, currentY, 45, 12, Font.Courier, 9, TextAlign.Right)
lbl_item_FATD_TOTAL.Text = String.Format("{0:c}", sSOI(idx).FATD_TOTAL)
currentPage.Elements.Add(lbl_item_FATD_TOTAL)Any Suggestions would be cool.
jkirkerx wrote:
Any Suggestions would be cool.
It's an unformatted decimal/double (as should be), until you convert it to a string. Below gives a predictable result:
Console.WriteLine(String.Format("{0:c}", 3.8)); Console.ReadLine();There could be two things wrong here - either your formatting isn't applied, or it is formatting correctly. If your formatting is applied, then check out the regional settings in the configuration - if your end-user behaves like I do, then there'll be a non-default value.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
jkirkerx wrote:
Any Suggestions would be cool.
It's an unformatted decimal/double (as should be), until you convert it to a string. Below gives a predictable result:
Console.WriteLine(String.Format("{0:c}", 3.8)); Console.ReadLine();There could be two things wrong here - either your formatting isn't applied, or it is formatting correctly. If your formatting is applied, then check out the regional settings in the configuration - if your end-user behaves like I do, then there'll be a non-default value.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
I just figured it out! It was the PDF generator, in which the number was being formatted correctly, but the space I allocated for the data was not wide enough, so somehow it truncated the decimal values after the . That was a head scratcher that I spent hours on this morning. But thanks for the response and help, appreciate it. :)