Getting "Multiple-step operation generated errors. Check each status value." error using ADO with SQL server 2008

Question

We are in the process to migrate our SQL 2000 box to SQL 2008. But we ran into an issue; when a result set (rows or not) is returned by using a query that has a UNION. Later in the code we try to add a new row and assign field to it but because a UNION was used, when we try to assign a value to the field it gives us a Multiple-step operation generated errors. Check each status value. error. We tried the following code on a Windows XP & Windows 7 and got the same result. But when we change our connection string to point back to our SQL 2000 box we don't get that error any more.

The following example show the problem we are having.

var c = new ADODB.Connection();
var cmd = new ADODB.Command();
var rs = new ADODB.Recordset();
object recordsAffected;

c.Open("Provider=SQLOLEDB;Server=*****;Database=*****;User Id=*****;Password=*****;");

cmd.ActiveConnection = c;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.CommandText = "create table testing2008 (id int)";
cmd.Execute(out recordsAffected);

try {
    cmd.CommandText = "select * from testing2008 union select * from testing2008";

    rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
    rs.Open(cmd, Type.Missing, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, -1);

    rs.AddNew();
    rs.Fields["id"].Value = 0; //throws exception
    rs.Save();
}
catch (Exception ex) {
    MessageBox.Show(ex.ToString());
}
finally {
    cmd.CommandText = "drop table testing2008";
    cmd.Execute(out recordsAffected);
    c.Close();
}

score 10 · Accepted Answer · answered May 01 '14 at 10:12

The link below is an article that gives a great breakdown of the 6 scenarios this error message can occur:

Scenario 1 - Error occurs when trying to insert data into a database

Scenario 2 - Error occurs when trying to open an ADO connection

Scenario 3 - Error occurs inserting data into Access, where a fieldname has a space

Scenario 4 - Error occurs inserting data into Access, when using adLockBatchOptimistic

Scenario 5 - Error occurs inserting data into Access, when using Jet.OLEDB.3.51 or ODBC driver (not Jet.OLEDB.4.0)

Scenario 6 - Error occurs when using a Command object and Parameters

http://www.adopenstatic.com/faq/80040e21.asp

Hope it may help others that may be facing the same issue.

Solve my problem! Thank a lot @EdsonF !! – WishKnew Nov 01 '16 at 05:00 — WishKnew, Nov 01 '16 at 05:00

score 7 · Answer 2 · answered Apr 04 '12 at 11:54

7

It is type mismatch, try

rs.Fields["id"].Value = "0";

or make sure you assign a Variant to the value.

answered Apr 04 '12 at 11:54

Olix

81
1
7

This tip helped me fix my problem. Lead me to notice a problem with Selecting a UNIQUEIDENTIFIER vs a VARCHAR when inserting into a ADODB RecordSet object – Kyle Pierson Sep 29 '22 at 19:49

score 7 · Answer 3 · answered May 07 '12 at 11:26

Since I posted this problem, we figured out that the problem was when you do a union the attributes on the fields are not bound (i.e. the attributes: basecatalog, basetable & basecolumn are empty) to remedy our problem we had to force the values of those attributes, by saving the recordset to xml (adPersistXML), change the xml and reopen the recordset from the xml. This rebound the fields and we were able to continue. We know this may not be the most efficient solution, but it was for an older app and we didn't want to rewrite the sql statements. It looks like the main error Multiple-step operation generated errors. Check each status value. is related to when an error occurs when a value is assigned to a field.

score 4 · Answer 4 · answered Dec 01 '11 at 18:10

4

Two things I can think of... Make sure your "ID" column will accept a zero (0). Also - I've stopped this issue on one occasion by not using the adUseClient cursor (try server).

Many times this is a type mismatch, trying to stuff a NULL into a non-null column, or attempting to write more characters into a column than it's designed to take.

Hope this helps. - Freddo

answered Dec 01 '11 at 18:10

Freddo

95
4

2

Freddo, I know this is an old thread, but I wanted to thank you for your response. I received this error and had no idea why. It turned out to be due to trying to write more characters than I had originally set the field for. Thanks, again. – Sep 26 '13 at 21:16

score 2 · Answer 5 · answered Oct 26 '16 at 10:29

Same issue occurred to me the problem was that i violated an object property , in my case it was size the error came out as

"IntegrationException: Problem (Multiple-step operation generated errors. Check each status value.)"

  Imports ADODB
  Dim _RecordSet As Recordset  
  _rs.Fields.Append("Field_Name", DataTypeEnum.adVarChar, 50)

  _Recordset("Field_Name").Value = _RecordDetails.Field_NameValue

_RecordDetails.Field_NameValue length was more than 50 chars , so this property was violated , hence the error occurred .

score 2 · Answer 6 · answered Jul 30 '18 at 12:52

2

I found another scenario:

When I was trying to set the value of a adLongVarChar field in a new record for a memory-only adodb.recordset. In my case, the error was triggered because the string I was passing had a buried unicode character.

answered Jul 30 '18 at 12:52

J Cloud

21
2

1

My case was the same. Cyrillic letter in one of the string values. – nyagolova Aug 22 '18 at 09:49

score 2 · Answer 7 · answered Nov 14 '19 at 23:25

2

I found this error when our legacy application was trying to parse 1/1/0001 12AM date and time. Looks like VB6 recordsets doesn't like that value. To get rid of the errors, I had to set all the offending dates to null.

answered Nov 14 '19 at 23:25

Dithos

41
4

score 0 · Answer 8 · answered Feb 08 '18 at 19:10

I was getting this error when trying to insert/update the field with a value that did not match the table>field type.

For example, the database table > field was

char(1)

however, I was trying to insert/update

"apple"

into the record.

Once I change the inputted value to "a" and it worked.

score 0 · Answer 9 · answered Mar 22 '23 at 02:28

0

It happens in case you try to insert a text to an Access table column with ShortText column type, if the column length is shorter than the text length.

answered Mar 22 '23 at 02:28

Al007

383
1
5
15

Getting "Multiple-step operation generated errors. Check each status value." error using ADO with SQL server 2008

9 Answers9

Linked