MS Access 2019 SQL INSERT long parameter error

Jim Longlade 20 Reputation points
2023-05-08T18:59:51.7666667+00:00

Upgraded PC and MS Office.

Using MS Access 2019 MSO (Version 2304 Build 16.0.16327.20200) 32 bit on a Windows 11 Pro computer 10.0.22621 Build 22621 x64-based PC

Create a Simple access database Data.accdb containing the table Table1 with the Long-Integer field ID

Create a new C# Windows Form App (.Net Framework 4.7) - I'm using Visual Studio Community 2022 (64-bit) v17.5.4

Add a Button: TestLongButton and configure as below:

If param is defined to be 'int param = 1' the number will be added to the table correctly.

If param is defined to be 'long param = 1', I get the error:

"Command parameter[0] '' data value could not be converted for reasons other than sign mismatch or data overflow.

With existing C# apps which worked fine with the older version of office, I now get the error when trying to INSERT into an Access Database with long values:

'Microsoft.ACE.OLEDB.12.0' failed with no error message available, result code: E_OUTOFMEMORY(0x8007000E).

I needed to install the 32-bit of Access (and therefore Office) to support to legacy programs still using a 32 bit database.

       private void TestLongButton_Click(object sender, EventArgs e)
        {
            try
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Data.accdb"))
                {
                    conn.Open();
                    long param = 1;
                    using (OleDbCommand cmd = new OleDbCommand("INSERT INTO Table1([ID]) VALUES(?)", conn))
                    {
                        cmd.Parameters.AddWithValue("@ID", param);
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message + Environment.NewLine + ex.StackTrace); }
        }
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
390 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,916 questions
{count} votes

Accepted answer
  1. Jiachen Li-MSFT 31,321 Reputation points Microsoft Vendor
    2023-05-09T07:08:53.62+00:00

    Hi @Jim Longlade ,

    "Command parameter[0] '' data value could not be converted for reasons other than sign mismatch or data overflow.

    This problem is caused by a mismatch between the format of the data and the format in the database.

    'Microsoft.ACE.OLEDB.12.0' failed with no error message available, result code: E_OUTOFMEMORY(0x8007000E).

    This can occur if the program is attempting to allocate too much memory, or if there is insufficient memory available on the system to meet the program's requirements.

    Based on your problem description, it's possible that the application is running out of memory due to the limitations of the 32-bit architecture.

    32-bit applications are limited to using a maximum of 2GB of memory, which can lead to out-of-memory errors when working with large datasets or performing memory-intensive tasks.

    If you encounter both problems, it is recommended that you replace the long format in the database with the Integer format to reduce the memory footprint and match the int format in your code.

    Best Regards.

    Jiachen Li


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.