Filtering items for a list box

[John Wildes]

Hello

I have a small program that I've created to generate accounting
statements out of our policy managment system.

The first part of the process is selecting the customer to create the
statement for. In this process the application queries the database,
returns a dataset of just customer names, and their customer code that
is displayed in a list. This results in something like 200 names.

The statements are sent out in billing cycles so not all 200 names are
processed everytime the application is run. There are 4 billing cylces.

The users of this little app have requested that I allow them to filter
based on the customer billing cycle to display only those customers in
the customer list window. This customer list window is a listbox BTW.

I have included some code below, I am not sure why it is not working.
Basically I have a series of case statements that try to apply the
DataView.RowFilter on the Customers table in my dataset before the
listbox is populated with items. This I thought should allow me to
query the database once, get the entire list of names, and then only
show those whos value in the USERFIELD1 was equal to the billing cycle
code either M1,2,3,or 4. This unfortunately doesn't work, as all the
customers are still showing in my list box even after I have changed the
billing cycle code from my drop down box.

Hope this wasn't too long, if anyone has any suggestions for me please
share. I am new to VB.net Programming, and have built this application
from scratch with the help of some more senior developers.
Unfortunately they are no longer available to help me.

Thanks again...
Code samples below..
Private Sub cboBillingCycle_SelectedIndexChanged(ByVal sender As
Object, ByVal e As System.EventArgs) Handles
cboBillingCycle.SelectedIndexChanged

'converts selected item to string
billingCycle = CType(cboBillingCycle.SelectedItem, String)

'displays appropriate status
Select Case billingCycle
Case "ALL"
sbPanelBillingCycle.Text = "Displaying all clients"
Case "M1"
sbPanelBillingCycle.Text = "Displaying M1 cycle"
Case "M2"
sbPanelBillingCycle.Text = "Displaying M2 cycle"
Case "M3"
sbPanelBillingCycle.Text = "Displaying M3 cycle"
Case "M4"
sbPanelBillingCycle.Text = "Displaying M4 cycle"

End Select
'runs customer list load where billingCycle is used in case
statements to cause certain billing
'cycles to be filtered and displayed.
LoadCustomerList()

End Sub
Private Sub LoadCustomerList()
Dim listItem As ListViewItem
Dim custDataRow As CustomersRow
Dim custPropArray() As String

Select Case billingCycle
Case "All"
dsStatementData.Customers.DefaultView.RowFilter =
"UserField1 = '*' "
Case "M1"
dsStatementData.Customers.DefaultView.RowFilter =
"UserField1 = 'M1' "
Case "M2"
dsStatementData.Customers.DefaultView.RowFilter =
"UserField1 = 'M2' "
Case "M3"
dsStatementData.Customers.DefaultView.RowFilter =
"UserField1 = 'M3' "
Case "M4"
dsStatementData.Customers.DefaultView.RowFilter =
"UserField1 = 'M4' "
End Select

'clear customer list before we reload
lstCustomers.Items.Clear()

Try
For Each custDataRow In dsStatementData.Customers.Rows
custPropArray = New String()
{custDataRow("CustomerID"), custDataRow("Name")}
listItem = New ListViewItem(custPropArray)
lstCustomers.Items.Add(listItem)
Next

If lstCustomers.Items.Count > 0 Then
lstCustomers.Items(0).Selected = True
End If
lstCustomers.Select()
Catch ex As Exception
MessageBox.Show("An error occured while getting customer
list:" & vbCrLf & GetExceptionMessage(ex))
End Try
End Sub

 

[Jay B. Harlow [MVP - Outlook]]

John,

dsStatementData.Customers.DefaultView.RowFilter =
"UserField1 = '*' "

For Each custDataRow In dsStatementData.Customers.Rows
custPropArray = New String()
{custDataRow("CustomerID"), custDataRow("Name")}
listItem = New ListViewItem(custPropArray)
lstCustomers.Items.Add(listItem)
Next

You set the DataView.RowFilter, however you then use the DataTable itself.
You need to use the DataView for your binding.

Something like:

For Each custDataRow In dsStatementData.Customers.DefaultView

Hope this helps
Jay

 

[John Wildes]

I hate to be a pest. I see the logic behind it, and I have tried that,
but I get a "Specified cast is invalid" when it processes that line
after the cboBillingCycle_SelectedIndexChanged event is fired.

any other ideas, or syntax?

john

 

[Jay B. Harlow [MVP - Outlook]]

John,
Doh! I meant to add that when you use a For Each over a DataView you are
have DataRowView objects not DataRow objects.

Change the type that custDataRow is.

Hope this helps
Jay

 

[John Wildes]

Jay,

Thanks for the help, it works great.

john