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

Posted on 2011-04-28
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
strQ = " select  testorder.labnumber"
 strQ = strQ & "  From testorder WITH (NOLOCK) INNER JOIN opbill WITH (NOLOCK) ON = 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
Question by:AnnaJames77
    LVL 11

    Expert Comment

    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:
    LVL 22

    Expert Comment

    by:Om Prakash
    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.

    Author Comment

    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)
    LVL 4

    Accepted Solution

    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.

    Author Comment

    pls let me know how to check it
    LVL 27

    Expert Comment

    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")  
    LVL 27

    Expert Comment

    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

    LVL 4

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now