Search for the closest match in XML DataSet

damir_john
damir_john used Ask the Experts™
on
Hallo,

I'm currently struggling to find a way to search for the closest match in an XML DataSet.

My project consist of a form and one XML file. The form has a Searchbox (Textbox1) that will search the column Height, a textbox to show the result (textbox2), a button (button1), a DataGridView (DataGridView1) and a DataSet (DataSet1)

Public Class Form1

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

        Dim filePath As String = "\XMLFile1.xml"

        DataSet1.ReadXml(filePath)

        DataGridView1.DataSource = DataSet1
        DataGridView1.DataMember = "Tank_205"
    End Sub
End Class

The user enters the value into the searchbox and then I want it to search for the closest value (It must be equal to or below. It must not round the value up.)

E.g. If I enter 2895 in the searchbox it will search the column height in table Tank_205 and return the corresponding value (volume) for 1500 in height in TextBox2. But if I enter 3000 in the searchbox it should return the corresponding value (volume) for 3000 in height in TextBox2, so it has to be Equal to or below. Textbox1.text <= "Height".

Question:
However all I really want to know is how to find the closest value that is equal to or below the value specified in the XML file.
 
<?xml version="1.0" encoding="utf-8" ?>
<Tank_Data>
  <Tank_205>
    <height>1000</height>
    <volume>38132</volume>
  </Tank_205>
  <Tank_205>
    <height>1500</height>
    <volume>43222</volume>
  </Tank_205>
  <Tank_205>
    <height>3000</height>
    <volume>101020</volume>
  </Tank_205>
  <Tank_205>
    <height>4500</height>
    <volume>133002</volume>
  </Tank_205>
</Tank_Data>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
If may be misunderstanding what you want to do, but since you are already creating a dataset and binding it to your DataGridView, could you do the following:

1. Create a DataView from your existing datasource and define a RowFilter where height is <= the value they supply in the search box.  Sort the DataView Descending.

2. Then you could simply grab the first row in the DataView, which would be your closest match.

Quick question:

Are you also wanting to select the DataGridView cell that the closet match is displayed in?

Regards,

Keith

Author

Commented:
The dataGridView was just "eyecandy". I tried another approach. Basically I just want it to search in my XML file  and return the value equal to or below the height specified in the searchbox. Then I have to show the volume in another textbox. From there I need to use it to calculate some values.

Tried this code (Am I on the right track?) but I get an error saying :
{"Index was outside the bounds of the array" : "Index was outside the bounds of the array."
Public Class Form2
 
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim filePath As String = "\XMLFile1.xml"
 
        DataSet1.ReadXml(filePath)
 
    End Sub
 
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strExpr As String = "height >= 'TextBox1.text'"
        ' sort descending by column named CompanyName.
        Dim strSort As String = "height DESC"
 
        Dim myTable As DataTable = DataSet1.Tables("Tank_205")
        ' Use the Select method to find all rows matching the filter.
        Dim foundRows() As DataRow = myTable.Select(strExpr, strSort)
        Me.TextBox2.Text = foundRows(0)("volume")
    End Sub
End Class

Open in new window

Commented:
I think you're making this too difficult on yourself.  Give this a try:


Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
       Dim filePath As String = "\XMLFile1.xml"
       DataSet1.ReadXml(filePath)
 
 End Sub
 
 
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim strExpr As String = "TextBox1.text"
 
       ' Stuff your dataset into a table
      
        Dim myTable As DataTable = DataSet1.Tables("Tank_205")
 
       ' Now create a DataView over the table you just filled
 
 
 ' Use the Select method to find all rows matching the filter.
        Dim foundRows() As DataRow = myTable.Select(strExpr, strSort)
        Me.TextBox2.Text = foundRows(0)("volume")
    End Sub

Open in new window

JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Commented:
ignore that post, I wasn't done yet and I hit submit by accident.

Commented:
Ok...let's try that again.  This should get you what you want.


Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
       Dim filePath As String = "\XMLFile1.xml"
       DataSet1.ReadXml(filePath)
 
 End Sub
 
 
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim strExpr As String = "TextBox1.text"
 
       ' Stuff your dataset into a table
      
        Dim myTable As DataTable = DataSet1.Tables("Tank_205")
 
       ' Now create a DataView over the table you just filled
 
        Dim myDV as DataView
        myDV = New DataView(myTable)
 
        ' Now create a row filter on the dataview based on the height that the user entered.  Also sort it by Heigh Desc.
 
         myDV.Rowfilter = "HEIGHT <= '" & strExpr & "' ORDER BY HEIGHT DESC"
 
         ' Now....your closest exact match or closest match should be
 
         For Each dvRow as DataRowView in myDV
              YourClosetMatch = dvRow(0).value
         Next
 
 End sub

Open in new window

Commented:
one thing I missed...if your HEIGHT field is numeric you should do this:

Dim strExpr as Integer = CInt(TextBox1.Text)

Also, you would need to change the DataView rowfilter syntax to this:

myDV.Rowfilter = "HEIGHT <= " & strExpr & " ORDER BY HEIGHT DESC"


Commented:
One other goof...you would only want the first dvRow...so change this code:

        For Each dvRow as DataRowView in myDV
              YourClosetMatch = dvRow(0).value
         Next

To:

        For Each dvRow as DataRowView in myDV
              YourClosetMatch = dvRow(0).value
              Exit For
         Next

Author

Commented:
So far it seems to be working except the line dvRow(0).value gives an error saying :
Public member 'value' on type 'String' not found.
Commented:
OK...my bad, I was typing code from memory!  Always a bad idea.

Instead of dvRow(0).value

use this

dvRow.item(0)

or

dvRow.item("HEIGHT")

Sorry, hope this helps.

Author

Commented:
Thank you very much for your help. I actually realized that I just had to remove the .value before your post. Just didnt have an opportunity to test since I was at work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial