Unable to filter Dataset

vbNewbie2009
vbNewbie2009 used Ask the Experts™
on
I'm having a difficult time grasping how I can filter data in a datatable using the Select method and then populate a combobox.  I've created a Private Dataset for my form that I populate on form Load.  Then when my routine runs to fill a combobox based off that I want to perform a Select against the table in the dataset but keep getting the error below.  I've attached the code as I have it...any help would be most appreciated.


Problem responseChkBoxEvents: --Message:Cannot bind to the new display member.  Parameter name: newDisplayMember--StackTrace:   at System.Windows.Forms.ListControl.SetDataConnection(Object newDataSource, BindingMemberInfo newDisplayMember, Boolean force)     at System.Windows.Forms.ListControl.set_ValueMember(String value)     at RxAssess.DynamicForm.responseChkBoxEvents(Object sender, EventArgs e) in C:\Programming\DynamicForm.vb:line 629--TargetSite:Void SetDataConnection(System.Object, System.Windows.Forms.BindingMemberInfo, Boolean)
Public Class DynamicForm
Private dsNCICriteria As New DataSet

Private Sub DynamicForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Capture data for NCI Criteria Combo Boxes
Dim conStringNCICriteria As String = "removed string;"
Dim strSQLNCICriteria As String = "select ntypeid,grade,cast(grade as nvarchar) + '   -   ' + description as description " + _
                "from nci_criteria"
Dim conNCICriteria As New SqlConnection(conStringNCICriteria)
Dim daNCICriteria As New SqlDataAdapter(strSQLNCICriteria, conNCICriteria)
daNCICriteria.Fill(dsNCICriteria, "NCICriteria")
daNCICriteria.Dispose()
conNCICriteria.Close()
conNCICriteria = Nothing
strSQLNCICriteria = Nothing
conStringNCICriteria = Nothing
End Sub

Sub responseChkBoxEvents(ByVal sender As System.Object, ByVal e As System.EventArgs)
If chk.Checked = True Then
                If chk.Name.Substring(chk.Name.LastIndexOf("N") + 1) <> 0 Then
                    Dim nciTypeID As String = chk.Name.Substring(chk.Name.LastIndexOf("N") + 1)
                    Dim nciFilter As String
                    nciFilter = "ntypeid = " + chk.Name.Substring(chk.Name.LastIndexOf("N") + 1)


                    Dim cbo As ComboBox = TryCast(Me.Controls(Replace(Replace(chk.Name, "CHK", "NCICBO"), "L" + z.ToString, "L" + (z + 1).ToString)), ComboBox)
                                      With cbo
                        .DataSource = dsNCICriteria.Tables("NCICriteria").Select(nciFilter)
                        .DisplayMember = "description"
                        .ValueMember = "grade"
                        .Visible = True
                        .SelectedItem = Nothing
                        .AutoCompleteSource = System.Windows.Forms.AutoCompleteSource.ListItems
                        .AutoCompleteMode = AutoCompleteMode.Append
                    End With
                End If
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try replacing

.DataSource = dsNCICriteria.Tables("NCICriteria").Select(nciFilter)

with

.dsNCICriteria.Tables("NCICriteria").DefaultView.RowFilter = nciFilter
.DataSource = dsNCICriteria.Tables("NCICriteria").DefaultView
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
can you print out the value of nciFilter?

Author

Commented:
Thanks CodeCruiser I will give that a try an about an hour.

Emoreau:  The value of nciFIlter is "ntypeid = 5"
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
CodeCruiser:  That works, i just had to put the first row outside the With clause and remove the dot.   Was that your intent?    I'm noticing that other checkBox events associated with populating comboBoxes all fire properly too.  The comboBoxes all share the same drop down values though.  Can the filter results be assigned to each comboBox individually or is this just a limitation?

Based on your suggestion I think I've misunderstood the whole Table Select method; I thought if I select just a certain number of items from a table and associated that with the combobox those values would be; somewhat added to just that combobox.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Select can be used but it returns an array of DataRows and I think that can not be used as a datasource on its own.

>Was that your intent?  
Yeah I think I made mistake there.

>The comboBoxes all share the same drop down values though.

Changing this
.DataSource = dsNCICriteria.Tables("NCICriteria").DefaultView.ToTable()

would make the filter specific to this combobox. You can also do

Dim dCombo1 As DataTable = dsNCICriteria.Tables("NCICriteria").DefaultView.ToTable()
.DataSource = dCombo1

This will be useful if you need access to this datasource later on.

Author

Commented:
Extremely helpful; thank you very much for the help and explanation!!!
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial