[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2762
  • Last Modified:

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
    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
0
AnnaJames77
Asked:
AnnaJames77
  • 2
  • 2
  • 2
  • +2
1 Solution
 
bmatumburaCommented:
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
0
 
Om PrakashCommented:
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.
0
 
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)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
coolcurrent4uCommented:
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.
0
 
AnnaJames77Author Commented:
pls let me know how to check it
0
 
ArkCommented:
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)
0
 
ArkCommented:
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

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now