none
C# Can't create DBF files with right length of decimals with numeric type RRS feed

  • Question

  • Hello.

    I've been facing an annoying problem when I try to create a dbf file. I am using C# 4.5. When I set a column with Numeric type, the lenght of decimals is automatically set as 5. And no metter if I setted it as Numeric(10,0) or Numeric(10,1). I even tryed Numeric(10,10)!

    I tryed to create this file with ODBC and OleDB but both result was the same.

    There's my code simplified:

    ODBC:
    =======
    OdbcConnection oConn = new OdbcConnection(@"Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277; Dbq=c:\;");

    oConn.Open();

    string createTableSyntax = "Create Table Person2 (SEQUENCIA Numeric, City char(50), Phone char(20))";

    OBS: I get 42000 erro when I try use Numeric(10,0) with ODBC

    var cmd = new OdbcCommand(createTableSyntax, oConn);

    cmd.ExecuteNonQuery();

    ========
    OleDB
    ========

    OleDbConnection oConn = new OleDbConnection();

    oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties=dBASE III;";

    oConn.Open();

    string createTableSyntax = "Create Table Person2 (SEQUENCIA Numeric(10,0), City char(50), Phone char(20))";

    var cmd = new OleDbCommand(createTableSyntax, oConn);

    cmd.ExecuteNonQuery();



    Does anyone has any idea why this is happening?

    Thanks a lot!

                
    Tuesday, August 16, 2016 2:31 PM

Answers

  • Hi Danielle de Santana,

    Precision in dBASE III allows numbers with up to two-digit exponents and in dBASE IV numbers with up to three-digit exponents. Because numbers are stored as text, they are converted to numbers. If the number to convert does not fit in a field, unexplained results may occur.

    While dBASE allows a precision and a scale to be specified with a NUMERIC data type, it is not supported by the ODBC dBASE driver. The ODBC dBASE driver always returns a precision of 15 and a scale of 0 for a NUMERIC data type.

    A column created with the Numeric data type using the ODBC dBASE driver maps to the SQL_DOUBLE ODBC data type. Thus the data in this column is subject to rounding. This behavior is not the same as that of the NUMERIC data type in dBASE (type N), which is Binary Coded Decimal (BCD).

    For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/ms713987(v=vs.85).aspx

    If you want to Numeric type, I would suggest that you could use Sql express.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 17, 2016 5:51 AM
    Moderator

All replies

  • Hi Danielle de Santana,

    Precision in dBASE III allows numbers with up to two-digit exponents and in dBASE IV numbers with up to three-digit exponents. Because numbers are stored as text, they are converted to numbers. If the number to convert does not fit in a field, unexplained results may occur.

    While dBASE allows a precision and a scale to be specified with a NUMERIC data type, it is not supported by the ODBC dBASE driver. The ODBC dBASE driver always returns a precision of 15 and a scale of 0 for a NUMERIC data type.

    A column created with the Numeric data type using the ODBC dBASE driver maps to the SQL_DOUBLE ODBC data type. Thus the data in this column is subject to rounding. This behavior is not the same as that of the NUMERIC data type in dBASE (type N), which is Binary Coded Decimal (BCD).

    For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/ms713987(v=vs.85).aspx

    If you want to Numeric type, I would suggest that you could use Sql express.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 17, 2016 5:51 AM
    Moderator
  • Can you use DOUBLE? On the display side you can format it according to your specs.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, August 17, 2016 2:40 PM