CODEWITHSUNDEEP
sql-servervb.net
Diether Silverious
Diether Silverious

Reputation: 199

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 stored procedure code:

ALTER PROCEDURE [dbo].[uspYearGradeFilter]
    @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 siGender 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

Upvotes: 0

Views: 160

Answers (1)

heapunderflow
heapunderflow

Reputation: 69

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)

Upvotes: 1

Related Questions