Filtering information from a DataGrid [RESOLVED]

[ettropics]

I am new to vb.net programming and need some assistance ...
this has been a struggle for a couple of weeks, I have tried, dataviews, etc. with no luck.

I have a Windows Form that has a datagrid that is filled on load with a dataset

The users would like to be able to sort and filter this information

I have incorporated textboxes for the filtering, I would like to be able to have them type in a "Name" or "ID", etc. and have the dataset sort and return only those records back into the datagrid

Any help in the right direction would be most appreciated.

SQL 2000 database (backend)
vb.net (frontend)

Here is the code that I am using for filtering:

#Region "Requery Data"

Private Sub requeryData()
Dim Filter$, SQL$
Dim mygr As DataGrid
Dim myrs As dsStudentInfo

Filter$ = " WHERE 1 "
If IsDBNull((Me.txtFilterID)) Then
Filter$ = Filter$ & " AND ([studentID] =' & Me.txtFilterID & ')"
End If

If IsDBNull((Me.txtFilterLast)) Then
Filter$ = Filter$ & " AND ([studentLast] Like ' & Me.txtFilterLast & *')"
End If
Filter$ = Filter$ & " AND ([studentFirst] Like ' & Me.txtFilterFirst & *')"

If IsDBNull((Me.txtFilterFirst)) Then
Filter$ = Filter$ & " AND ([studentFirst] Like ' & Me.txtFilterFirst & *')"
End If

If IsDBNull((Me.txtFilterMiddle)) Then
Filter$ = Filter$ & " AND ([studentMI] Like ' & Me.txtFilterMiddle & *')"
End If

If IsDBNull((Me.txtFilterSSN)) Then
Filter$ = Filter$ & " AND ([studentSSN] Like ' & Me.txtFilterSSN & *')"
End If

If IsDBNull((Me.txtFilterGrade)) Then
Filter$ = Filter$ & " AND ([gradeCode] = ' & Me.txtFilterGrade & ')"
End If
If IsDBNull((Me.txtFilterBirthdate)) Then
Select Case Me.FormBorderStyle
Case 1 'Less Than
Filter$ = Filter$ & " AND ([studentBirthDate] < #' & Me.txtFilterBirthdate & '#)"
Case 3 'Greater Than
Filter$ = Filter$ & " AND ([studentBirthDate] > #' & Me.txtFilterBirthdate & '#)"
Case Else 'Equals
Filter$ = Filter$ & " AND ([studentBirthDate] = #' & Me.txtFilterBirthdate & '#)"
End Select
End If

SQL$ = "SELECT * FROM dvfilterStudentID " & Filter$ & ";"
mygr = Me.DataGrid1
myrs = dsStudentInfo2
End Sub
#End Region

From the textbox_Enter
requeryData()

Thank you from a very new and frustruated vb.net programmer
learning and doing at the same time...

ettropics

[Lunatic3]

I do filtering on datagrid by binding it to a dataview and then do filtering on the dataview, it works great.

if DV is your dataview then filtering will be DV.Rowfilter="filtering statment goes here".

[ettropics]

Thank you Lunatic3, you gave me a little omph to keep at it...

I've got it working better, another bump in the road that I need some help with...

I would like to use any text box for filtering, if not one then another ... I can get one to work just fine, however, I'm missing something, can anyone help out, see "Private Sub setupGrid"

Private Sub requeryData()
Dim tStudentInfoTable As DataTable

SqlDataAdapter1.Fill(DataSet12, "tblStudentInfo")

tStudentInfoTable = DataSet12.Tables("tblStudentInfo")

txtLast.DataBindings.Add("Text", tStudentInfoTable, "StudentLast")
txtFirst.DataBindings.Add("Text", tStudentInfoTable, "StudentFirst")

dvDataView = New DataView(tStudentInfoTable)
End Sub

***---------------------------------------------------------------***
Private Sub setUpGrid()
If Me.txtFirst.Focus Then
dvDataView.RowFilter = "StudentFirst = '" & _
txtFirst.Text & "'"
dgDataGrid.DataSource = dvDataView
Else
If Me.txtLast.Focus Then
dvDataView.RowFilter = "StudentLast = '" & _
txtLast.Text & "'"
dgDataGrid.DataSource = dvDataView
End If
End If
***--------------------------------------------------------------***

Thank you for any help and for inspiration to continue on.

[Lunatic3]

I' am not sure if its your problem, but see if it helps.
If you you filter on "First Name" then going to another like "Last Name" will do filtering on the previusly filtered rows. So you may restore the dataview to its original rows by setting its RowFilter to "" before changing your filter criteria.

dvDataView.Rowfilter=""

[ettropics]

Thank you for your insight. I got it to work. Whew! Conquered that hurdle, on to the next...