DataView Filter - Excluding Null when not required.

Language VB.NET (VS 2008) Database: ACCESS

The DataView doesn't seem to be filtering correctly. It seems to automatically filter out null values even though it is set to "Serial LIKE '%" & tcSerialTB.Text & "%'"

Code:

ci = New DataTable("Capital")
Dim cid As New DataColumn
cid.DataType = System.Type.GetType("System.Int32")
cid.ColumnName = "CID"
ci.Columns.Add("CID")

Dim filenumber As New DataColumn
filenumber.DataType = System.Type.GetType("System.String")
filenumber.ColumnName = "FileNumber"
ci.Columns.Add("FileNumber")

Dim item As New DataColumn
item.DataType = System.Type.GetType("System.Int32")
item.ColumnName = "Item"
ci.Columns.Add("Item")

Dim purchaseyear As New DataColumn
purchaseyear.DataType = System.Type.GetType("System.Int32")
purchaseyear.ColumnName = "PurchaseYear"
ci.Columns.Add(purchaseyear)

Dim quant As New DataColumn
quant.DataType = System.Type.GetType("System.Int32")
quant.ColumnName = "Quant"
ci.Columns.Add(quant)

Dim make As New DataColumn
make.DataType = System.Type.GetType("System.String")
make.ColumnName = "Make"
ci.Columns.Add("Make")

Dim model As New DataColumn
model.DataType = System.Type.GetType("System.String")
model.ColumnName = "Model"
ci.Columns.Add(model)

Dim serial As New DataColumn
serial.DataType = System.Type.GetType("System.String")
serial.ColumnName = "Serial"
ci.Columns.Add(serial)

Dim cost As New DataColumn
cost.DataType = System.Type.GetType("System.Int32")
cost.ColumnName = "Cost"
ci.Columns.Add(cost)

Dim sold As New DataColumn
sold.DataType = System.Type.GetType("System.Boolean")
sold.ColumnName = "Sold"
ci.Columns.Add(sold)

Dim cats As New DataColumn
cats.DataType = System.Type.GetType("System.String")
cats.ColumnName = "CategoryName"
ci.Columns.Add(cats)

Dim itemname As New DataColumn
itemname.DataType = System.Type.GetType("System.String")
itemname.ColumnName = "ItemName"
ci.Columns.Add("ItemName")

For Each dtr As DataRow In icT.Rows
    ci.ImportRow(dtr)
Next

Once the table is made another sub does the filtering:

Private Sub tcfilterit()

        Try
            Dim fil As New StringBuilder

            fil.Append("Serial LIKE '%" & tcSerialTB.Text & "%'")

            If tcSoldCB.SelectedItem = "Sold" Then
                fil.Append(" AND ")
                fil.Append("Sold = TRUE")
            ElseIf tcSoldCB.SelectedItem = "Not Sold" Then
                fil.Append(" AND ")
                fil.Append("Sold = FALSE")
            Else
                fil.Append(vbNullString)
            End If

            If tcCatCB.SelectedItem = vbNullString Then
            ElseIf tcCatCB.SelectedItem = "All" Then
            Else
                fil.Append(" AND ")
                fil.Append("CategoryName LIKE '%" & tcCatCB.SelectedItem & "%'")
            End If

            If tcItemCB.SelectedItem = vbNullString Then
            ElseIf tcItemCB.SelectedItem = "All" Then
            Else
                fil.Append(" AND ")
                fil.Append("ItemName LIKE '%" & tcItemCB.SelectedItem & "%'")
            End If

            Dim dv As New DataView()
            dv.Table = ci
            dv.RowFilter = fil.ToString
            MsgBox(ci.Rows.Count & " " & dv.ToTable.Rows.Count)
            tcDGV.DataSource = dv.ToTable
        Catch nre As NullReferenceException
        Catch ee As EvaluateException
            MsgBox(ee.ToString, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

    End Sub

ansaurus

tags:

views:

answers:

DataView Filter - Excluding Null when not required.

related questions