Getting "A multi-step operation generated errors. Check each status value." error using ADO with SQL Server 2008

Question

We are trying to port our SQL 2000 to SQL 2008. But we ran into a problem; when the result set (rows or not) is returned using a query with UNION. Later in the code we try to add a new line and assign a field to it, but since UNION was used, when we try to assign a value to the field, it gives us the Multiple-step operation generated errors. Check each status value. Multiple-step operation generated errors. Check each status value. . We tried the following code on Windows XP and Windows 7 and got the same result. But when we change the connection string to return to our SQL 2000 mailbox, we no longer get this error.

The following example shows 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(); } 

Answer 1

Since I posted this problem, we found out that the problem was that you are merging, the attributes in the fields are not related (i.e. the attributes: basecatalog, basetable and basecolumn are empty) to fix our problem, which we forced the values ​​of these attributes, saving the recordset in xml (adPersistXML), change xml and open the recordset from xml again. This is a field rebound, and we were able to continue. We know that this may not be the most efficient solution, but it was for an older application, and we did not want to rewrite the sql statements. It seems that the main error is Multiple-step operation generated errors. Check each status value. Multiple-step operation generated errors. Check each status value. It is related to when an error occurs, when a value is assigned to a field.

Answer 2

Below is a link to an article that lists a large number of 6 scenarios that may occur in this post:

Scenario 1 - An error occurred while trying to insert data into the database

Scenario 2 - Error trying to open an ADO connection

Scenario 3 - Error entering data in Access, where the field has a space

Scenario 4 - Error entering data in Access when using adLockBatchOptimistic

Scenario 5 - Error entering data in Access when using the Jet.OLEDB.3.51 driver or ODBC (not Jet.OLEDB.4.0)

Scenario 6 - Error Using Command Object and Parameters

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

Hope this can help others who may face the same problem.

Answer 3

This is a type mismatch, try

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

or make sure you assign an option to a value.

Answer 4

Two things I can think of ... Make sure the ID column is zero (0). Also, I stopped this problem once without using the adUseClient cursor (try the server).

Many times this is a type mismatch, an attempt to populate NULL in a non-zero column, or try to write more characters to the column than it should have done.

Hope this helps. - freddo

Answer 5

I had the same problem, the problem was that I violated the property of the object, in my case it was the size of the error that arose as

"IntegrationException: problem (with multiple-step 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 the length was more than 50 characters, therefore this property was violated, therefore an error occurred.

Answer 6

I got this error when trying to insert / update a field with a value that does not match the table field type.

For example, the database table field> was

char (1)

however I tried to insert / update

"an Apple"

to the record.

As soon as I changed the entered value to "a" and it worked.