damir_john
asked on
Search for the closest match in XML DataSet
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.
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>
ASKER
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."
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
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
ignore that post, I wasn't done yet and I hit submit by accident.
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
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"
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"
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
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
ASKER
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.
Public member 'value' on type 'String' not found.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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