How to hold 6 decimal digits without losing precision

[1164961]

Hi,
I have columns in SQL server defined as numeric(15,6).
However in my program (written in Delphi 5), I cannot insert the exact values in these columns because none of the field types (tfloatfield,tcurrencyfield) can support representing 6 decimal digits exactly.
If I use tfloatfield , then when I enter for example 34.536 in the grid then this value is hold in the tfloatfield as 34.535999.
If I use tcurrencyfield, then 34.536 is hold as 34.536 but I cannot enter 34.536789 because tcurrencyfield supports only 4 decimal digits.
What do you suggest to solve this problem?

Thanks in advance

Caglar Okat

 

[Zathras]

Float should be what you want, but watch out for the EnableBCD property. Borland defaults that to true which limits you to 4 decimal places. Set it to false and it should be ok.

 

[1164961]

Thanks but it doesn't help. ENABLE BCD is already false when I perform those operations.

 

[Zathras]

I don't know what the problem is. I just created a quick test table and simple application and it handles 6 decial places just fine. I'm using ADO. If you're using BDE, I would suggest starting a new thread with the same problem, but provide more details (such as whether you are using ADO or BDE, what kind of grid, table definition, etc.)

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, Grids, DBGrids, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

end.

 

[1164961]

Hi,
Thanks very much for your help Zathras.
I am using dxDBGrid,Delphi 5, TQuery,BDE 5.2.
The problem is not related about the grid I guess.Because,

assume that I have got a column in my table like that:

INVOICE_TOTAL numeric(15,6)

and a TFloatField object related with this column.

Then when the following code is run (after the query is opened of course),
------------------------------------------------------
Query1.edit;
Query1INVOICE_TOTAL.asfloat:=34.538;
--------------------------------------------------------
then value of Query1INVOICE_TOTAL.asfloat is 34.537999.

I think the problem is with the setasfloat function of
the TFloatField.This function calls dbiputfield() function of the BDE.After the call to dbiputfield(), the value changes from 34.538 to 34.537999.

I cannot solve this problem.I use the latest BDE.

Thanks in advance...

 

[Zathras]

I can't reproduce your symptoms using Microsoft SQL Server. What database engine are you using?

It looks like you are running into the fundamental problem with converting decimal values to binary. That is why Delphi provides the currency type and the Binary Coded Decimal (BCD) option.

If you can't re-design your database, then all you can do is to be sure to use rounding functions on all of your data before printing the invoice. If you round 34.537999 to three places you will get 34.538 which is what you put in.

On the other hand, if you only really need 3 decimal places of precision, set Enable BCD to true and that may solv your problem. Why is the database column defined with 6 places if you are only going to use 3?

 

[1164961]

Hi,

I have to hold the unit-price column with 6 decimal digits (I live in Turkey and 6 zeros will be extracted from the turkish lira at 1 January 2005).So I certainly need it.
I also am using SQL Server 2000.
Since I need 6 decimal digits, Currency and BCDField don't help.
As I told in my last reply, I cannot assign exact numbers to floatfield with 1,2,...6 decimal digits (not every number causes problem but some of them).
I stuck at this point , so I also decided to make roundings inside the code.
Thanks for answers...