none
Inserting decimal value in Access Db with OleDb gives DB_E_ERRORSOCCURED RRS feed

  • Question

  •  

    Hi,

     

    I'm using System.Data.OleDb provider to access a table in an Access (Jet) DB. The table has a "decimal" column, 18 digits. Everything works fine so far, except for one certain computer: On that machine, inserting a value, e.g. a 0, gives DB_E_ERRORSOCCURED.

     

    Regional settings are not different from other computers where the insert succeeds.

    OleDb provider is Microsoft.Jet.OLEDB.4.0.

    I use OleDbConnection, OleDbCommand, OleDbParameter to insert the value with a literal SQL command using parameters, like "insert into MyTable (MyColumn) values (?)".

    .NET framework is 3.5.

     

    Does anyone have an idea what might cause this problem? Where should I search for differences between that certain machine and the others?

     

    Thanks,

     

    Matthias

     

    Sunday, April 20, 2008 7:52 PM

All replies

  • Can you post exact exception message and the code that creates parameter and executes SQL statement?

     

    Monday, April 21, 2008 9:44 AM
    Moderator
  • The following code reproduces the error on the respective machine.
    It uses an empty database "DecimalBug.mdb" in the current directory.

     

    When inserting a long value (123L in the code below), the following error occurs:
    "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    Additional information: Unspecified error: DB_E_ERRORSOCCURRED(0x80040E21)"
    It works when inserting a decimal instead (123m). This would be a work around.
    But when reading the value, it always return 0m, not the actual value. The correct value is in the DB, I can see it when opening the file with Access.
    Inserting and reading works if I use column type "currency" instead of "decimal".

     

    So far, we haven't found relevant differences between the machine where the error occurs, and other machines.
    Versions are:
    .net framework 3.5
    msjet40.dll 4.0.8618.0
    msjetoledb40.dll 4.0.8227.0

    using System;

    using System.Data.Common;

    class Program

    {

    const string providerName = "System.Data.OleDb";

    const string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DecimalBug.mdb";

    static void Main(string[] args)

    {

    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

    DbConnection connection = factory.CreateConnection();

    connection.ConnectionString = connectionString;

    connection.Open();

    DbCommand command = connection.CreateCommand();

     

    // Create the table

    command.CommandText = "create table testtable (testcol decimal)";

    command.ExecuteNonQuery();

     

    // Insert a value

    command.CommandText = "insert into testtable (testcol) values (?)";

    DbParameter parameter = command.CreateParameter();

    parameter.Value = 123L; // Gives DB_E_ERRORSOCCURED when inserting, but 123m works.

    command.Parameters.Add(parameter);

    command.ExecuteNonQuery();

     

    // Read that value

    command.CommandText = "select testcol from testtable";

    using (DbDataReader reader = command.ExecuteReader())

    {

    while (reader.Read())

    {

    object test1 = reader[0]; // Value is always 0 {decimal}

    }

    }

    connection.Close();

    }

    }

    Tuesday, April 22, 2008 9:32 AM
  • If it is decimal data type then you need to specify Scale and Presision properties for your parameter and also specify that it is decimal type. In your case you did not specify any information, and by default I believe it is string type of parameter. When you call CreateParameter method, you should provide additional information with the type and then set Scale and Precision properties to appropriate values.

     

    Thursday, April 24, 2008 9:48 AM
    Moderator
  • OK, I'll try setting the DbType explicitly.

    However, MSDN states: "If the type is not specified, ADO.NET infers the data provider Type of the Parameter from the Value property of the Parameter object." And the question is: Why does it work an all machines but one?

    One more question: How can I specify scale and precission? DbParameter doesn't have such properties, just a Size.

     

    Thanks!
    Thursday, April 24, 2008 10:08 AM