topic RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB in Product Forum

C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Tue, 11 Jul 2023 11:30:00 GMT

We use Common - OLEDB - ExecuteQuery, with an update query. This causes , memory error, specifically, a C0000005 error is an access violation error caused by a buffer overrun. What could be the solution to this ? Is there any alternative to this ?

------------------------------
Gnana Gracy Antony Santiago Raj
Developer
BNP
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

PvD_SE — Tue, 11 Jul 2023 12:46:00 GMT

Hi Gnana,

If you could describe what you're trying to do, then we can have a look at either improving it or finding an alternative.

------------------------------
Happy coding!
---------------
Paul
Sweden

By all means, do not mark this as the 'Best answer'!

------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Wed, 12 Jul 2023 10:53:00 GMT

Hi Paul,

The process reads data from an Excel file, it runs a query to exclude some of this data from the main update to the database. This is what we do. Since the no. of records have exceeded 5000 with 20 fields per row, this error occurred.

Thanks

------------------------------

Developer
BNP
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

PvD_SE — Wed, 12 Jul 2023 11:29:00 GMT

Hi G,

5k rows is not much, not even for 20 columns. OLEDB should be able to handle this without any problems.

How much does the selection exceed 5k rows?
Does the error also occur if you only try to get a few rows with the SQL?
What does your SQL look like?

------------------------------
Happy coding!
---------------
Paul
Sweden

By all means, do not mark this as the 'Best answer'!

Off duty:
https://plusservices.se/my books/
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Wed, 12 Jul 2023 13:16:00 GMT

Hi Paul, The sql query is this

"UPDATE [Sheet1$] SET [Sheet1$].[IGNORE] = 'T' WHERE [Sheet1$].[AGREEMENT ID] = '" & [Agreements To Exclude Collection.AGREEMENT ID] & "'".

We hve not checked with the few rows.

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

PvD_SE — Thu, 13 Jul 2023 07:18:00 GMT

Hi Gracy,

I don't think you'll be needing to repeat the [Sheet1$] on each part of the SQL. So I reckon the following should do:
"UPDATE [Sheet1$] SET [IGNORE] = 'T' WHERE [AGREEMENT ID] = '" & [Agreements To Exclude Collection.AGREEMENT ID] & "'".

On the other hand it seems the use of ' and " in the SQL do not really add up. You may have a look at that.

------------------------------
Happy coding!
---------------
Paul
Sweden

(By all means, do not mark this as the 'Best answer'! )
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Tue, 18 Jul 2023 13:53:00 GMT

Hi Paul, We are still stuck up with the above query and get the error. Is there anything else you feel not quite right here ?

Internal : Could not execute code stage because exception thrown by code stage: No value given for one or more required parameters.

Thanks,

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Tue, 18 Jul 2023 13:55:00 GMT

The quotes seems to be right, the expression evaluates to

UPDATE [Sheet1$] SET [IGNORE] = 'T' WHERE [AGREEMENT ID] = '709392' (Text)

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Wed, 19 Jul 2023 10:47:00 GMT

Hi Paul,

Any updates on this please ?

-GG

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

plnarayana777 — Wed, 19 Jul 2023 11:15:00 GMT

I gave try from my end with same query , it is working with only modification done is removed single quote for number

UPDATE [Sheet1$] SET [IGNORE] = 'T' WHERE [AGREEMENT ID] = 709392

Created dummy data with same column names

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Wed, 19 Jul 2023 11:28:00 GMT

Hi , But still this didnt work in my bot. Thanks Lakshmi Narayana for looking into this. Can you share with me the connection string and code flow so that I can check with mine? Kindly take a look at mine.

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

plnarayana777 — Wed, 19 Jul 2023 12:58:00 GMT

My connection string

I don't think their is issue with connection string , as it is throwing error in execution

If possible can you share the sample file with some dummy data (replacing any sensitive data )

Simple and direct steps

for sample data i have used you can refer above

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Wed, 19 Jul 2023 13:24:00 GMT

Here you go...

Tx,

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

plnarayana777 — Wed, 19 Jul 2023 14:07:00 GMT

I have tested with your file and it successfully updated the value

but this time i used single quotes for the number as it threw error stating datatype mismatch

UPDATE [Sheet1$] SET [IGNORE] = 'T' WHERE [AGREEMENT ID] = '697970'

tested with two values both are updated

Can you try removing the IMEX=1 in connection string

if possible try this connection string "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[Input File Name With Path]&";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Wed, 19 Jul 2023 14:22:00 GMT

Nope, I have changed the connection string, but still the same error.

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

GG — Wed, 19 Jul 2023 14:27:00 GMT

Is it do with Blueprism version ? We use v6.10.1

------------------------------
Gracy
------------------------------

RE: C0000005 error caused by a buffer overrun while updating DB using OLEDB

PvD_SE — Fri, 21 Jul 2023 06:37:00 GMT

Hi Gracy,

Have you looked here:
https://www.connectionstrings.com/excel/
...for the correct connection string?

------------------------------
Happy coding!
---------------
Paul
Sweden

(By all means, do not mark this as the 'Best answer'! )
------------------------------