[RESOLVED] Filter .DBF File In DataGridView

[M4cJunk13]

Hello,

I'm trying to filter a search on a .DBF file and I'm running into some errors. From what I can gather the error I keep getting is...

"An unhandled exception of type 'System.Data.EvaluateException' occurred in System.Data.dll
Additional information: Cannot find column [PARTNO]."

I am able to populate my DataGridview and everything, I just can't seem to filter it. Please help, much appreciation.

Imports System
Imports System.Data.OleDb
Public Class Form1


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

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim LinkConn As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\\; Extended Properties = dBase III; User ID=;Password="
            Dim con As OleDbConnection = New OleDbConnection()
            con.ConnectionString = LinkConn
            con.Open()

            Dim Query As String = "Select * from INV"
            Dim adapater As OleDbDataAdapter = New OleDbDataAdapter(Query, con)
            Dim ds As DataSet = New DataSet()
            adapater.Fill(ds)
            con.Close()

            Me.DataGridView1.DataSource = ds.Tables(0)

        Catch ex As Exception
            MsgBox("Error while connecting to databse." & vbNewLine & ex.Message)

        End Try

    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        Dim dbDataSet As New DataTable
        Dim DV As New DataView(dbDataSet)
        DV.RowFilter = String.Format("Convert(PARTNO, 'System.String') Like '%{0}%' or DESCRIPT Like '%{0}%' or Convert(ONHAND, 'System.String') Like '%{0}%' ", TextBox1.Text)
        DataGridView1.DataSource = DV
    End Sub
End Class

[dday9]

The error seems obvious, it cannot find the column 'PARTNO'. Could you post the schema of your table?

[M4cJunk13]

The error seems obvious, it cannot find the column 'PARTNO'. Could you post the schema of your table?

Thank you for looking at this. I also thought that too, but when I run it and change this line in the button click to

 Dim Query As String = "Select PARTNO from INV"

it finds the column. So I maybe my syntax is wrong in the TextBoxChange? I uploaded the .DBF file that I'm using. There really isn't more to it than just that file. All I have is a Button, DataGridView and a TextBox on a Windows Form.

[dday9]

I'm not very good with expressions, but I think that it has to do with the way that you're using the Convert function. If you go to this page and scroll about half way down you will find the various functions. The very first function is the Convert function and here are the arguments that it accepts:

expression -- The expression to convert.
type -- The .NET Framework type to which the value will be converted.

So in your example that you gave:

Convert(PARTNO, 'System.String')

It is trying to convert PARTNO to System.String, but PARTNO is not an expression it is a column. My suggestion would be to remove the Convert function all together and see if it works.

[M4cJunk13]

 DV.RowFilter = String.Format("PARTNO Like '%{0}%' or DESCRIPT Like '%{0}%' or ONHAND Like '%{0}%' ", TextBox1.Text)

No, unfortunately I'm still getting that fancy error.

[M4cJunk13]

I had went back in and changed the column names and still the first column can not be found.

            Me.DataGridView1.DataSource = ds.Tables(0)
            DataGridView1.Columns(0).HeaderText = "PN"
            DataGridView1.Columns(1).HeaderText = "DS"
            DataGridView1.Columns(2).HeaderText = "OH"
            DataGridView1.Refresh()

If anyone can help...?

[TnTinMN]

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        Dim dbDataSet As New DataTable
        Dim DV As New DataView(dbDataSet)
        DV.RowFilter = String.Format("Convert(PARTNO, 'System.String') Like '%{0}%' or DESCRIPT Like '%{0}%' or Convert(ONHAND, 'System.String') Like '%{0}%' ", TextBox1.Text)
        DataGridView1.DataSource = DV
    End Sub

Of course it can not find the field 'PARTNO' or any other field for that matter as there are no fields in the DataTable 'dbDataSet' (strange name also).

You create a new empty table and use that new table as the source for the DataView.

[M4cJunk13]

TnTinMN,

I'm not quite sure I know what you mean. I'm sorry I had gotten some help from some people with this and I am fairly new to VB.NET and I'm coming from VBA environment. Could you show me an example please?

Thank you!

[TnTinMN]

You have two methods. The 'Button1_Click' method queries the DB creating a DataSet that has on DataTable stored in it. When that method completes, the only access you have to the DataTable is going to be through the DataGridView1.DataSource property. This property is preserving the data you pulled because the DataGridView is declared at the Class level; this is the same concept of variable scope that exists in VBA.

I did not check your filter logic, but this is how I would write the TextBox1_TextChanged method given your other code.

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
   Dim dt As DataTable = TryCast(DataGridView1.DataSource, DataTable)
   If dt IsNot Nothing Then
      If String.IsNullOrWhiteSpace(TextBox1.Text) Then
         dt.DefaultView.RowFilter = Nothing
      Else
         dt.DefaultView.RowFilter = String.Format("Convert(PARTNO, 'System.String') Like '%{0}%' or DESCRIPT Like '%{0}%' or Convert(ONHAND, 'System.String') Like '%{0}%' ", TextBox1.Text)
      End If
   End If
End Sub

[M4cJunk13]

TnTinMN,

Ok, looking at your code I see where I am making my mistake. Thank you for the example, it is definitely what I was trying to do. I'm still trying to wrap my head around some of these concepts but your snippet is clearing some of the confusion. Tank you again!