vb6 error Run-time error '-2147217887 Multiple-step OLE DB operation generated errors. Check each OLE DB status value

strQ = "SELECT date_from, date_to FROM Rep_Week WHERE rep_week_no=" & cboRepWeek.ItemData(cboRepWeek.ListIndex)
    If rs.State = 1 Then rs.Close
    rs.Open strQ, cnn, adOpenKeyset
    If rs.EOF = False Then
        dtFrom = rs!date_from
        dtTo = rs!date_to + 1  
    End If
strQ = " select  testorder.labnumber"
 strQ = strQ & "  From testorder WITH (NOLOCK) INNER JOIN opbill WITH (NOLOCK) ON opbill.id = testorder.opbillid"
  strQ = strQ & "  Where opbill.DateTime Between '" & Format(dtFrom, "MM/DD/YYYY") & "' AND '" & Format(dtTo, "MM/DD/YYYY") & "' AND opbill.cancelled = 0"
  strQ = strQ & "    AND opbill.refdoctorid NOT IN (SELECT doctor_id FROM Ref_Doctor)"
  strQ = strQ & "    AND testorder.labnumber NOT IN (Select labnumber from Lab_Header)"
 strQ = strQ & "     Group By testorder.labnumber"
             If rs1.State = 1 Then rs1.Close
        rs1.Open strQ, cnn, adOpenKeyset, adLockReadOnly

I get an error at  this point- "rs1.Open strQ, cnn, adOpenKeyset, adLockReadOnly"
Run-time error '-2147217887  Multiple-step OLE DB operation generated errors. Check each OLE DB status value
ANd the error occurs only for some from date and to date. pls help
Who is Participating?
coolcurrent4uConnect With a Mentor Commented:
it has something to do with the record-set,
it looks like there is no record in the record-set or it is close, and you are trying to access it

check these and try  again.
The errors means an error occurred while executing your SQL statement. To get the actual error message that's being returned by the database, implement an error handler that enumerates the error messages that were returned by the database engine. Use this article as a guide:

Om PrakashCommented:
Check that you are passing all the values in the SQL properly.  For example, proper week number is passed from
and other date values.

Print the individual SQL and see that you are getting the required output.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

AnnaJames77Author Commented:
thankyou om_prakash_p ,bmatumbura for your replies. i tried using err handler as well as removing it. it gives the same error.
i also checked the query in the immediate window and run it in sql it generates value in sql. it gets the correct value from  cboRepWeek.ItemData(cboRepWeek.ListIndex)
AnnaJames77Author Commented:
pls let me know how to check it
Seems this is Date Format issue

Public Function VBDateToSQLDate(ByVal dt As Date) As String
   VBDateToSQLDate = "#" & Replace(format(dt, "mm.dd.yyyy"), ".", "/") & "#"
End Function

replace your
Format(dtFrom, "MM/DD/YYYY")  
Format(dtTo, "MM/DD/YYYY")  
BETWEEN expect Date literals # while you'r using String '
Try also replace BETWEEN with >= AND <= operators:
strQ = strQ & "  WHERE opbill.DateTime >=" & VBDateToSQLDate(dtFrom) & " AND opbill.DateTime <="  & VBDateToSQLDate(dtTo) & " AND opbill.cancelled = 0"

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.