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

VS2013: Windows Form: Search into loaded Excel xml into form with textboxes.

I have a working code that loads an excel xml file in a Form with computer products. I need to be able to search into this xml and display the information for a particular product. My Form contains 4 textboxes (see attached) that I would like to use to search for specific product. The fileds I would like to search are:
'Nombre' (Name of the product, it is a unique name)
"Numero de Parte" (means 'Part Number')

Attached is the image of the VB.net Form and the code is in the link:
This is the link to my previous post:
José Perez
José Perez
  • 4
  • 4
1 Solution
Fernando SotoRetiredCommented:
Hi Oscar;

The XML is already loaded into the form when you used my last solution from your previous post, so there is no need to read it again. If you used the code from the last post then you already have a collection of objects and you can use this collection to get the info needed. If you post your code like I posted in your last solution I will try and build a query against that collection.
José PerezAuthor Commented:
Public Class UploadXML
    '' Holds the data from the XML document after parsing
    Dim results As New List(Of ExcelRowData)()
    '' Holds a copy of the data results setup for the BindingNavigator
    Dim bs As New BindingSource()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub txtDlgBox_Click(sender As Object, e As EventArgs) Handles txtDlgBox.Click
        OpenFileDialog1.Filter = "Archivo Xml (*.xml)|*.xml"
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            txtRuta_eXML.Text = OpenFileDialog1.FileName
        End If
    End Sub

    Private Sub btnCargaXML_Click(sender As Object, e As EventArgs) Handles btnCargaXML.Click
        '' Load the XML document to memory
        'Dim xdoc As XDocument = XDocument.Load("Path and Filename to the XML File")
        'Dim xdoc As XDocument = XDocument.Load("C:\Users\Oscar\Downloads\Computadores de Mesa.xml")
        Dim xdoc As XDocument = XDocument.Load(txtRuta_eXML.Text)

        '' XML Namespace to access the DocumentProperties node and its children
        Dim o As XNamespace = xdoc.Root.GetNamespaceOfPrefix("o")

        ' Query to get the DocumentProperties info.
        Dim docProperties = (From dp In xdoc.Root.Descendants(o + "DocumentProperties") _
                            Select New With _
                                { _
                                    .Author = dp.Element(o + "Author").Value, _
                                    .LastAuthor = dp.Element(o + "LastAuthor").Value, _
                                    .Created = dp.Element(o + "Created").Value, _
                                    .Version = dp.Element(o + "Version").Value _

        '' Accessing the results
        Console.WriteLine("{0}  :  {1}  :  {2}  :  {3}", _
                          docProperties.Author, docProperties.LastAuthor, docProperties.Created, docProperties.Version)

        '' Gets the XML Namespace for the data we will be working with
        Dim ss As XNamespace = xdoc.Root.GetNamespaceOfPrefix("ss")

        '' The variable exchangeInfo will contain a List(Of String)
        '' which has the Text values of the two Data nodes.
        '' All you will need to do is parse the string for the data you want
        Dim exchangeInfo = (From ei In xdoc.Descendants(ss + "Row") _
                            Where ei.HasAttributes() AndAlso ei.Attribute(ss + "StyleID").Value = "Header" AndAlso _
                                  ei.Descendants(ss + "Data").Count() = 2
                            From data In ei.Descendants(ss + "Data") _
                            Select data.Value).ToList()

        For Each data As String In exchangeInfo
            txtTipoCambio.Text = exchangeInfo(0)
            txtFecha.Text = exchangeInfo(1)

        '' Parse the XML document and place each row in a class called ExcelRowData
        results = (From n In xdoc.Root.Descendants(ss + "Row") _
              Where n.Elements(ss + "Cell").Count() = 9 AndAlso n.Attributes().Count() = 0 _
              Select New ExcelRowData With _
              { _
                    .Categoría = n.Elements()(0).Value, _
                    .Subcategoría = n.Elements()(1).Value, _
                    .Nombre = n.Elements()(2).Value, _
                    .Marca = n.Elements()(3).Value, _
                    .Precio = n.Elements()(4).Value, _
                    .Disponibilidad = n.Elements()(5).Value, _
                    .No_de_Parte = n.Elements()(6).Value, _
                    .SKU = n.Elements()(7).Value, _
                    .Atributos = n.Elements()(8).Value _

        '' Assign the parsed XML data into the BindingSource
        bs.DataSource = results
        '' Connect the BindingSource to the BindingNavigator
        BindingNavigator1.BindingSource = bs

        '' Bind all the TextBox controls to the field they are to display
        txtCategoria.DataBindings.Add(New Binding("Text", bs, "Categoría"))
        txtSubcategoria.DataBindings.Add(New Binding("Text", bs, "Subcategoría"))
        txtNombre.DataBindings.Add(New Binding("Text", bs, "Nombre"))
        txtMarca.DataBindings.Add(New Binding("Text", bs, "Marca"))
        txtPrecioCompra.DataBindings.Add(New Binding("Text", bs, "Precio"))
        txtDisponibilidad.DataBindings.Add(New Binding("Text", bs, "Disponibilidad"))
        txtNodeParte.DataBindings.Add(New Binding("Text", bs, "No_de_Parte"))
        txtSKU.DataBindings.Add(New Binding("Text", bs, "SKU"))
        txtAtributos.DataBindings.Add(New Binding("Text", bs, "Atributos"))
    End Sub

    Private Sub ComboBox4_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboDisco.SelectedIndexChanged

    End Sub
End Class

'' Custom class to hold one row of data from the XML document
Public Class ExcelRowData
    Public Property Categoría As String
    Public Property Subcategoría As String
    Public Property Nombre As String
    Public Property Marca As String
    Public Property Precio As String
    Public Property Disponibilidad As String
    Public Property No_de_Parte As String
    Public Property SKU As String
    Public Property Atributos As String
End Class

Open in new window

Fernando SotoRetiredCommented:
Hi Oscar;

Question, are you wishing to search the data that is connected to the Binding Navigator and display it in the form so that you do not have to continually click on the next record key?

Will all the text boxes for the search be required or any or all text box data?
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

José PerezAuthor Commented:
I would like to search for an SKU, for example, and load the resulting fields of the prouct into the textboxes of the form.
Fernando SotoRetiredCommented:
Will the SKU always be required on the search and the other three be optional?
José PerezAuthor Commented:
Only 1 is mandatory (any of the 4), the other 3 are optionals to run the search.
Fernando SotoRetiredCommented:
Hi Oscar;

The code snippet below should do what you need it to. It will need a modification as far as TextBox names to match the ones you used. Also in your question you have a fiels called "id" but I did not find a matching field in the class ExcelRowData for it, so I used one of the others for testing and will need to be modified below in the third If statement.

'' Query to get the required field
Dim selected = results.Where(Function(s) s.SKU = txtSKU.Text)

'' If this TextBox is not empty then add it to the query
If Not String.IsNullOrEmpty(txtNumeroDeParte.Text) Then
    selected = selected.Where(Function(i) i.No_de_Parte = txtNumeroDeParte.Text)
End If

'' If this TextBox is not empty then add it to the query
If Not String.IsNullOrEmpty(txtNombre.Text) Then
    selected = selected.Where(Function(i) i.Nombre = txtNombre.Text)
End If

'' If this TextBox is not empty then add it to the query
If Not String.IsNullOrEmpty(txtID.Text) Then
    selected = selected.Where(Function(i) i.Precio = txtID.Text)
End If

'' Find the index of the item that was selected
Dim idx As Integer = results.IndexOf(selected.SingleOrDefault())

'' If idx is greater then -1 it was found and set the BindingNavigator to display it
'' otherwise display message or anything else you need to do
If idx < 0 OrElse idx > results.Count - 1 Then
    MessageBox.Show("The item was not found in the collection.")
    bs.Position = idx
End If

Open in new window

José PerezAuthor Commented:
It works perfect! Now I'm going to post a new challenge for this very same post. I would need to save the whole form (data in textboxes) in an MSSQL 2012 database server. Can you please try to help with one also?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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