An OleDbCommand.Prepare() method throws the System.Data.OleDb.OleDbException: 'The parameter is incorrect.' exception

[VladimirDrobyshev]

The following code under the .NET Core 3 causes the System.Data.OleDb.OleDbException: 'The parameter is incorrect.' exception, but under .NET Framework, it works fine:

Code

        using (OleDbCommand command = new OleDbCommand()) {
            command.Connection = connection;
            command.CommandText = "insert into sales (id, units, cost_per_unit, discount, total_cost, sale_date, productId, RegionId, ChannelId, SectorId) values (@id, @units, @cost_per_unit, @discount, @total_cost, @sale_date, @productId, @RegionId, @ChannelId, @SectorId)";
            command.Parameters.AddRange(
                new OleDbParameter[] {
                    new OleDbParameter("@id", OleDbType.Integer),
                    new OleDbParameter("@units", OleDbType.Integer),
                    new OleDbParameter("@cost_per_unit", OleDbType.Integer),
                    new OleDbParameter("@discount", OleDbType.Integer),
                    new OleDbParameter("@total_cost", OleDbType.Integer),
                    new OleDbParameter("@sale_date", OleDbType.Date),
                    new OleDbParameter("@productId", OleDbType.Integer),
                    new OleDbParameter("@RegionId", OleDbType.Integer),
                    new OleDbParameter("@ChannelId", OleDbType.Integer),
                    new OleDbParameter("@SectorId", OleDbType.Integer)
                }
            );
            command.Prepare();

Call Stack

System.Data.OleDb.dll!System.Data.OleDb.OleDbCommand.ProcessResults(System.Data.OleDb.OleDbHResult hr)	Unknown
System.Data.OleDb.dll!System.Data.OleDb.OleDbCommand.ApplyParameterBindings(System.Data.Common.UnsafeNativeMethods.ICommandWithParameters commandWithParameters, System.Data.OleDb.tagDBPARAMBINDINFO[] bindInfo)	Unknown
System.Data.OleDb.dll!System.Data.OleDb.OleDbCommand.CreateAccessor()	Unknown
System.Data.OleDb.dll!System.Data.OleDb.OleDbCommand.InitializeCommand(System.Data.CommandBehavior behavior, bool throwifnotsupported)	Unknown
System.Data.OleDb.dll!System.Data.OleDb.OleDbCommand.Prepare()	Unknown
OleDbIssue.dll!OleDbIssue.SalesGenerator.Generate(System.Data.OleDb.OleDbConnection connection, System.DateTime startDate, int daysCount) Line 77	C#
OleDbIssue.dll!OleDbIssue.SalesGenerator.Run(string connectionString) Line 147	C#
OleDbIssue.dll!OleDbIssue.Program.Main(string[] args) Line 8	C#

Sample project

[jader1313]

  You don't need to use parameter names in VALUES, but just ? placeholders. In .Net Core 3.0 or 3.1 with 1 parameter works, but with 2 or more parameters error occurs. Following example:

        string StrConn = "Provider=MSOLEDBSQL;Server=(localdb)\\MSSQLLocalDB;Database=SarathlalDb;DataTypeCompatibility=80;Integrated Security=SSPI; ";
        using (OleDbConnection connection = new OleDbConnection(StrConn))
        {
            connection.Open();
            using (OleDbCommand command = new OleDbCommand())
            {
                command.Connection = connection;
                command.CommandText =
                    "INSERT INTO Employees(Id, Name) VALUES(?,?)";

                var par = new OleDbParameter();
                par.OleDbType = OleDbType.VarChar;
                par.Size = 250;
                par.Value = Guid.NewGuid().ToString();
                command.Parameters.Add(par);

                par = new OleDbParameter();
                par.OleDbType = OleDbType.VarChar;
                par.Size = 250;
                par.Value = "Test";
                command.Parameters.Add(par);
                command.Prepare();
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (InvalidOperationException e)
                {
                    Console.WriteLine("ERRO: Could not perform INSERT INTO.", "SQL", command.CommandText);
                    throw e;
                }
            }
        }

[jader1313]

I believe the problem is related to this #981

[jader1313]

I tested using a LocalDB database and OLE DB Driver for SQL Server and got a error too.

"Provider=MSOLEDBSQL;Server=(localdb)\MSSQLLocalDB;Database=SarathlalDb;DataTypeCompatibility=80;Integrated Security=SSPI; "

In .Net Core 3.0 or 3.1 tests with 1 parameter works, but with 2 or more parameters error occurs.

@JoeHz tests the command with 1 parameter just to confirm what I said:

Select * from [PlayerNumbers] where [Section] = ?

[jader1313]

@maryamariyan @divega @cheenamalhotra @danmosemsft @saurabh500 @msftgits
We are converting an EF6 provider to EF Core, but we are not getting ahead due to this problem with OleDbCommand with more than one parameter. We are stopped for almost 60 days, can someone help in solving this bug or at least give some forwarding?

[jader1313]

@DevExpress-Visualization see #981 and check if that didn't solve your problem please

[saurabh500]

@DevExpress-Visualization @jader1313 Has this got anything to do with x86 specific apps or is the issue reproducible on x64 as well? I havent tried a repro yet, but wanted to check before I try this out.

[jader1313]

@saurabh500 I don't know. @DevExpress-Visualization did not specify in his initial post which database/driver he was having problems with, but since it is a command with parameters, it may have been solved with the issue 981 solution. So I believe that @DevExpress-Visualization should retest.

[VladimirDrobyshev]

Hi @saurabh500 and @jader1313,

Sorry for the delay.
We use the Access (*.mdb) database with the Microsoft.Jet.OLEDB.4.0 provider.
The sample from my first post works without exceptions in the case of the x64 configuration.
Unfortunately the Jet OLEDB provider is not compatible with it.
Following the microsoft-jet-oledb-4-0-provider-is-not-registered-on-the-local-machine topic I replaced it with the Microsoft.ACE.OLEDB.12.0 provider and it works fine.

[saurabh500]

@DevExpress-Visualization Thanks for the information.
#981 did fix some problematic aspects on x86, but there are more areas that I can see in code, and there are tests that still fail on x86. I am tracking those as well and working on the fixes.

[saurabh500]

Also based on @DevExpress-Visualization this seems like a x86 specific issue.

[jader1313]

From what @DevExpress-Visualization reported, it has already been resolved and I believe the ticket can be closed @saurabh500.

[jader1313]

From what @DevExpress-Visualization reported, it has already been resolved and I believe the ticket can be closed @saurabh500 .

[saurabh500]

He moved to using the ACE driver for the x64 arch where the problems are not occurring. I will test if Jet driver with x86 app works or not.

[ajcvickers]

@saurabh500 Do you believe that there is still work to be done here?