Help with passing values from multiple rows of Gridview to an XML file

Hello,

I am using the code below to retreive countries from a Textbox, check to see if it already exist in country.xml, if it does ot exist:

A.  Add it to Country.xml with a newly created ID
B. Add the newly created ID to Link. xml file

An if it does exist:

A. Retreive the existing ID
B. Add the xisting ID to Link.xml file.

Now, I need to do the same for multiple rows in a GridView

A. When I loop through the rows of the Gridview  and check if the data exist in an country.xml, if it does not exist:

A.  How do I add it and to country.xml with a newly created ID then
B.  How do I add the newly created ID in a string variable (s)

An if it does exist:

A. How do I retreive the existing ID and add it to the same string (s) as mentioned above.

For example, If GridView1 contains

id  CtryUser
1   BEL
2   CAN
3   DEU
4   USA

and only DEU and USA don't exist in Country XML file, code should be modified to first

Take existing IDs for BEL and CAN and create a string to include

S = '1', '2'

Than create new ID for DEU, pass it in the same string

S = '1', '2', '3'

Than create new ID for USA, pass it in the same string

S = '1', '2', '3', '4'

Afterwards, Add the string valude the the Link.xml country_ID element

<CountryUser_ID>'1', '2', '3', '4' </CountryUser_ID>

 

Code:


Dim xyz() As String = New String() {"CountryOrigin", "CountryUser"}
       
 For i = 0 To 1
            Dim curXYZ = xyz(i)
            Dim MyFix As XDocument = XDocument.Load(Server.MapPath("~/App_Data/" & curXYZ & ".xml"))

            Dim xid As String
            xid = curXYZ & "_ID"

            Dim LargestFixID = (From el In MyFix.Descendants(curXYZ & "Table") Select
                CInt(el.<xid>.Value)).ToList
            LargestFixID.Sort()

            Dim xtable As String

            xtable = curXYZ & "Table"
            Dim ctrl As Control = MultiView1.FindControl("C1" & curXYZ)
            Dim Txt As String = Nothing
            If TypeOf (ctrl) Is C1ComboBox Then
                Txt = CType(ctrl, C1ComboBox).Text
            End If

                     Dim CheckForItemFix = (From el In MyFix.Descendants(xtable).Elements(curXYZ) Select el.Value).ToList


            'Check if value is already in xml files, if not add it to the xml files
            If Not CheckForItemFix.Contains(Txt) Then

                Dim xNew As XElement = New XElement(xtable)

                xNew.Add(New XElement(xid, (LargestFixID.Count() + 1)))

                xNew.Add(New XElement(curXYZ, Txt))

                MyFix.Root.Add(xNew)

                MyFix.Save(Server.MapPath("~/App_Data/" & curXYZ & ".xml"))

                'Prepare variable to add to Link.xml file

                Select Case curXYZ
                    Case "CountryOrigin"
                        searchIdCountryOrigin = LargestFixID.Count() + 1
                    Case "CountryUser"
                        searchIdCountryUser = LargestFixID.Count() + 1

           'Check if value is already in xml files, if exist add retrieve its ID

            Dim searchFix As String = Txt
            Dim domFix As New XmlDocument()
            domFix.Load(Server.MapPath("~/App_Data/" & curXYZ & ".xml"))
            Dim listFix As XmlNodeList = domFix.SelectNodes("//" & xtable)
           
               If CheckForItemFix.Contains(Txt) Then
                For Each node As XmlNode In listFix
                    If node(curXYZ).InnerText.Equals(searchFix) Then
                        Select Case curXYZ
                            Case "CountryOrigin"
                                searchIdCountryOrigin = node(xid).InnerText
                            Case "CountryUser"
                                searchIdCountryUser = node(xid).InnerText
                    End Select
                        Exit For
                    End If
                Next
            End If

           ‘Save Data to Link.xml File              

                     Dim MyDocLinkSSADB As XDocument = XDocument.Load(Server.MapPath("~/App_Data/Link.xml"))

                Dim LinkID = (From el In MyDocLinkA.Descendants("LinkA") Select CInt(el.<LinkAID>.Value)).ToList

                LinkID.Sort()

                Dim xNewLink As XElement = New XElement("LinkA")

                xNewLink.Add(New XElement("LinkAID", (LinkID.Last() + 1)))
                If i = 1 Then
 
 MyDocLinkSSADB.Root.Add(New XElement("LinkA", New XElement("CountryOrigin_ID", searchIdCountryOrigin), New XElement("CountryUser_ID", searchIdCountryUser))
                            MyDocLinkSSADB.Save(Server.MapPath("~/App_Data/Link.xml"))

End If

Curent Link.xml based on code:

<LinkTable>
<LinkA>1</LinkA>
<CountryUser_ID>1</CountryUser_ID>
<CountryOrigin_ID>1</CountryOrigin_ID>
</LinkTable>
<LinkTable>
<LinkA>2</LinkA>
<CountryUser_ID>2</CountryUser_ID>
<CountryOrigin_ID>3</CountryOrigin_ID>
</LinkTable>
<LinkTable>
<LinkA>3</LinkA>
<CountryUser_ID>3</CountryUser_ID>
<CountryOrigin_ID>3</CountryOrigin_ID>
</LinkTable>


Desired xml file based on example mentioned at beginning of this post.

<LinkTable>
<LinkA>1</LinkA>
<CountryUser_ID>'1','2','3','4'</CountryUser_ID>
<CountryOrigin_ID>1</CountryOrigin_ID>
</LinkTable>


I will need to load the GridView again from data in the Link.xml, but hope to post this question in another post after I resolve this issue.

Thanks,

victor
vcharlesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nepaluzCommented:
You really should get acquainted ith LINQ
Also Creating XML in Visual Basic Using LINQ

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
I'm afraid I couldn't find the answer in the LINQ links. The two main questions are,

1. How do I loop through a grid, take value of row and check it against data in an xml file (if exist) than take the existing ID and create a string variable?

2. How do I pass that string variable to another xml file?
vcharlesAuthor Commented:
Hi,

I managed to loop through the Grid with the code below but my problem is I am saving data in CountryUser.xml  with the same ID.

For example, I have

ESP
DEU
FRA

in my grid and they were not initially in my CountryUser.xml file, when I save the data
it shows  in CountryUser.xml as

<CountryUser_ID>20</CountryUser_ID>
<CountryUser>ESP</CountryUser>
<CountryUser_ID>20</CountryUser_ID>
<CountryUser>DEU</CountryUser>
<CountryUser_ID>20</CountryUser_ID>
<CountryUser>FRA</CountryUser>

How do I increment the CountryUser_ID for it to be 20,21,22?

And in my LinkSSADB file it shows as

<CountryUser_ID>20</CountryUser_ID>

However it needs to show as

<CountryUser_ID>'20','21,'22'</CountryUser_ID>

How do I modify my code below to fix these two issues?

  Protected Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
        Dim searchIdCountryOrigin As String = Nothing
        Dim searchIdCountryUser As String = Nothing
        Dim xyz() As String = New String() {"CountryUser", "CountryOrigin"}
        For i = 0 To 1
            Dim curXYZ = xyz(i)
            Dim MyFix As XDocument = XDocument.Load(Server.MapPath("~/App_Data/" & curXYZ & ".xml"))
            Dim xid As String
            xid = curXYZ & "_ID"
            Dim LargestFixID = (From el In MyFix.Descendants(curXYZ & "Table") Select CInt(el.<xid>.Value)).ToList
            LargestFixID.Sort()
            Dim xtable As String
            xtable = curXYZ & "Table"
            Dim CheckForItemFix = (From el In MyFix.Descendants(xtable).Elements(curXYZ) Select el.Value).ToList
            Dim S As String
            For x As Integer = 0 To C1GridView1.Rows.Count - 1
                If Not CheckForItemFix.Contains(C1GridView1.Rows(x).Cells(0).Text) Then
                    Dim xNew As XElement = New XElement(xtable)
                    xNew.Add(New XElement(xid, (LargestFixID.Count() + 1)))
                    xNew.Add(New XElement(curXYZ, C1GridView1.Rows(x).Cells(0).Text))
                    MyFix.Root.Add(xNew)
                    MyFix.Save(Server.MapPath("~/App_Data/" & curXYZ & ".xml"))
                    Select Case curXYZ
                        Case "CountryUser"
                            searchIdCountryUser = LargestFixID.Count() + 1
                    End Select
                End If
                Dim searchFix As String = C1GridView1.Rows(x).Cells(0).Text
                Dim domFix As New XmlDocument()
                domFix.Load(Server.MapPath("~/App_Data/" & curXYZ & ".xml"))
                Dim listFix As XmlNodeList = domFix.SelectNodes("//" & xtable)
                If CheckForItemFix.Contains(C1GridView1.Rows(x).Cells(0).Text) Then
                    For Each node As XmlNode In listFix
                        If node(curXYZ).InnerText.Equals(searchFix) Then
                            Select Case curXYZ
                                Case "curXYZ"
                                    searchIdCountryUser = node(xid).InnerText
                            End Select
                            Exit For
                        End If
                    Next
                End If
            Next
            Dim MyDocLinkSSADB As XDocument = XDocument.Load(Server.MapPath("~/App_Data/LinkSSADB.xml"))
            Dim LinkID = (From el In MyDocLinkSSADB.Descendants("LinkA") Select CInt(el.<LinkAID>.Value)).ToList
            LinkID.Sort()
            Dim xNewLink As XElement = New XElement("LinkA")
            xNewLink.Add(New XElement("LinkAID", (LinkID.Last() + 1)))
            If i = 0 Then
                MyDocLinkSSADB.Root.Add(New XElement("LinkA", New XElement("LinkAID", New XElement("CountryOrigin_ID", searchIdCountryOrigin), New XElement("CountryUser_ID", searchIdCountryUser))))
                MyDocLinkSSADB.Save(Server.MapPath("~/App_Data/LinkSSADB.xml"))
            End If
        Next
    End Sub
vcharlesAuthor Commented:
Hi,

I managed to get it to work with thye code below, but my results are 1,2,3,  How do I remove the last ","?


 Dim CheckForItemFix = (From el In MyFix.Descendants(xtable).Elements(curXYZ) Select el.Value).ToList
            For x As Integer = 0 To C1GridView1.Rows.Count - 1
                If Not CheckForItemFix.Contains(C1GridView1.Rows(x).Cells(0).Text) Then
                    Dim xNew As XElement = New XElement(xtable)
                    xNew.Add(New XElement(xid, num))
                    xNew.Add(New XElement(curXYZ, C1GridView1.Rows(x).Cells(0).Text))
                    MyFix.Root.Add(xNew)
                    MyFix.Save(Server.MapPath("~/App_Data/" & curXYZ & ".xml"))
                    Select Case curXYZ
                        Case "CountryUser"
                            searchIdCountryUser = num
                    End Select
                    num = num + 1
                    num1 += num & ","
                End If
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.