Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2015-02-27
Medium Priority
Last Modified: 2015-03-01
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:
Question by:José Perez
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 64

Expert Comment

by:Fernando Soto
ID: 40637179
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.

Author Comment

by:José Perez
ID: 40637194
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

LVL 64

Expert Comment

by:Fernando Soto
ID: 40637314
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?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

by:José Perez
ID: 40637327
I would like to search for an SKU, for example, and load the resulting fields of the prouct into the textboxes of the form.
LVL 64

Expert Comment

by:Fernando Soto
ID: 40637329
Will the SKU always be required on the search and the other three be optional?

Author Comment

by:José Perez
ID: 40637336
Only 1 is mandatory (any of the 4), the other 3 are optionals to run the search.
LVL 64

Accepted Solution

Fernando Soto earned 2000 total points
ID: 40637497
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


Author Comment

by:José Perez
ID: 40638198
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?


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question