Stuck: Configuring DataView RowFilter

[ABOU]

I am designing a gym system for a college project and i am having problems configuring a rowfiler on a dataview for a data grid on one of my forms.

I will try to describe the problem as best i can.

The screen image displays a form with 2 combo boxes and three groupBoxes.

I am using the first comboBox (cboCID) to control the data in the first groupBox, this will cycle through the client records according to the CID.

I also want the combo box to work on the third groupBox to filter the records that appear. I wish to do this by relating the ID in the cboCID to the id in the datagrid in the third row, showing only the records which match the CID in the comboBox.

I have been working on some code but have hit a wall as i havent delt with the rowFilter function before.


cboCID Code

Private Sub cboCID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCID.SelectedIndexChanged

        Me.TblClientBindingSource.MoveFirst()
        Dim index As Integer

        For index = 1 To cboCID.SelectedIndex
            Me.TblClientBindingSource.MoveNext()
        Next


        Dim DV_CP As DataView = New DataView(DsGT2000New.tblClientProgramme)


       [B] If cboCID.SelectedIndex = "-1" Then
            DV_CP.RowFilter = "[CID] = like '*'"
        Else

            DV_CP.RowFilter = "[CID] = 'cboCID.SelectedItem'"

        End If
[/B]

        Me.DGV_CP.DataSource = DV_CP
        DGV_CP.Refresh()


    End Sub

The first part of the If statement appears to work as the Ids are loaded in to the combo box at form load. however filtering the rows has given me some trouble.

Any help would be greatly appreciated

 

[Stonkie]

DV_CP.RowFilter = "[CID] = 'cboCID.SelectedItem'"

The value cboCID.SelectedItem is isn't evaluated, it is as is within the string literal. Try this :

DV_CP.RowFilter = "[CID] = '" + cboCID.SelectedItem.ToString() + "'"

--
Btw this :

cboCID.SelectedIndex = "-1"

Compares a number to a String so it actually converts the SelectedIndex which is a number into a String. Go into your regional settings and set the negative symbol to something else than "-" and this code will probably crash. Use this instead :

cboCID.SelectedIndex < 0

--
The BindingSource class has a Position property so this :

Me.TblClientBindingSource.MoveFirst()
Dim index As Integer

For index = 1 To cboCID.SelectedIndex
    Me.TblClientBindingSource.MoveNext()
Next

Could be simplified to this :

Me.TblClientBindingSource.Position = cboCID.SelectedIndex

I could be making decent money doing other people's homeworks!

 

[cjard]

Any help would be greatly appreciated

Have a read of the DW2 link in my signature, section Displaying Related Data

You can get VB.NET to set rowfliters for you, you dont have to do it manually

 

[ABOU]

Thanks for the help!

I removed the If statement as it was unnecessary.
That bindingsource.position code was fantastic, it cut the code down by like 4 lines!

That guide made the rowfilter format make sense (i was a bit confussed on how it worked) kinda want to get a feel for how it works manually but im still having issues, played about a bit with it this morning.

DV_CP.RowFilter = "[CID] like 'C001' "

Will display all the records with a CID of C001

DV_CP.RowFilter = "[CID] = '" + cboCID.SelectedItem.ToString() + "'"

Gives a blank dataset, so i tried dragging a textbox CID in from the details view of tblClient (txtCID) to see if that value would change the datagrid correctly.

DV_CP.RowFilter = "[CID] like 'txtCID.txt'"

but to no avail.

 

[Stonkie]

My guess is that "SelectedItem" is the wrong property, use "SelectedValue" instead. You might have to set the ValueMember property to the DataSource's column you need to use as value if you didn't already (use DisplayMember to show another one while keeping the value binding to something that is not show to the user).

The RowFilter property works like an SQL "WHERE" clause in many aspects. You just tell it "ColumnName = Value" and it filters to the rows with this condition right. But in "[CID] like 'txtCID.txt'", you are telling the component to filter to those with a CID equal to "txtCID.txt". What you really want is to filter according to CID equal to the value of the txtCID.Text property.

For example,

dim first as Boolean = ("C001" = "txtCID.Text") 'This is false
dim second as Boolean = ("C001" = txtCID.Text) 'This will be true if the content is the TextBox is "C001".

PS. I think the "=" operator is overloaded to compare the actual strings' contents, but I'd rather use the Equals method and not take any chance.

 

[ABOU]

Im not sure :S

It cant be this complicated.. i want the row filter to match the value of the combo box and therefore filter the CID colomn according to the combo box, i put the text box in cause i thought it might be a problem with the combo box.

But in "[CID] like 'txtCID.txt'", you are telling the component to filter to those with a CID equal to "txtCID.txt". What you really want is to filter according to CID equal to the value of the txtCID.Text property.

sorry for being a complete noob but is this not the same thing just said differently?

This is the code i am using to load the CIDs in to the combo box

'loading the CID combo box with data from the tblClient Field
        cboCID.DataSource = DsGT2000New.Tables("tblClient")
        cboCID.DisplayMember = "CID"
        cboCID.ValueMember = "CID"

When i look at the three lines of code that dont work, they all appear to be trying to do the same thing.

DV_CP.RowFilter = "[CID] like 'C002' "

Ths one works becaue the CID to filter to is specified outright, using a textbox or combo box with a value that can change does not. i cant see how that is?

And again sorry for the noobish response, i know it must be frustrating, programming is deferentially not my strong point, im just trying to understand what im picking up instead of ripping code straight off the web.

 

[Stonkie]

I think you are simply misunderstanding what a String is... Try this code and see the output :

dim str1 as String = "something"
dim str1Name as String = "str1" ' This has value "str1"
dim str1Value as String = str1 ' This has value "something"
dim str1ValueWithPrefix as String = "Value : " & str1 'This has value "Value : something"
dim str1NameWithPrefix as String = "Name: str1" 'This has value "Name : str1"
MessageBox.Show(str1ValueWithPrefix)

What you are doing is like similar to str1NameWithPrefix. What you want is something like str1ValueWithPrefix so you must reference to the variable without the " characters.

Btw, this is called concatenation, try googling for it

 

[ABOU]

oh my, what a mistake to make :S

the whole time its not working cause im comparing [CID] to "txtCID.txt"

in the compare statement though the format is

DV_CP.RowFilter = "[CID] = [B]something[/B]"

Whatever goes in there will be read as the name and not its value, even assigning a string the value of the text box does not work.

this may seem trivial but ive been sitting for 2 hours and im more lost than before.

DV_CP.RowFilter = "[CID] = ' + cboCID.SelectedValue.ToString() + "'"

i dont see how this would work.


I made the following code:

Dim s As String = cboCID.SelectedValue.ToString()
        MessageBox.Show(s)

On the form load the message box displays: System.Data.DataRowView

This is displayed twice, when i use the comboBox thereafter the message box displays the ID correctly...




Solved

DV_CP.RowFilter = "[CID] like '" & txtCID.Text & "'"

got the concatenation and it worked

thanks for the help Stonkie

 

[cjard]

and you know, the way to solve this with datarelations?

create a relation between your two tables
ensure they are set up to fill with related data
drop a combo on the form, set the datasource to the parent bindingsource
drop the grid on the form, datasource set to the child bindingsource
ensure the child bindingsource datasource is set to the relation exposed by the parent

take about 2 minutes. it was in that link i pointed you to