Can't save decimal value

[MarcinWerra]

trafficstars

Is it possible to save a decimal value?

using EntityFrameworkCore.Jet;
using Microsoft.EntityFrameworkCore;
using System;
using System.Data.Jet;

namespace TestSaveDecimal
{
  public class Table
  {
    public int Id { get; set; }
    public decimal DecimalValue { get; set; }
  }

  public class Database : DbContext
  {
    public DbSet<Table> Table { get; set; }

    public Database() { }
    public static string File;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      if (optionsBuilder.IsConfigured)
        return;
      JetConfiguration.OleDbDefaultProvider = "Microsoft.Jet.OLEDB.4.0";
      optionsBuilder.UseJet($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={File}; Persist Security Info=False;").EnableSensitiveDataLogging();
    }
  }

  class Program
  {
    static void Main(string[] args)
    {
      try
      {
        Database.File = "New-File.mdb";
        using (var db = new Database())
        {
          db.Database.EnsureCreated();

          var t = new Table();
          db.Table.Add(t);
          t.DecimalValue = 1.23M;
          db.SaveChanges();
        }
      }
      catch (Exception exception)
      {
        System.Console.WriteLine(exception.Message);
        if (exception.InnerException != null)
          System.Console.WriteLine(exception.InnerException.Message);
      }
    }
  }
}

There is an "Inappropriate data type in the criterion expression" with HResult 0x‭80040E07‬ from "Microsoft JET Database Engine".

   w System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   w System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   w System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   w System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   w System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   w System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   w System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   w System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   w System.Data.Jet.JetCommand.InternalExecuteDbDataReader(String commandText, CommandBehavior behavior)
   w System.Data.Jet.JetCommand.ExecuteDbDataReader(CommandBehavior behavior)
   w System.Data.Common.DbCommand.ExecuteReader()
   w Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   w Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   w Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

[bubibubi]

I checked your code using 2.2.0 stable. I ported the code to a test case (Model79_CantSaveDecimalValue). You can find the test case in the repository. Actually the only difference is that I'm using an accdb file with x86 configuration but I don't think that they could be the problem. I can run your code and the result is 1 record inserted. The sql queries runned against the database are the following (I enabled sql trace in Jet EF Provider setting JetConfiguration.ShowSqlStatements to true).

vvv BeginTransaction (ReadCommitted)==========
ExecuteNonQuery==========
CREATE TABLE [Table] (
    [Id] int NOT NULL IDENTITY,
    [DecimalValue] decimal(18, 2) NOT NULL,
    CONSTRAINT [PK_Table] PRIMARY KEY ([Id])
);

--- Commit==========

vvv BeginTransaction (ReadCommitted)==========
ExecuteDbDataReader==========
INSERT INTO [Table] ([DecimalValue])
VALUES (@p0);
SELECT [Id]
FROM [Table]
WHERE 1 = 1 AND [Id] = @@identity;


@p0(Decimal) = 1,23
@@identity = 1
--- Commit==========

[GGanter]

I have the same mistake. I think it’s a problem of the language. In a german Accessdb it don’t work.

[MarcinWerra]

Hello Bubibubi,

I need to work on some older application that uses .mdb files, so I need to use "Microsoft.Jet.OLEDB.4.0" OleDbDefaultProvider. I do not want to install the Microsoft.ACE.OLEDB driver on client computers.

I am currently trying not to use decimal fields. I use float fields instead.

I tested the record addition directly through OleDb and the same problem occurred in every "Microsoft.Jet.OLEDB.4.0" and "Microsoft.ACE.OLEDB.12.0" driver.

My CultrueInfo is "pl-PL". Setting the CultrueInfo parameter to "en-US" at startup does not help.

If I change the region format in the System Control Panel to "English (United States)", then adding a record with the Deciaml value works correctly through OleDb and through EntityFramework. After changing the region format back to "Polish (Poland)" it doesn't work.

There may be a bug in these OleDb drivers when they are used in a country where the decimal point is a comma instead of a dot.

My test function:

static void TestAddDecimalUsingOledb()
{
  Console.WriteLine("Adding decimal value");
  try
  {
    System.Console.WriteLine("CurrentCulture.Name: {0}", System.Threading.Thread.CurrentThread.CurrentCulture.Name);
    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
    System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");

    OleDbConnection con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = New-File.mdb; Persist Security Info = False; ");
    // OleDbConnection con = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = New-File.mdb; Persist Security Info = False; ");

    var cmd = new OleDbCommand
    {
      Connection = con,
      CommandType = CommandType.Text,
      CommandText = "INSERT INTO [Table] ([DecimalValue]) VALUES(@p0);"
    };
    // cmd.Parameters.AddWithValue("@p0", 1.23M);   // Did not work
      cmd.Parameters.Add("@p0", OleDbType.Decimal).Value = 1.23M; // Did not work
    // cmd.Parameters.Add("@p0", OleDbType.Currency).Value = 1.23M; // It works, but it is Currency type

    con.Open();
    using (DbDataReader reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        object test1 = reader[0];
        Console.WriteLine(test1);
      }
    }
    Console.WriteLine("OK");
  }
  catch(Exception exc)
  {
    System.Console.WriteLine(exc.Message);
  }
}

[bubibubi]

I think that the problem is not related only to decimal point. With system set in it-IT (so the comma separates decimal values and the dot separate thousands) everything's working fine. Actually I'm trying to reproduce the issue. Using your test application, if I change current culture and current ui culture to pl-PL should it stop working? Because for me it still works.

EDIT Looking around there are a lot of issues about decimal and Access. But I'd like to reproduce your issue.

EDIT Reading the just inserted value from the database, the value is 123 and not 1.23. This also uncommenting cmd.Parameters.Add("@p0", OleDbType.Currency).Value = 1.23M;

[MarcinWerra]

Yes, with the Region set in the System Control Panel to "Polish (Poland)" I get the same error message all the time.

I checked other Regions: Italian (Italy) - works German (Germany) - works

I noticed that when in "Polish (Poland)" region I set the "Digit grouping symbol" to "dot", then decimal value writing works. By default, in Polish region the "Digit grouping symbol" is "space".

I also checked the use of "space" in other Regions, where "dot" is the default: Italian (Italy) - works German (Germany) - works English (United states) - also works

Only in the "Polish (Polish)" region "space" as a "Digit grouping symbol" does not work.

I checked it on various virtual machines with freshly installed systems:

• Windows Server 2016 Standard english
• Windows 10 Pro for Workstations polish
• Windows 10 Pro polish
• Windows 10 Home polish

The same results were found in these systems.

The solution to the problem may be setting a "dot" in the System Control Panel as a "Digit grouping symbol" for "Polish (Poland)" region.

[lauxjpn]

There is a StackOverflow answer about this issue.

We might be able to get around this issue by emitting the decimal value to SQL using the current culture settings. OleDb should then convert it back to the invariant culture.