• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

E-FAIL error reading XML data

Access application fronting a SQL Server database.  We need to pull in information from an application written in Infopath that stores data in XML/Sharepoint, and then store that info in a SQL Server table.  We developed a view to read the XML data and it seems to work.....kinda.  We are consistantly getting E-FAIL errors at random times; they might occur on the 2nd record read, on the 50th record read, etc.

Here's the code.  I've cut non-essential stuff out and added some comments specifically for this question IN ALL CAPS.

Dim MCISConn As New ADODB.Connection
Dim rsPatient As New ADODB.Recordset
Dim rsSource as New ADODB.Recordset

' open the connection
'  THIS IS THE CONNECTION TO THE XML DATA.  IT POINTS TO THE SHAREPOINT SERVER
With MCISConn
    .CursorLocation = adUseServer
    .ConnectionString = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=sqlsvr03;Integrated Security=SSPI;Initial Catalog=PatientDirectory;Data Provider=SQLOLEDB.1"
    .Open
End With

' THIS IS THE SPECIFIC VIEW THAT WAS CREATED TO PULL THE XML DATA
' THE WHERE CLAUSE DOES NOT APPEAR TO WORK - IT LOOKS LIKE WE ARE GETTING ALL
' OF THE DATA
SQL = "SELECT * FROM Patient_List_IncidentReporting P "
SQL = SQL & "WHERE P.admissiondate > '" & sDate & "' "
rsSource.Open SQL, MCISConn ,adOpenForwardOnly
Do While Not rsSource.EOF
' THIS CHECK IS HERE BECAUSE OF THE ISSUE WITH THE WHERE CLAUSE NOT WORKING
    If rsSource!admissiondate <= CutoffDate Then
        GoTo NextSource
    End If

' check to see if we already have this guy in the patient table
    Set rsPatient = Nothing
    SQL = "select * from TBL_PATIENT_DATA where patient_id='" & rsSource!patientid & "' "
    SQL = SQL & "and episode_id=" & rsSource!episode
' GCONN IS DEFINED GLOBALLY, AND IS CURRENTPROJECT.CONNECTION
    rsPatient.Open SQL, gConn, adOpenKeyset, adLockOptimistic
    If rsPatient.EOF Then
' no, so add him in
        With rsPatient
            Debug.Print "adding " & rsSource!patientid & " episode " & rsSource!episode
            DoEvents
            .AddNew
            !patient_id = rsSource!patientid
            !episode_id = rsSource!episode
            !lname = rsSource!lastname
            !fname = rsSource!firstname
            !mname = rsSource!middlename
            !dob = rsSource!birthdate
            !sex = rsSource!sex
            !Status = rsSource!admissionstatus
            !date_enrolled = rsSource!admissiondate
            !date_discharged = rsSource!dischargedate
            !addr_1 = rsSource!address1
            !addr_2 = rsSource!address2
            !city = rsSource!city
            !state = rsSource!state
            !zip = rsSource!postalcode
            !home_phone = rsSource!homephone
            .Update
        End With
        sName = Trim$(rsSource!lastname & ", " & rsSource!firstname)
' remove any single quotes in the name
FindQuote:
        i = InStr(sName, "'")
        If i > 0 Then
            sName = Mid$(sName, 1, i - 1) & Mid$(sName, i + 1)
            GoTo FindQuote
        End If
' and load the name into the lookup table
        SQL = "INSERT INTO TBL_PATIENT_NAME_DROPDOWN ( patient_id, patient_name) "
        SQL = SQL & "values ('" & rsSource!patientid & "','" & sName & "')"
        gConn.Execute SQL
    End If
NextSource:

' THE ERROR OCCURS HERE ON THE MOVENEXT.  AS MENTIONED, IT MIGHT HAPPEN AFTER
' A FEW RECORDS,
' OR AFTER 100 OR SO RECORDS
' Data provider or other service returned an E_FAIL status.
    rsSource.MoveNext
Loop
rsSource.Close
Set rsPatient = Nothing
MCISConn.Close


Thanks in advance!
0
Clothahump
Asked:
Clothahump
  • 4
  • 2
1 Solution
 
Robbie_LeggettCommented:
From a previous question, this was the accepted solution:

i recieved this same error when having more than one recordset open at a time.  i fixed it by setting the activeconnection property to nothing"

...
rst.CursorLocation = adUseClient
rst.Open
set rst.ActiveConnection = Nothing
0
 
Robbie_LeggettCommented:
And another solution:

actually this error occurs when we use outer join & the table against which we perform the outer join contains any field with 'NOT NULL' constraint selected in query. actually in outer join database gives null values for fields not in join . but being a not null field database gives niether a null value nor a acceptable value . vb does not support any such values .

solution :
better to use nvl(,) function for those field which are not null & pass '' value to vb. so vb conclude it as null value.
0
 
ClothahumpAuthor Commented:
Robbie:
Already disconnecting the recordset, no joy.  And there is no join in the query, it's just reading one table from Sharepoint/XML.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Robbie_LeggettCommented:
I have a couple more questions:

Is this SharePoint 2003?
Why are you connecting to SharePoint with an Access driver?

:-)
0
 
ClothahumpAuthor Commented:
We found the solution over the weekend.  We set up an ODBC DSN and referenced it instead of trying to do a direct connection in code.  

Robbie, even though I didn't use any of your suggestions, I'm going to give you the points for your efforts in trying to help me.  Thank you very much.
0
 
Robbie_LeggettCommented:
Well done. I'm glad to hear you got the issue resolved.

Thanks very much.

:-)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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