Data type mismatch in criteria expression for decimal with OleDbCommand

Question

I have C# code that creates dbf table using OleDbCommand:

service.SetCommand($@"CREATE TABLE {RequestTableName} (
CurrencyRate decimal(19,9) null,
/*some more fields*/)");

After creating table I insert values into my table:

 service.SetCommand(
                $@"INSERT INTO {RequestTableName} 
(CurrencyRate) 
VALUES
( @CurrencyRate)");

 service.SetParam("@CurrencyRate", row.CurrencyRate ?? 0.0m);

Now in _command.ExecuteNonQuery(); I get: "Data type mismatch in criteria expression". My value i try to insert in table: row.CurrencyRate = 91.1235

This code worked for float type: CurrencyRate float null (row.CurrencyRate was double?), the only change i've done is changed type in create table sql to CurrencyRate decimal(19,9) null and row.CurrencyRate changed to decimal?. I need 10 numbers before decimal point and 9 number after decimal point (numbers like this: 1234567890.123456789)

I tried to run code with service.SetParam("@CurrencyRate", 0); - it worked well without errors, so i assume other fields are not the reason of error and issue is related to CurrencyRate

What I've tried:

1. Used decimal instead decimal(19,9) in create table sql
2. Added Precision and scale to my SetParam() method to add those values to OleDbParameter
3. used numeric(19,9). (I know that it's the same as decimal, but it was just a wild guess, because i'm out of ideas what's wrong)
4. Used sql scripts to create table and tried insert the same data, no errors appeared when i've done it manually without using code. And checked that number 1234567890.123456789 fit into database.
5. Added Convert.ToDecimal(row.CurrencyRate) inside SetParam: service.SetParam("@CurrencyRate", Convert.ToDecimal(row.CurrencyRate ?? 0.0m));
6. checked order of columns in table and in insert query

In debug I see that DataSourceType = DBTYPE_Decimal and DataType = System.Decimal, probably something is wrong with precision, but as I can see decimal fit in (19,9) size

Any ideas how to get rid of this data mismatch error?

Answer 1

Absolutely not obvious for me, but i had to override OleDbType from Decimal to Currency:

service.SetParam("@CurrencyRate", row.CurrencyRate ?? 0.0m, OleDbType.Currency);

Where SetParam code is:

public void SetParam(string name, object value, OleDbType? oleDbTypeOverride = null) 
        {
            var parameter = new OleDbParameter(name, value ?? DBNull.Value);
            if (oleDbTypeOverride != null)
            {
                parameter.OleDbType = oleDbTypeOverride.Value;
            }

            _command.Parameters.Add(parameter); 
            
        }

Found solution here: https://forum.hibernate.org/viewtopic.php?p=2391147