Control.Bindings.Add problem, exception telling me that the program cannot bind to a column that does not exist (but it does)

Question

When I try to use the following line of code:

cboSite.DataBindings.Add("Text", _dtSite.Select("Site <> 'ALL'"), "Site")

I get the following exception:

EXCEPTION : Cannot bind to the property or column Site on the DataSource.{10}Parameter name: dataMember

details I am connecting to an Access database and using .net 3.5 and writing the code in VB. In this database I have a table named Sites with a column named Site and when I try to use the line of code above I get the exception noted. I was under the impression that I could explicitly name the column that I need to use in my control (combo box). What am I doing wrong?

Answer 1

Its because the result produced from the .Select() statement, produces an output of a collection of DataRows, when what you need is to bind to a DataTable source that has been filtered. What you can do is filter the rows 1st, place the filtered rows into a clone of the original DataTable and then bind to the filtered DataTable. This code sample is below:

    'Begin by returning an array of DataRows that have been filtered
    Dim filterdRows() As DataRow = _dtSite.Select("Site <> 'ALL'")

    'Clone the original DataTable which will give us an empty one
    'with an identical structure.
    Dim _dtFiltered As DataTable = _dtSite.Clone
    For Each drFiltered As DataRow In filterdRows
        'Import the rwo into the filtered table
        _dtFiltered.ImportRow(drFiltered)
    Next

    'Bind the ComboBox to the filtered DataTable
    cboSite.DataBindings.Add("Text", _dtFiltered, "Site")

Hope this helps!