MySQL Bugs: #11925: Error:Multiple-step OLE DB operation generated errors. Check each OLE DB status

Bug #11925	Error:Multiple-step OLE DB operation generated errors. Check each OLE DB status
Submitted:	13 Jul 2005 21:04	Modified:	30 May 2013 5:24
Reporter:	Aiby Mohan Das	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	MySQL 3.1.12/ MySQL ODBC/3.51 MDAC 2.8	OS:	Windows (XP Professional)
Assigned to:		CPU Architecture:	Any

Description:
This happence when we update  to the field which is created as TEXT or VarChar type..!! 
While skipping those fields in Debug Mode, Data Storing to the Database 

OS: XP 
Connectivity : ODBC 
Application : VB6/ADO Object Method 

Error message is

Run-time error '-2147217887 (80040e21):'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done 

I found this experianced to many people in different way! Some people not geting connection! few people face this while data fetcing etc.. 

And most of them believes MDAC 2.8 is the solution! and works fine ! but its not true..

In the registry, under the key for an OLE DB provider's CLSID, there may be an entry named OLEDB_SERVICES. If the OLE DB provider that is used to make the ADO connection does not have the OLEDB_SERVICES entry, and ADO tries to set up a property that is not supported by the provider, the error occurs. 

 
 
 If OLEDB_SERVICES entry exists but there is a problem in the ADO connection string, the error occurs.
 

If the OLEDB_SERVICES registry entry is not present, the call to set up a property is made directly to the provider. If the provider does not support the property, the SetProperties OLE DB call made by ADO will fail. This is a fatal failure if the property is required to make a connection. If the property was explicitly included in the connection string, it is probably required. 

If the property setting is optional, the failure is not fatal. This is the case when you connect without explicitly setting the property in the connection string. In this case, ADO sets the property to True but marks it as optional

How to repeat:
Simply avoid Recordset Object!!   
The error caused when I tried to insert or assing values to a TEXT/VarChar type data fields. 

'----------------------------------------------------------'
        '  W r i t e  y o u   D a t a b a s e
        '----------------------------------------------------------'
        Dim mCn As New ADODB.Connection
        Dim Rec As New ADODB.Recordset
        Dim RecCrl As New ADODB.Recordset
        Dim mJournalID As Long
        Dim mVoucherNO As Long
        Dim mTagId As Long
        Dim mProjectID As Long
        Dim mModeOfPayment As Integer
        Dim mSQL As String
        
        
        If optCash.Value Then
            mModeOfPayment = 1
        ElseIf optCheque.Value Then
            mModeOfPayment = 2
        ElseIf optDraft.Value Then
            mModeOfPayment = 3
        Else
            mModeOfPayment = 1
        End If
        
        mTagId = FindMasterID("tblTags", "fldTagID", "fldTagName", txtTag)
        mProjectID = FindMasterID("tblProjects", "fldProjectId", "fldProjectName", txtProject)
        
        Set mCn = gbCnn
        mCn.BeginTrans
            'On Error GoTo ErrRollBack
            mSQL = "Select fldJournalID, fldPaymentID From tblAccounts Where fldAccountID = " & gbActiveAccountID
            RecCrl.Open mSQL, mCn, adOpenKeyset, adLockPessimistic, adCmdText
            RecCrl!fldJournalID = RecCrl!fldJournalID + 1
            RecCrl!fldPaymentID = RecCrl!fldPaymentID + 1
            mJournalID = RecCrl!fldJournalID
            mVoucherNO = RecCrl!fldPaymentID
            RecCrl.Update
            
            Rec.Open "tblJournal", mCn, adOpenDynamic, adLockOptimistic, adCmdTable
            Rec.AddNew
            Rec!fldAccountID = gbActiveAccountID
            Rec!fldID = mJournalID
            Rec!fldSerial = 1
            Rec!fldModuleID = 1
            Rec!fldVoucherType = 101
            Rec!fldVoucherNo = mVoucherNO
            Rec!fldDate = txtDate
            Rec!fldLedgerID = mLedgerID
            Rec!fldGroupID = mGroupId
            Rec!fldDr = Format(FormatAmountToValue(txtAmount.Text), "0.00")
            Rec!fldCr = 0
            Rec!fldNarration = txtNarration.Text   '<< ERROR Occurs at this point TEXT/VarChar Type
            Rec!fldByLedgerID = mLedgerID
            Rec!fldTagID = mTagId
            Rec!fldProjectID = mProjectID
            Rec!fldModeOfPayment = mModeOfPayment

            If mModeOfPayment > 1 Then
              Rec!fldNameOfBank = Trim(txtBank)  '<< EORROR 
              Rec!fldPlace = Trim(txtPlace)
              Rec!fldRefNo = Trim(txtRef)
              Rec!fldDated = txtDated
            Else
              Rec!fldNameOfBank = ""                  ' << Same field permits NullString/Null value
              Rec!fldPlace = ""
              Rec!fldRefNo = ""
              Rec!fldDated = Null
            End If
            Rec.Update

Suggested fix:
This will work with INSERT Query with ADO.Connection.Execute Method..'

'        Set mCn = gbCnn
'        mCn.BeginTrans
'            'On Error GoTo ErrRollBack
'            mSQL = "Select fldJournalID, fldPaymentID From tblAccounts Where fldAccountID = " & gbActiveAccountID
'            RecCrl.Open mSQL, mCn, adOpenKeyset, adLockPessimistic, adCmdText
'            RecCrl!fldJournalID = RecCrl!fldJournalID + 1
'            RecCrl!fldPaymentID = RecCrl!fldPaymentID + 1
'            mJournalID = RecCrl!fldJournalID
'            mVoucherNO = RecCrl!fldPaymentID
'            RecCrl.Update
'
'            Rec.Open "tblJournal", mCn, adOpenDynamic, adLockOptimistic, adCmdTable
'            Rec.AddNew
'            '----------------------------------------------------------'
'            '  F i r s t   S e t   o f   D a t a  To -->> DEBIT ACCOUNT
'            '----------------------------------------------------------'
'            mSQL = ""
'            mSQL = mSQL + "INSERT INTO tblJournal ("
'            mSQL = mSQL + "fldAccountID, "
'            mSQL = mSQL + "fldID, "
'            mSQL = mSQL + "fldSerial, "
'            mSQL = mSQL + "fldModuleID, "
'            mSQL = mSQL + "fldVoucherType, "
'            mSQL = mSQL + "fldVoucherNo, "
'            mSQL = mSQL + "fldDate, "
'            mSQL = mSQL + "fldLedgerID, "
'            mSQL = mSQL + "fldGroupID, "
'            mSQL = mSQL + "fldDr, "
'            mSQL = mSQL + "fldCr, "
'            mSQL = mSQL + "fldNarration, "
'            mSQL = mSQL + "fldByLedgerID, "
'            mSQL = mSQL + "fldTagID, "
'            mSQL = mSQL + "fldProjectID, "
'            mSQL = mSQL + "fldModeOfPayment, "
'
'            mSQL = mSQL + "fldNameOfBank, "
'            mSQL = mSQL + "fldPlace, "
'            mSQL = mSQL + "fldRefNo, "
'            mSQL = mSQL + "fldDated ) "
'
'            mSQL = mSQL + " VALUES ( "
'            mSQL = mSQL & gbActiveAccountID                 'AccountID
'            mSQL = mSQL + "," & mJournalID                  'JournalId
'            mSQL = mSQL + "," & 1                           'SlNo
'            mSQL = mSQL + "," & 1                           'Module ID
'            mSQL = mSQL + "," & 101                         'Voucher Type
'            mSQL = mSQL + "," & mVoucherNO                  'Voucher No
'            mSQL = mSQL + ",'" & Format(txtDate, "Yyyy-Mm-Dd") + " 00:00:00'"        'Date
'            mSQL = mSQL + "," & mLedgerID                   'LedgerId
'            mSQL = mSQL + "," & mGroupId                    'GroupID
'            mSQL = mSQL + "," & Format(FormatAmountToValue(txtAmount.Text), "0.00") ' Dr amount
'            mSQL = mSQL + "," & 0                           'Cr Amount
'            mSQL = mSQL + ",'" + txtNarration.Text + "'"    'Narration
'            mSQL = mSQL + "," & mLedgerID                   'ByLedgerID
'            mSQL = mSQL + "," & mTagId                      'TagID
'            mSQL = mSQL + "," & mProjectID                  'ProjectID
'            mSQL = mSQL + "," & mModeOfPayment              'ModeOfPayment
'            If mModeOfPayment > 1 Then
'              mSQL = mSQL + ",'" + txtBank + "'"
'              mSQL = mSQL + ",'" + txtPlace + "'"
'              mSQL = mSQL + ",'" + txtRef + "'"
'              mSQL = mSQL + ",'" + Format(txtDated, "Yyyy-Mm-Dd") + " 00:00:00'"
'            Else
'              mSQL = mSQL + ",''"
'              mSQL = mSQL + ",''"
'              mSQL = mSQL + ",''"
'              mSQL = mSQL + ", Null"
'            End If
'            mSQL = mSQL & ")"
'            mCn.Execute mSQL
'            '----------------------------------------------------------'

Could you please write table definitions ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I've got a similar problem with SQL server/XP pro/MDAC 2.81 trying to write into a varchar field a text value.
I discovered that something wrong was occurred to the MDAC installation on my pc,so I repaired it with the instructions found at

http://www.macropool.com/en/download/mdac_xp_sp2.html

They also offer a free tool to automatically repair the MDAC installation but it didn't work in my case, while the instructions for manual fixing did.

For your info, this was the code that generated the error:

    Const adOpenKeyset = 1
    Const adOpenDynamic = 2
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = 1
    ConnString = "Driver={SQL Server};Server=172.17.10.91;Database=Compass;Uid=sa;Pwd="
    Set rs = CreateObject("adodb.recordset")
    Set ws = ActiveWorkbook.Worksheets("FasiCiclo")
    r = 2
    Do Until ws.Cells(r, 1) = ""
       sqlstm = "select * from dbo.fasiciclo where codciclo='" & ws.Cells(r, 1) & "' "
       sqlstm = sqlstm & "and numfase=" & ws.Cells(r, 5)
       rs.Open sqlstm, ConnString, adOpenKeyset, adLockOptimistic
       If rs.EOF Then
          rs.AddNew
          ws.Cells(r, 1).Font.Color = vbBlue
       Else
          ws.Cells(r, 1).Font.Color = vbMagenta
       End If
       c = 1
       For Each fld In rs.Fields
          If ws.Cells(r, c) <> "" Then
             fld.Value = ws.Cells(r, c)       <- error here
             Debug.Print c, Err
          End If
          c = c + 1
       Next
       On Error Resume Next
       rs.Update
       If Err <> 0 Then
          ws.Cells(r, 1).Font.Color = vbRed
          Set rs = Nothing
          Set rs = CreateObject("adodb.recordset")
          Err = 0
       Else
          rs.Close
       End If
       r = r + 1
    Loop

The instruction 
             fld.Value = ws.Cells(r, c)   
generated the error trying to move a text value into a varchar field.

Ciao

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.