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

Hi,
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:
'id'
'Nombre' (Name of the product, it is a unique name)
"Numero de Parte" (means 'Part Number')
"SKU"

Attached is the image of the VB.net Form and the code is in the link:
This is the link to my previous post:
http://www.experts-exchange.com/Programming/Languages/.NET/Q_28619544.html
experts-exchange.PNG
LVL 2
José PerezAsked:
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.

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.
0
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 _
                                }).SingleOrDefault()

        '' 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
            'Console.WriteLine(data)
            txtTipoCambio.Text = exchangeInfo(0)
            txtFecha.Text = exchangeInfo(1)
        Next


        '' 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 _
              }).ToList()

        '' 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

0
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?
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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.
0
Fernando SotoRetiredCommented:
Will the SKU always be required on the search and the other three be optional?
0
José PerezAuthor Commented:
Only 1 is mandatory (any of the 4), the other 3 are optionals to run the search.
0
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.")
Else
    bs.Position = idx
End If

Open in new window

0

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
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?

http://www.experts-exchange.com/Programming/Languages/.NET/Q_28626976.html
0
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
.NET Programming

From novice to tech pro — start learning today.