Filter Gridview from MultiSelect Listbox 1

[MrMode]

I am coding in VB

I have a listbox with multiselect enabled.

      <asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Requirements_Heading" 
            DataValueField="ID" AutoPostBack="true" SelectionMode="Multiple" OnSelectedIndexChanged="ListBox1_SelectedIndexChanged" Rows="8"></asp:ListBox>
        <asp:SqlDataSource runat="server" ID="SqlDataSource1"

I am able to verify I have captured multiple values by displaying them in a label- the code behind is fired OnSelectedIndexChanged event in Listbox:

 Dim stringBuilder = New StringBuilder()
        Dim delimiter = ", "
        For Each item As ListItem In ListBox1.Items
            If item.Selected Then
                stringBuilder.AppendFormat("{0}{1}", item, delimiter)
            End If
Next
        Label1.Text = "These are the requirements you have selected: " & stringBuilder.ToString()

What I want to do is use the values from the Listbox in the WHERE statement (I am sure I will be able to use IN but don't know how to deliver the values) to filter a gridview. At the moment, it changes to give me only one of the selections from the listbox, not all of them:

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource2">
            <Columns>
                <asp:BoundField DataField="Company Name" HeaderText="Company Name" SortExpression="Company Name"></asp:BoundField>
                <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" InsertVisible="False" SortExpression="ID"></asp:BoundField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource runat="server" ID="SqlDataSource2" 
            ConnectionString='<%$ ConnectionStrings:HSCConvertedConnectionString %>' 
            SelectCommand="SELECT tblCompany.CompanyName, tblSupplierMapCodes.ID FROM ... WHERE (tblSupplierMapCodes.ID = @ID)">
            <SelectParameters>
                <asp:ControlParameter ControlID="ListBox1" PropertyName="SelectedValue" Name="ID"></asp:ControlParameter>
            </SelectParameters>
        </asp:SqlDataSource>

 

[MrMode]

Just in case it helps, I have tried using vb from an Access database mixed with some asp elements to illustrate what I am after...

   Protected Sub Illustration()
        Dim strSQLRowSource As String
        Dim strWhere As String
        Dim cmd As New SqlCommand

        'Create the sql statement
        strSQLRowSource = ("SELECT tblCompany.[Company Name], tblSupplierMapCodes.ID FROM ... WHERE ~") , con)

        'Insert the string into the where clause
        strWhere = " tblSupplierMapCodes.ID IN ("
        For Each item In ListBox1.Items
            strWhere = strWhere & item.value & ","
        Next
        Replace(strSQLRowSource, "~", strWhere)

        'Trim the last comma
        strWhere = Left(strWhere, Len(strWhere) - 1) & ") "

        'Execute the query
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub

 

[jbenson001]

My first suggestion would be to get rid of the SqlDataSource controls all together. They are only good for very simple querying and become cumbersome once the SQL you need to write gets even a little bit complex, like in your case. Also, you cannot debug using them.

I assume you are using a SQL Server database. If so, then if you know what stored procedures are, you should write and use them. If not, this is a good time to learn. It will be very useful in your career. You cannot depend on those datasource controls.

At the very least, generate the SQL yourself and execute it.
So you need to change this:

SELECT tblCompany.CompanyName, tblSupplierMapCodes.ID FROM ... WHERE (tblSupplierMapCodes.ID = @ID)

To something like this:

You need to use the IN clause
SELECT tblCompany.CompanyName, tblSupplierMapCodes.ID FROM ... WHERE (tblSupplierMapCodes.ID IN (1,2,5,6))

This is assuming that the ID column is numeric if not, each value would have to be placed in quotes IN ('1', '2', '6')

You already have code to get each selected value from the listbox, you can use that while generating your SQL statement.

 

[MrMode]

Fixed it!

HTML has a ListBox and Gridview:

    <div>
            <asp:ListBox ID="ListBox1" runat="server" AutoPostBack="true"
                DataSourceID="SqlDataSource1" DataTextField="Requirements_Heading" DataValueField="ID" SelectionMode="Multiple"
                Rows="8" OnSelectedIndexChanged="ListBox1_SelectedIndexChanged"></asp:ListBox>
            <asp:SqlDataSource runat="server" ID="SqlDataSource1"
                ConnectionString='<%$ ConnectionStrings:... %>'
                SelectCommand="SELECT ID, [Requirements Heading] AS Requirements_Heading FROM tblSupplierMapCodes"></asp:SqlDataSource>
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" DataKeyNames="ID"></asp:GridView>
        </div>

In code behind (I have coded in VB, and removed connection strings, table names etc to keep it as clean and simple to follow as possible):

Imports System.Collections.Generic
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports System.Data

Partial Class MultiSelectListBoxGridView
    Inherits System.Web.UI.Page
    Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("...").ToString())
    Private SelectedMapId As New System.Text.StringBuilder()

    Protected Sub Page_Load(sender As Object, e As EventArgs)
        ListBox1.SelectionMode = ListSelectionMode.Multiple
    End Sub

    Protected Sub LoadCompanies()
        Using connection As New SqlConnection()
            connection.ConnectionString = ConfigurationManager _
                        .ConnectionStrings("...").ConnectionString
            Using command As New SqlCommand("SELECT  FROM ", connection)
                connection.Open()
                command.CommandText += " WHERE tbl123.ID IN (" + SelectedMapId.ToString() + ")"
                GridView1.DataSource = command.ExecuteReader()
                GridView1.DataBind()
            End Using
        End Using
    End Sub

    Protected Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs)
        For Each MapID As ListItem In ListBox1.Items
            If MapID.Selected Then
                SelectedMapId.AppendFormat("{0},", MapID.Value)
                'Build Comma-separated values
            End If
        Next
        SelectedMapId.Remove(SelectedMapId.Length - 1, 1)
        'Remove last comma
        LoadCompanies()

    End Sub

End Class

Hope this helps anyone else who is looking to populate a gridview with multiple selections from a listbox.

Any and all improvements are most welcome.