Diether Silverious Diether Silverious - 7 months ago 59
Vb.net Question

Data Filtering in vb.net

I was trying to create a filter on the

datagridview
using
combobox
. What I wanted to do is to filter the filtered data in the
datagridview
.I have five
comboboxes
for the filtration. For example, the first
combobox
is for the Year then I still want to filter the search into Grade level,then to Section and so on. So, the user will be able to sort or filter his search from the database. So far i have my stored procedure code and tried it in the combo box. The SchoolYear, Grade ,section, Gender filtering works well.. Aside from Account filtering, There are two possible inputs Active And inactive. When i choose Active for filtering,there are some inactive records that shows, otherwise inactive filtering has no problem.

My storeProcedure code:

ALTER PROCEDURE [dbo].[uspYearGradeFilter]
-- Add the parameters for the stored procedure here

@Year Nvarchar(20)= NULL,
@Grade Nvarchar(20) = NULL,
@Section Nvarchar(20)= NUll,
@Gender Nvarchar(20)= NULL,
@Account Nvarchar(20) = NULL
AS
BEGIN

SET NOCOUNT ON;

SELECT si.StudentID,SI.Surname,SI.FirstName,SI.MiddleName,si.Gender,si.BirthDay,SI.TelNum,
Birthday,getdate() AS [Today],
Datediff(yy,BirthDay,getdate()) -
CASE
WHEN DATEADD(YY, DATEDIFF(YY,BirthDay,getdate()),BirthDay)
>GETDATE() THEN 1
ELSE 0

END AS [age]
FROM StudentInformation SI
JOIN StudentHistory SH
ON SI.StudentID = SH.StudentID


WHERE sh.SchoolYear LIKE COALESCE('%'+ @Year+'%', sh.SchoolYear)
AND sh.Levels LIKE COALESCE('%' + @Grade + '%', sh.Levels)
AND SI.Gender LIKE COALESCE('%' + @Gender + '%', si.gender)
AND SH.Section LIKE COALESCE('%' + @Section + '%', sh.Section)
AND Si.account LIKE COALESCE('%' + @Account + '%', si.account)

END


My vb.net code

Private Sub BindGrid()
Using cmd As New SqlCommand("dbo.uspYearGradeFilter", cn)

cmd.CommandType = CommandType.StoredProcedure

'Add a parameter for all comboboxes but only if a value is selected:

If cboYear.SelectedIndex >= 0 Then
Dim paramYear As New SqlParameter("@Year", SqlDbType.NVarChar, 20)
paramYear.Value = cboYear.Text
cmd.Parameters.Add(paramYear)
End If

If cboGrade.SelectedIndex >= 0 Then
Dim paramGrade As New SqlParameter("@Grade", SqlDbType.NVarChar, 20)
paramGrade.Value = cboGrade.Text
cmd.Parameters.Add(paramGrade)
End If
If cboSection.SelectedIndex >= 0 Then
Dim paramSection As New SqlParameter("@Section", SqlDbType.NVarChar, 20)
paramSection.Value = cboSection.Text
cmd.Parameters.Add(paramSection)
End If
If cboGender.SelectedIndex >= 0 Then
Dim paramGender As New SqlParameter("@Gender", SqlDbType.NVarChar, 20)
paramGender.Value = cboGender.Text
cmd.Parameters.Add(paramGender)
End If
If cboAccount.SelectedIndex >= 0 Then
Dim paramAccount As New SqlParameter("@Account", SqlDbType.NVarChar, 20)
paramAccount.Value = cboAccount.Text
cmd.Parameters.Add(paramAccount)
End If


da.SelectCommand = cmd
dt.Clear()
da.Fill(dt)
dgv1.DataSource = dt

End Using
End Sub


Can someone please help me to figure out what's wrong with my code. I just can't understand because i know the logic is almost the same. Thanks

Answer

Are you saying that the account value is set to either active or inactive? If so, inactive accounts could be picked up because you are using

AND Si.account LIKE COALESCE('%' + @Account + '%', si.account) 

You may want to try removing the leading '%' and using the following line instead:

    AND Si.account LIKE COALESCE(@Account + '%', si.account) 
Comments