We help IT Professionals succeed at work.

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

Medium Priority
3,091 Views
Last Modified: 2012-08-14
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
    rs.Close
   
   
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
Comment
Watch Question

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:

http://support.microsoft.com/kb/168336
Top Expert 2010

Commented:
Check that you are passing all the values in the SQL properly.  For example, proper week number is passed from
cboRepWeek.ItemData(cboRepWeek.ListIndex)
and other date values.

Print the individual SQL and see that you are getting the required output.

Author

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)
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
pls let me know how to check it
Ark
CERTIFIED EXPERT

Commented:
Seems this is Date Format issue
Try

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")  
and
Format(dtTo, "MM/DD/YYYY")  
with
VbDateToSQLDate(dtFrom)
VbDateToSQLDate(dtTo)
Ark
CERTIFIED EXPERT

Commented:
PS
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

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.