Data Provider or other service returned an E_FAIL status

[zynder]

Hi guys,

I'm getting this weird error when populating a listview.
Data Provider or other service returned an E_FAIL status

But before that I'm getting a runtime error "Invalid Use of Null". I fixed it by concatenating vbNullString then now I'm getting that weird error.

Information Below:
Frontent: Visual Basic 6 SP6
Backend: SQL SERVER 2005
Connection Settings on Module:

Public Sub Open_Connection()
  Set con = New ADODB.Connection
  With con
    .ConnectionString = "Provider=SQLNCLI;Server=xx.xxx.xxx.xx\instance;Database=dbase;Uid=username; Pwd=password;"
    .CommandTimeout = 0
    .CursorLocation = adUseClient
    .Open
  End With
End Sub

Code where the error occurs:

rs.Open "sp_storedprocedure '" & Format(DTPicker1.Value, "YYYY-MM-DD") & " 00:00:00.000" & "', '" & Format(DTPicker2.Value, "YYYY-MM-DD") & " 23:59:59.000" & "', '" & CustID & "'", con, 3, 1

Do Until rs.EOF
      With ListView
        .ListItems.Add , , ""
        .ListItems(lngCounter).ListSubItems.Add.Text = rs.Fields("Name").Value & vbNullString
        .ListItems(lngCounter).ListSubItems.Add.Text = rs.Fields("CharacterCount").Value & vbNullString
        TNL = TNL + CDbl(rs.Fields("CharacterCount").Value)
        .ListItems(lngCounter).ListSubItems.Add.Text = rs.Fields("AuthorName").Value & vbNullString
        .ListItems(lngCounter).ListSubItems.Add.Text = rs.Fields("ReportType").Value & vbNullString
        TNP = TNP + CLng(rs.Fields("PageCount").Value)
        TD = TD + CDbl(rs.Fields("Duration").Value)
      End With
    
      lngCounter = lngCounter + 1
      rs.MoveNext
Loop

The offending line is rs.MoveNext

I hope you can help me guys I'm stumped with this kind of error.

-zynder

[developerno1]

is there a datetime field in the table?? usually its the reason for the error, change it to text and it'll be fixed...

[zynder]

Yes there is a date time. I can't change it to text. Is there any other workaround?

[Darren M.]

Is there a field with timestamp? If so, can you change it to datetime?

Google came up with this thread and that was a suggestion.
http://www.codingforums.com/showthread.php?t=21166

Good luck!

[exaros.m]

I have a similar issue. I do not have any fields with a timestamp however. The date fields were all converted to text:

If recordCount > 0 Then
'select all unprocessed orders and processed orders(last date processed)
Select Case defaultPlant
Case "EA"
strSql = "SELECT tbl_allOpenOrders.HOLDCODE, tbl_allOpenOrders.REQUESTEDDATE, tbl_allOpenOrders.DOCO, '' as processed FROM tbl_branchPlant ,productListByPlant , tbl_allOpenOrders WHERE tbl_branchPlant.branchPlant = productListByPlant.plant AND productListByPlant.modelNumber = tbl_allOpenOrders.LITM GROUP BY tbl_allOpenOrders.HOLDCODE, tbl_allOpenOrders.REQUESTEDDATE, tbl_allOpenOrders.DOCO"
'
' There are other case statements here but removed for simplicity
'
Case Else
MsgBox ("Please select a valid branch plant")
End Select

rstValue.Open strSql, cnn, adOpenKeyset, adLockOptimistic

' Add new orders to lst_OpenOrders
lst_allOpenOrders.AddItem ("Order#" & ";" & "Requested Date" & ";" & "Hold Code" & ";" & "Last Processed")
While Not rstValue.EOF
lst_allOpenOrders.AddItem (stValue.Fields("DOCO") & ";" & rstValue.Fields("REQUESTEDDATE") & ";" & rstValue.Fields("holdCode") & ";" & rstValue.Fields("processed"))
rstValue.MoveNext
Wend


I receive the e_fail status error just at the lst.AddItem just after the While statement. This used to work so im not sure the cause of the issue. I tried compacting and repairing and this also did not work. The results of the sql statement above based on the data contained in the tables should return at least 1 record.

Any help is greatly appreciated.

-Mike