[Solved] Filter RadGrid with value from Dropdownlist

[Thomas]

Hello,

I have a Radgrid on my page which I want to filter with a value from a dropdownlist:

Protected Sub RadGrid_Suchresultate_NeedDataSource(ByVal source As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid_Suchresultate.NeedDataSource
        '=== Radgrid "Suchresultate" ==============================================================

        Dim conn As New SqlConnection
        conn.ConnectionString = ConfigurationManager.ConnectionStrings("BwaConnectionString").ConnectionString

        Dim cmd_Suchresultate As New SqlCommand
        cmd_Suchresultate.CommandType = CommandType.Text
        cmd_Suchresultate.Connection = conn

        Dim cmd_Kategorien As New SqlCommand
        cmd_Kategorien.CommandType = CommandType.Text
        cmd_Kategorien.Connection = conn

        If Request.QueryString("Suche") <> "" Then

            cmd_Suchresultate.CommandText = "SELECT [ArtikelID], [Artikelnummer], [Bezeichnung], [Preis], [Text], [Bild], [WarengruppeID], [Einheit] FROM [Artikel]"
            cmd_Kategorien.CommandText = "SELECT DISTINCT W.Warengruppe, W.WarengruppeID FROM Artikel As A INNER JOIN Warengruppe As W ON A.WarengruppeID = W.WarengruppeID"

            '--- Querystring "Suche" in String speichern ----------------------------------------------
            Dim str_Suche As String = Request.QueryString("Suche")
            '--- Suchbegriff(e) in Array speichern ----------------------------------------------------
            Dim str_Suchbegriffe As String() = Split(str_Suche, " ")

            Dim str_Operator As String = " WHERE "

            For Each str_Suchbegriff As String In str_Suchbegriffe
                cmd_Suchresultate.CommandText &= str_Operator & " " & "([Artikelnummer] LIKE '%' + @Artikelnummer" & str_Suchbegriff & " + '%' OR [Bezeichnung] LIKE '%' + @Bezeichnung" & str_Suchbegriff & " + '%' OR [Text] LIKE '%' + @Text" & str_Suchbegriff & " + '%')"
                cmd_Kategorien.CommandText &= str_Operator & " " & "([Artikelnummer] LIKE '%' + @Artikelnummer" & str_Suchbegriff & " + '%' OR [Bezeichnung] LIKE '%' + @Bezeichnung" & str_Suchbegriff & " + '%' OR [Text] LIKE '%' + @Text" & str_Suchbegriff & " + '%')"
                str_Operator = " AND "
                '--- SqlParameter definieren --------------------------------------------------------------
                'In der For - Each Schleife wird das Array "Suchbegriffe"  durchlaufen und die einzelnen Suchbegriffe der Variablen "Suchbegriff" zugewiesen
                'An den Namen des Parameters wir der Suchbegriff angehängt, damit jeder Paramer einen anderen Namen erhält (sonst Fehler, Parameternamen müssen eindeutig sein)
                With cmd_Suchresultate.Parameters
                    .Add(New SqlParameter("@Artikelnummer" & str_Suchbegriff, str_Suchbegriff))
                    .Add(New SqlParameter("@Bezeichnung" & str_Suchbegriff, str_Suchbegriff))
                    .Add(New SqlParameter("@Text" & str_Suchbegriff, str_Suchbegriff))
                End With
                With cmd_Kategorien.Parameters
                    .Add(New SqlParameter("@Artikelnummer" & str_Suchbegriff, str_Suchbegriff))
                    .Add(New SqlParameter("@Bezeichnung" & str_Suchbegriff, str_Suchbegriff))
                    .Add(New SqlParameter("@Text" & str_Suchbegriff, str_Suchbegriff))
                End With
                '--- SqlParameter definieren E N D E ------------------------------------------------------
            Next

            Page.Title = "Ihre Suchergebnisse für den Suchbegriff " & str_Suche 'TODO: Title besser machen

        ElseIf Request.QueryString("Kategorie") <> "" Then
            '--- Querystring "Kategorie" in String speichern ------------------------------------------
            Dim str_Kategorie As String = Request.QueryString("Kategorie")
            cmd_Suchresultate.CommandText = "SELECT [ArtikelID], [Artikelnummer], [Bezeichnung], [Preis], [Text], [Bild], [WarengruppeID], [Einheit] FROM [Artikel] WHERE WarengruppeID = @WarengruppeID"

            '--- SqlParameter definieren --------------------------------------------------------------
            With cmd_Suchresultate.Parameters
                .Add(New SqlParameter("@WarengruppeID", str_Kategorie))
            End With
            '--- SqlParameter definieren E N D E ------------------------------------------------------

            Page.Title = "Ihre gewünschte Kategorie " & str_Kategorie

        Else
            'Seite direkt aufgerufen
            Response.Redirect("~/default.aspx")

        End If

        Dim dt_Suchresultate As New DataTable

        Using conn
            conn.Open()

            Dim dr_Suchresultate As SqlDataReader = cmd_Suchresultate.ExecuteReader()
            dt_Suchresultate.Load(dr_Suchresultate)
            RadGrid_Suchresultate.DataSource = dt_Suchresultate

            Dim dr_Kategorien As SqlDataReader = cmd_Kategorien.ExecuteReader()
            drp_Kategorien.DataSource = dr_Kategorien
            drp_Kategorien.DataValueField = "WarengruppeID"
            drp_Kategorien.DataTextField = "Warengruppe"
            drp_Kategorien.DataBind()
        End Using

        '=== Radgrid "Suchresultate" E N D E ======================================================

    End Sub

What is the best way to achieve that?

Thanks in adance for any help

Best regards

Tom

[Thomas]

I found a very good example in the Telerik help files, so I was able to sort this very easily.

Only one question left: How can I reset the filter? I thought maybe running Databind() again against the Grid, but that didn't do the trick. Any ideas?

Thanks

Tom

[Mira]

Hello Thomas,

To reset all filters of a grid, you can clear the FilterExpression string and rebind the grid:

RadGrid1.MasterTableView.FilterExpression = "";
RadGrid1.Rebind();

You can see how it works in this demo.

Kind regards,
Mira
the Telerik team

[Thomas]

Hi Mira,

I tried that one before.
But if I do that, the Grid is empty, no records are showing up

Tom

[Mira]

Hello Thomas,

Based on the supplied information, it is hard to determine what is causing this behavior.
Please open a formal support ticket and sent us a small working project, demonstrating your full setup and showing the issue.
We will debug it locally and get back to you.

Thank you in advance for the cooperation.

Regards,
Mira
the Telerik team