My VB code is not working when looping through the second loop

Question

0.00/5 (No votes)

See more:

I have a simple xml document that I want to create a excel spreadsheet from. My vb code is only working when the xml document does not have report parent with child elements.
How do I change the For Each statement so the code does not fail on second loop where is does not find the following elements FName, MName, LName, LogIn and Organization
For example it is working for this xml
I am using objStreamWriter to output excel sheet.
xml file name is saved in objXmlResultDoc

Desired output in 2 rows with 8 column
FirstName MiddleName LastName LogIn Organization OrganizationORI OrganizationName CaseNumber
Someone X Pati File\JeanX 999 NULL NULL 2312
Null Null Null Null Null 999 Googleee
2312
My code does not work for the following xml document
<?xml version="1.0" encoding="UTF-8"?>

XML

<Results>
	<Report>
		<FName>Someone</FName>
		<MName>X</MName>
		<LName>Pati</LName>
		<LogIn>File\JeanX</LogIn>
		<Organization>999</Organization>
		<CaseNumber>2312</CaseNumber>
	</Report>
	<Report>
		<OrganizationORI>999</OrganizationORI>
		<OrganizationName>Googleee</OrganizationName>
		<CaseNumber>2312</CaseNumber>
	</Report>
</Results>

Vb code

VB

'Create excel 
Dim objXMLNode As XmlNode
Dim objXmlResultDoc As XmlDocument
Dim strFName As String
Dim strMName As String
Dim strOrganizationName As String
Dim strLName As String
Dim strLogIn As String
Dim strOrganization As String
Dim strCaseNumber As String
objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
objOutputMemStream = New System.IO.MemoryStream
objStreamWriter = New System.IO.StreamWriter(objOutputMemStream)
           objStreamWriter.WriteLine("Requestor First Name, Requestor Middle Name, Requestor Last Name, Requestor Login, Organization ID, Organization Name, Case Number")
           objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
            For Each objXMLNode In objXmlResultDoc.DocumentElement.SelectNodes("Report")
               'Get FirstName
                If objXMLNode.SelectSingleNode("Report/"FName") Is Nothing Then
                    strFirstName = objXMLNode.SelectSingleNode(FName").InnerText
                Else
                    strFirstName = "NULL"
                End If
                'Get MiddleName
                If objXMLNode.SelectSingleNode("Report/"MiddleName") Is Nothing Then
                        strMiddleName = objXMLNode.SelectSingleNode("MiddleName").InnerText
                    Else
                        strMiddleName = "NULL"
                End If
                'Get LastName
                If objXMLNode.SelectSingleNode("Report/"LastName") Is Nothing Then
                    strLastName = objXMLNode.SelectSingleNode("LastName").InnerText
                Else
                    strLastName = "NULL"
                End If
                'Get Login
                If objXMLNode.SelectSingleNode("Report/"LogIn") Is Nothing Then
                    strLogIn = objXMLNode.SelectSingleNode("LogIn").InnerText
                Else
                    strLogIn = "NULL"
                End If
                'Get Organization
                If objXMLNode.SelectSingleNode("Report/"Organization") Is Nothing Then
                    strOrganization = objXMLNode.SelectSingleNode("Organization").InnerText

                Else
                    strOrganization = "NULL"
                End If
                'Get CaseNumber
                If objXMLNode.SelectSingleNode("Report/"CaseNumber") Is Nothing Then
                    strCaseNumber = objXMLNode.SelectSingleNode(CaseNumber").InnerText
                Else
                    strCaseNumber = "NULL"
                End If
                
                objStreamWriter.WriteLine(strFName + "," + strMName + "," + strLName + "," + strLogIn + "," + strOrganization + "," + strOrganizationName + "," + strCaseNumber)
            Next

            objStreamWriter.Flush()
            objOutputMemStream.Position = 1

What I have tried:

VB

Dim strLName As String
Dim strLogIn As String
Dim strOrganization As String
Dim strCaseNumber As String
objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
objOutputMemStream = New System.IO.MemoryStream
objStreamWriter = New System.IO.StreamWriter(objOutputMemStream)
           objStreamWriter.WriteLine("Requestor First Name, Requestor Middle Name, Requestor Last Name, Requestor Login, Organization ID, Organization Name, Case Number")
           objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
            For Each objXMLNode In objXmlResultDoc.DocumentElement.SelectNodes("Report")
               'Get FirstName
                If objXMLNode.SelectSingleNode("Report/"FName") Is Nothing Then
                    strFirstName = objXMLNode.SelectSingleNode(FName").InnerText
                Else
                    strFirstName = "NULL"
                End If
                'Get MiddleName
                If objXMLNode.SelectSingleNode("Report/"MiddleName") Is Nothing Then
                        strMiddleName = objXMLNode.SelectSingleNode("MiddleName").InnerText
                    Else
                        strMiddleName = "NULL"
                End If
                'Get LastName
                If objXMLNode.SelectSingleNode("Report/"LastName") Is Nothing Then
                    strLastName = objXMLNode.SelectSingleNode("LastName").InnerText
                Else
                    strLastName = "NULL"
                End If
                'Get Login
                If objXMLNode.SelectSingleNode("Report/"LogIn") Is Nothing Then
                    strLogIn = objXMLNode.SelectSingleNode("LogIn").InnerText
                Else
                    strLogIn = "NULL"
                End If
                'Get Organization
                If objXMLNode.SelectSingleNode("Report/"Organization") Is Nothing Then
                    strOrganization = objXMLNode.SelectSingleNode("Organization").InnerText

                Else
                    strOrganization = "NULL"
                End If
                'Get CaseNumber
                If objXMLNode.SelectSingleNode("Report/"CaseNumber") Is Nothing Then
                    strCaseNumber = objXMLNode.SelectSingleNode(CaseNumber").InnerText
                Else
                    strCaseNumber = "NULL"
                End If
                
                objStreamWriter.WriteLine(strFName + "," + strMName + "," + strLName + "," + strLogIn + "," + strOrganization + "," + strOrganizationName + "," + strCaseNumber)
            Next

            objStreamWriter.Flush()
            objOutputMemStream.Position = 1

Posted 5-Apr-18 6:14am

Member 11403304

Updated 5-Apr-18 7:28am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

A_Griffin · Accepted Answer · 2018-04-05T07:30:00

Solution 1

IDK, but this doesn't make sense:

VB

If objXMLNode.SelectSingleNode("Report/"FName") Is Nothing Then
    strFirstName = objXMLNode.SelectSingleNode(FName").InnerText
Else
    strFirstName = "NULL"
End If

... if it = Nothing you can't assign it. You should probably change that to

VB

If Not objXMLNode.SelectSingleNode("Report/"FName") Is Nothing Then

Posted 5-Apr-18 7:30am

A_Griffin

Comments

Member 11403304 5-Apr-18 13:42pm

Thanks for your help this worked as you suggested
I did solve this by adding Not
e.g. If Not objXMLNode.SelectSingleNode("Report/FName") Is Nothing Then