Command - "Multiple-step OLE DB Operation generated errors"

[The_Grudge]

I have the following code within a Loop.

It works perfectly if I write one record at a time, but within the loop (loading multiple records) I get the error:

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

Why does this work once, but not on the second time through?

strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                         "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                         "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name) " & _
                         "VALUES ('" & mUniqueID & _
                         "','" & mInstitution & _
                         "','" & mOccurs & _
                         "'," & "?" & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mTime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & "?" & _
                         "," & "?" & _
                         ",'" & mUserName & "')"
                         
                                                            
                        com.Parameters.Append com.CreateParameter("ClinicDate", adDate, adParamInput, , mClinic_Date)
                        com.Parameters.Append com.CreateParameter("StartDate", adDate, adParamInput, , mStart_Date)
                        com.Parameters.Append com.CreateParameter("EndDate", adDate, adParamInput, , mEnd_Date)
                         
          
       com.CommandText = strWriteData
       com.Execute 'Execute command

[LaVolpe]

If the code you posted above is in a loop, creating the parameters multiple times is likely the problem. Create the parameters (without default values) just once outside of the loop. And inside the loop, set the parameter values.

[The_Grudge]

Makes sense, but I moved that outside the loop and still get the error.

[LaVolpe]

Can we see your test loop? Whenever I see that error, it is almost always a typo in a field name, a missing value, requiring a relational record in another table, or some other easy mistake, but hard to spot. However, you said it worked when outside the loop; therefore, thinking it is something related to being inside the loop.

[The_Grudge]

Sure - see below. Now I wrote this 12 years ago and haven't hardly looked at it since. I'm going through converting it to work with SQL Server as we're moving off Oracle.

I may have to rewrite the part below that uses two different insert statements based on the mSpecialBookedDate being present or not. Why I did that, I don't know. Also, I know I'm not truly using parameters here - that's part of what I'm working on.

Do While dtWriteDate <= mEnd_Date
 
   'Check for holidays each time
    CheckHolidays mClinic_Date
       If mHoliday = False Then
          If Len(mSpecialBookedDate) > 0 Then ' If SpecialBookedDate is not blank then...
             strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                         "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                         "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name,Special_Booked_Date) " & _
                         "VALUES ('" & mUniqueID & _
                         "','" & mInstitution & _
                         "','" & mOccurs & _
                         "'," & "?" & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mTime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & "?" & _
                         "," & "?" & _
                         ",'" & mUserName & _
                         "'," & "?"
                                                                      
                         com.Parameters.Append com.CreateParameter("ClinicDate", adDate, adParamInput, , mClinic_Date)
                         com.Parameters.Append com.CreateParameter("StartDate", adDate, adParamInput, , mStart_Date)
                         com.Parameters.Append com.CreateParameter("EndDate", adDate, adParamInput, , mEnd_Date)
                         com.Parameters.Append com.CreateParameter("SpecialBookedDate", adDate, adParamInput, , mSpecialBookedDate)
                       
          Else
             strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                         "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                         "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name) " & _
                         "VALUES ('" & mUniqueID & _
                         "','" & mInstitution & _
                         "','" & mOccurs & _
                         "'," & "?" & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mTime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & "?" & _
                         "," & "?" & _
                         ",'" & mUserName & "')"   
            End If
          com.Parameters.Append com.CreateParameter("ClinicDate", adDate, adParamInput, , mClinic_Date)
                        com.Parameters.Append com.CreateParameter("StartDate", adDate, adParamInput, , mStart_Date)
                        com.Parameters.Append com.CreateParameter("EndDate", adDate, adParamInput, , mEnd_Date)
       com.CommandText = strWriteData
       com.Execute 'Execute command
   
         
    End If

[LaVolpe]

A quick scan seems that both queries are identical except one having an additional field/value for SpecialBookedDate. However, your 1st query is missing the closing parenthesis. If that's the problem, like I said, something easy but hard to spot

Note: If that additional field is a required entry in the database table, then the "Else" portion of your IF statement should fail. Is that the case? The first portion should fail due to the missing parenthesis.

[The_Grudge]

The first part would fail yes, but I’m just testing one specific record at the moment and the first part of the if is never fired. It just looks at the Else....that’s where the error stems from — somewhere in there

[LaVolpe]

Missing the assignment of the Special_Booked_Date field and its value?

It's hard to help here because I don't think we are seeing everything. You said it worked when no loop introduced, but didn't after that. Then you said that moving the parameter creation outside of the loop generated the same error. Nowhere in the sample code posted did we see creating the command object and seeing its non-parameter properties being set. Nor are we seeing dtWriteDate being modified which must happen else your loop would be infinite.

Not trying to be picky, but not seeing your exact code doesn't help us help you. Again, I typically see that error in my own queries when, among other blunders, I forget to include a required field in the query and maybe not including the Special_Booked_Date field is the problem?

[DllHell]

I see this error when the data exceeds the defined size, for example, trying to write 4 characters to a field that is only defined as 3.

compare Special_Booked_Date and End_Date in SSMS and make sure they are the same data types, then compare the data types in vb and make sure they are same too.

[The_Grudge]

Thanks guys, I get it. I may post my entire code tomorrow if I can't figure it out.

The IF statement above has two SQL statements and the first one (with the "Special_Booked_Date") is never fired. Well, at least not right now while I'm testing my code. So yah, it's there in the code but the error isn't coming from in there as it only executes the Else in that statement when I step through.

It's something with the command/parameter but it fires properly the first time through the loop so I don't think it's changed length or anything when it writes the second record. It's more likely in the creation of the command etc.

Thanks again and chat tomorrow maybe!

[LaVolpe]

If you can't figure it out, definitely post back with more complete sample code.

P.S. I'd consider just using one SQL statement, including the Special_Booked_Date field. If Len(mSpecialBookedDate) is zero, simply provide NULL or whatever default value you want to use. That could simplify things a bit. Also, it appears the loop will be using the same values for vast majority of the fields in the query for each record added within the loop. If you are going to use parameters for all fields/values, recommend setting the ones that won't change, inside the loop, outside of the loop. The only parameters inside the loop should be the ones that can change in each iteration. The command object and parameters need to be created just once, outside the loop. The parameter values can be set within the loop, as needed.

[vbcommander]

I seem to remember seeing this years ago when the field in the db was too small for the data being inserted. But I may be remembering incorrectly. There have been so many issues...

[The_Grudge]

I return...

I've cleaned up the code a bit and again, it works on the first iteration through the loop. On the second time through that error crops up again.

Public Function WritetoSQLServer(ByVal SQLCon As ADODB.Connection, ByRef FormInUse As Form)
 Dim strWriteData As String
 Dim com As ADODB.Command
 Dim intWeekNum As Integer
 Dim strYear As String
 Dim strMonth As String
 Dim strDay As String
 Dim dtStartDate As Date
 Dim dtWriteDate As Date
 Dim intIndex As String
 Dim strYear2 As String
 
 Set com = New ADODB.Command
 
 'Get the clinic start date and week number
 dtStartDate = FormInUse.MonthViewClinic.Value
 intWeekNum = FormInUse.MonthViewClinic.Week
  
 With com
    .ActiveConnection = SQLCon
    .ActiveConnection.BeginTrans
    .CommandType = adCmdText
    .Prepared = True
 
    dtWriteDate = dtStartDate
          
   'Write the clinic to the database until the clinic date is greater than the identified end date.
    Do While dtWriteDate <= mEnd_Date
      'Check for holidays each time
       CheckHolidays mClinic_Date
          If mHoliday = False Then
             strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                            "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                            "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name,Special_Booked_Date) " & _
                            "VALUES ('" & mUniqueID & _
                            "','" & mInstitution & _
                            "','" & mOccurs & _
                            "'," & "?" & _
                            ",'" & mLocation & _
                            "','" & mSID_DOC_NUM & _
                            "','" & mClinicNum & _
                            "','" & mClinic_Code & _
                            "','" & mDoc_Index & _
                            "','" & mRooms & _
                            "','" & mTime & _
                            "','" & mCancelled & _
                            "','" & mCancelledReason & _
                            "','" & mSpecial & _
                            "','" & mHold & _
                            "','" & mReassignment & _
                            "','" & mDayOfWeek & _
                            "','" & mReinstatement & _
                            "'," & "?" & _
                            "," & "?" & _
                            ",'" & mUserName & _
                            "'," & "?)"
                                                                      
                         .Parameters.Append com.CreateParameter("ClinicDate", adDate, adParamInput, , mClinic_Date)
                         .Parameters.Append com.CreateParameter("StartDate", adDate, adParamInput, , mStart_Date)
                         .Parameters.Append com.CreateParameter("EndDate", adDate, adParamInput, , mEnd_Date)
                         
                         If Len(mSpecialBookedDate) = 0 Then
                            com.Parameters.Append com.CreateParameter("SpecialBookedDate", adDate, adParamInput, , Null)
                         Else
                            com.Parameters.Append com.CreateParameter("SpecialBookedDate", adDate, adParamInput, , mSpecialBookedDate)
                         End If
        
       .CommandText = strWriteData
       .Execute 'Execute command
    End If
                              
   'Increment the clinic date according to the "frequency" of the clinic
    Select Case mOccurs
            Case "Every Week"
                  intWeekNum = intWeekNum + 1
                  mClinic_Date = DateAdd("d", 7, mClinic_Date)
            Case "Every Other Week"
                  intWeekNum = intWeekNum + 2
                  mClinic_Date = DateAdd("d", 14, mClinic_Date)
            Case "Every Third Week"
                  intWeekNum = intWeekNum + 3
                  mClinic_Date = DateAdd("d", 21, mClinic_Date)
            Case "One Time"
                  mClinic_Date = DateAdd("d", 1, mEnd_Date)
            Case "1,2,4, & 5"
                  
     End Select
          
     Select Case FormInUse.Name
        Case "frmMain"
         If Len(mUniqueID) > 8 Then
           intIndex = Mid$(mUniqueID, 7, 3)
         Else
           intIndex = Mid$(mUniqueID, 7, 2)
        End If
        Case "frmHDHEntry"
           intIndex = Mid$(mUniqueID, 7, 5)
        Case "frmCancer"
           intIndex = Mid$(mUniqueID, 7, 3)
      End Select
    
     strYear = Format(Right(mClinic_Date, 2), "00")
     strYear2 = Format(Right(mClinic_Date, 4), "0000")
     strMonth = Mid$(mClinic_Date, 1, 2)
     strDay = Mid$(mClinic_Date, 4, 2)
          
     dtWriteDate = DateSerial(strYear2, strMonth, strDay)
     mUniqueID = strYear & strMonth & strDay & intIndex
    
 Loop
 
 End With
    
 com.ActiveConnection.CommitTrans
 Set com = Nothing

End Function

[LaVolpe]

First, I think you still should be moving your creation parameters outside of the loop or create your command object in each loop iteration; one or the other.

I do have a question... This line in your original code

.Parameters("SpecialBookedDate") = mSpecialBookedDate

Is mSpecialBookedDate a date variable? If not, is that line converting the parameter to a valid date value? If that is not true, likely a problem.

When the error occurs, go into debug mode and look at the parameter values you set.

Here's your code with the parameter creation moved outside the loop along with most other command object stuff. It appears that only 2 values change with the loop: unique ID and clinic date. You'll also notice that I made the uniqueID field a parameter (that may have to be tweaked if needed to fit your database field size) and changed the query to use the parameter. If other query values change during your call to CheckHolidays, then the following is incomplete and requires tweaking. Even if you don't use what's below; maybe it can give you and idea of what I was trying to suggest....

Public Function WritetoSQLServer(ByVal SQLCon As ADODB.Connection, ByRef FormInUse As Form)
 Dim strWriteData As String
 Dim com As ADODB.Command
 Dim intWeekNum As Integer
 Dim strYear As String
 Dim strMonth As String
 Dim strDay As String
 Dim dtStartDate As Date
 Dim dtWriteDate As Date
 Dim intIndex As String
 Dim strYear2 As String
 
 Set com = New ADODB.Command
 
 'Get the clinic start date and week number
 dtStartDate = FormInUse.MonthViewClinic.Value
 intWeekNum = FormInUse.MonthViewClinic.Week
  
 With com
    .ActiveConnection = SQLCon
    .ActiveConnection.BeginTrans
    .CommandType = adCmdText
    .Prepared = True
    .Parameters.Append .CreateParameter("uID", adVarChar, adParamInput)
    .Parameters.Append .CreateParameter("ClinicDate", adDate, adParamInput)
    .Parameters.Append .CreateParameter("StartDate", adDate, adParamInput, , mStart_Date)
    .Parameters.Append .CreateParameter("EndDate", adDate, adParamInput, , mEnd_Date)
    If Len(mSpecialBookedDate) = 0 Then
        .Parameters.Append .CreateParameter("SpecialBookedDate", adDate, adParamInput, , Null)
    Else
        .Parameters.Append .CreateParameter("SpecialBookedDate", adDate, adParamInput, , mSpecialBookedDate)
    End If
    
    strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                   "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                   "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name,Special_Booked_Date) " & _
                   "VALUES (?" & _
                   ",'" & mInstitution & _
                   "','" & mOccurs & _
                   "'," & "?" & _
                   ",'" & mLocation & _
                   "','" & mSID_DOC_NUM & _
                   "','" & mClinicNum & _
                   "','" & mClinic_Code & _
                   "','" & mDoc_Index & _
                   "','" & mRooms & _
                   "','" & mTime & _
                   "','" & mCancelled & _
                   "','" & mCancelledReason & _
                   "','" & mSpecial & _
                   "','" & mHold & _
                   "','" & mReassignment & _
                   "','" & mDayOfWeek & _
                   "','" & mReinstatement & _
                   "'," & "?" & _
                   "," & "?" & _
                   ",'" & mUserName & _
                   "'," & "?)"
    .CommandText = strWriteData

    dtWriteDate = dtStartDate
          
   'Write the clinic to the database until the clinic date is greater than the identified end date.
    Do While dtWriteDate <= mEnd_Date
        'Check for holidays each time
        CheckHolidays mClinic_Date
        If mHoliday = False Then
            .Parameters("uID") = mUniqueID
            .Parameters("ClinicDate") = mClinic_Date
            .Execute 'Execute command
        End If
                              
        'Increment the clinic date according to the "frequency" of the clinic
        Select Case mOccurs
            Case "Every Week"
                  intWeekNum = intWeekNum + 1
                  mClinic_Date = DateAdd("d", 7, mClinic_Date)
            Case "Every Other Week"
                  intWeekNum = intWeekNum + 2
                  mClinic_Date = DateAdd("d", 14, mClinic_Date)
            Case "Every Third Week"
                  intWeekNum = intWeekNum + 3
                  mClinic_Date = DateAdd("d", 21, mClinic_Date)
            Case "One Time"
                  mClinic_Date = DateAdd("d", 1, mEnd_Date)
            Case "1,2,4, & 5"
                  
        End Select
          
        Select Case FormInUse.Name
        Case "frmMain"
            If Len(mUniqueID) > 8 Then
                intIndex = Mid$(mUniqueID, 7, 3)
            Else
                intIndex = Mid$(mUniqueID, 7, 2)
            End If
        Case "frmHDHEntry"
            intIndex = Mid$(mUniqueID, 7, 5)
        Case "frmCancer"
            intIndex = Mid$(mUniqueID, 7, 3)
        End Select
    
        strYear = Format(Right(mClinic_Date, 2), "00")
        strYear2 = Format(Right(mClinic_Date, 4), "0000")
        strMonth = Mid$(mClinic_Date, 1, 2)
        strDay = Mid$(mClinic_Date, 4, 2)
          
        dtWriteDate = DateSerial(strYear2, strMonth, strDay)
        mUniqueID = strYear & strMonth & strDay & intIndex
    
    Loop
 
 End With
    
 com.ActiveConnection.CommitTrans
 Set com = Nothing

End Function

[The_Grudge]

That seemed to do the trick. I moved the parameter creation outside the loop. Then, after it writes and figures out the next clinic date I update the parameter with:

  .Parameters("ClinicDate") = mClinic_Date

That seems to work. Thanks for sticking with me!