# Using Double as a DataTable.DefaultView.RowFilter

• ### General discussion

• Hi

I have been playing around with some code, just as a timewaster with no purpose what so ever, just keeping my mind alive :)

The idea I was trying, was to set up a DataTable with differing DataTypes - just String, Double, Decimal, Date, Boolean and Integer, and then have a routine that would determine the DataType to build a RowFilter to act properly for the different types and produce  Filtered results across the entire DataGridView display of the Data.

*

As I developed the idea, everything was fine - until I fell foul of the indistinct natue of floating point numbers in comparison situations. This would manifest itself as a Filter with obviously equal visible values when viewed on screen but failing to match when the Filter is applied.

*

I overcame the hurdle right away by using the DataType of Decimal for any Filter comparisons on Double values. This works fine and appears to not care about this treatment.

*

Now, although I have no trouble in determining a (close as necessary) type of equality between two Double values, using the difference to be within a tolerance to straddle to minute differences from the way Doubles are held in memory. However, that is of no use in building a DataRow Filter.

*

What I do not understand fully is whether or not there are any pitfalls in using a Decimal equivalency for two Double values? Would there be any hazards lurking in the background with this approach. I have tried many many tests to try and find anomalies, but so far I have found none.

*

Here is the entire code I am using.

*

```' This example needs a Form1 with
' a blank DataGridView1,
' TextBox1 and Label1

' This code is an attempt to auto Filter DataTable
' rows on some of the most common Data Types
' String, Date, Boolean, Decimal, Integer
' NOTE: to enable comparisons for the Filter,
' both Decimal and Double are compared as Decimal.

Option Strict Off
Option Explicit On
Public Class Form1
Dim myTable As New DataTable("Freddy")
Dim rand As New Random
Dim Path As String = Application.StartupPath & "\Data\Data.xml"
Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
Dim parent As String = My.Computer.FileSystem.GetParentPath(Path)
If Not IO.Directory.Exists(parent) Then
IO.Directory.CreateDirectory(parent)
End If
' save data file when exit the application
myTable.WriteXml(Path, XmlWriteMode.WriteSchema)
End Sub
If IO.File.Exists(Path) Then
' data file found so load it
Else
' no data file found so create dummy data
With myTable

' just some test data
Dim r1() As String = {"Freddy", "Mary", "Brian", "Les", "Smith", "Brown", "MacHine", "Elizabeth", "Jones", "Tom"}
Dim r2() As String = {"Male", "Female", "Undeclared"}
Dim r3() As Boolean = {True, False}

For i As Integer = 0 To 222
.Rows.Add(i, If(rand.Next(0, 2) = 1, "False", "True"), rand.NextDouble() * 10000, r1(rand.Next(r1.Length)), r1(rand.Next(r1.Length)), r2(rand.Next(r2.Length)), Now.Date, rand.NextDouble() * 100)

Next
Dim md As Double = Double.MaxValue

End With
End If

bs.DataSource = myTable

' just some formatting of DGV
With DataGridView1
.DataSource = bs
.MultiSelect = False
.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
.SelectionMode = DataGridViewSelectionMode.CellSelect
.Columns("Decimal").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
For Each c As DataGridViewColumn In DataGridView1.Columns
c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
Next
End With
Label1.Text = "Rows = " & myTable.DefaultView.Count.ToString
End Sub
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
Label1.Text = "Rows = " & DoFilter().ToString
End Sub
Function DoFilter() As Integer
Dim t As String = Trim(TextBox1.Text)
If t.Length < 1 Then
myTable.DefaultView.RowFilter = Nothing
Return myTable.DefaultView.Count.ToString
End If
Dim fstring As String = Nothing
Try
For Each c As DataColumn In myTable.Columns
Select Case c.DataType
' ================================
' Treat both Double and Decimal as
' Decimal to enable matching
' ================================
Case GetType(Decimal), GetType(Double)
Dim v As Decimal = 0
If Decimal.TryParse(t, v) Then
fstring &= "[" & c.ColumnName & "] = '" & v & "' or "
End If

Case GetType(Date)
Dim d As DateTime = Now
If Date.TryParse(t, d) Then
fstring &= "[" & c.ColumnName & "] = '" & CDate(t) & "' or "
End If

Case GetType(Boolean)
If t.ToLower = "true" Or t.ToLower = "false" Then
fstring &= "[" & c.ColumnName & "] = '" & t & "' or "
End If

Case GetType(Integer)
Dim v As Integer = 0
If Integer.TryParse(t, v) Then
fstring &= "[" & c.ColumnName & "] = '" & v & "' or "
End If

Case Else
fstring &= "[" & c.ColumnName & "] like '" & t & "' or "

End Select
Next
fstring = fstring.Substring(0, fstring.Length - 3)
myTable.DefaultView.RowFilter = fstring
Catch ex As Exception
MessageBox.Show("ERROR: " & ex.Message)
End Try
Return myTable.DefaultView.Count.ToString
End Function
End Class
```

Regards Les, Livingston, Scotland

• Edited by Thursday, November 2, 2017 1:12 AM typos and grammer corrections.
Thursday, November 2, 2017 12:47 AM

### All replies

• I would suggest if this is strictly for a DataGridView/DataTable to look at the following component. Literally it takes one line of code and figures out the filtering for you. Take a look at the source (it's C#), see how they did it and then apply similar logic if you really want to write this yourself without a component.

Note that each column can be filtered or just one. Also it permits you to customize the interface and data sources (which I have done). I've used this in one app back in 2005, still running and filtering perfectly.

https://www.codeproject.com/Articles/33786/DataGridView-Filter-Popup

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Thursday, November 2, 2017 1:59 AM
• Hi

Thanks Karen.

Non of the the code from the link is usable on my setup as I have removed all database stuff from my installation of VS.

I don't have any use for managed databases in any shape or form. I am interested in the use of DataTables and simple data handling for simple uses though and that is what is behind the post.

My simple attempts above are no more than that. The main point of my post is that I have done something that works as expected in all the tests, but I still have a niggle with whether or not I have introduced a 'faulty' idea in the way I decide equality between Doubles by using Decimal comparisons.

Regards Les, Livingston, Scotland

Thursday, November 2, 2017 2:37 AM