ASP.NET Listbox Multiple Items passed to SQL Server then passed to Gridview

0
votes

What I'm trying to do is to select multiple countries from listbox, then hit select, then a gridview would pull out data from a table based on the countries selected. my problem is that I don't know how to pass the string where country in (A,B,C,...) into a SQL Server stored procedure. Any advice on this would be great!

My listbox is

 <asp:ListBox ID="Country" cssclass="LBox" runat="server" SelectionMode="Multiple">
             <asp:ListItem Text="A" Value="A" />
             <asp:ListItem Text="B" Value="B" />
             <asp:ListItem Text="C" Value="C"  />
             <asp:ListItem Text="D" Value="D"  />
                     ..............
        </asp:ListBox>

My select button is

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim li As ListItem
    Dim myString As String = ""
    Dim myString2 As String = ""

    For Each li In ListBoxComp.Items
        If li.Selected = True Then
            If myString = "" Then
                myString = "'" & li.Text & "'"
            Else
                myString = myString & ", " & "'" & li.Text & "'"
            End If
        End If
    Next
    myString2 = myString2
    Session("Selected") = myString2

from http://forums.asp.net/t/1618260.aspx?code+SQL+to+retrieve+records+from+multiple+random+selection+list+box

My gridview is

 <asp:GridView ID="GridView1" runat="server" 
      AutoGenerateColumns="False" BackColor="White" 
      BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" 
      CellPadding="3" DataSourceID="SqlDataSource1" ForeColor="Black" 
      GridLines="Vertical">
      ......
 </asp:GridView>

Datasource is 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
     SelectCommand="usp_select" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:SessionParameter Name="myString2" SessionField="Selected" />
    </SelectParameters>
</asp:SqlDataSource>

SQL Server stored procedure is 

ALTER PROCEDURE [dbo].[usp_select]
    @myString2 varchar(5000)
AS
BEGIN
   SELECT * 
   FROM dbo.table 
   WHERE country IN (@myString2)
END

My problem is that I don't know how to pass the string where country in (A,B,C,...) into the SQL Server stored procedure. Any advice on this would be great!

2
asp.netsql-servervb.netgridviewlistbox

2 Answers

1
votes

In the example you got your code from, you must use dynamic sql in your stored proc. Something like:

Declare @sqlstring varchar(max);

Set @sqlstring = 'Select * from dbo.table where country in (' + @myString2 + ')';

Execute sp_executesql @sqlstring;

Otherwise you're trying to use IN with just a string, which is causing your problems I think.

But honestly, I stay away from dynamic sql when possible. You might consider finding a good split string function that turns your string into a table or cte. Then you could use these rows in a join or a "where exists" clause. I like to pass stuff like this in as xml and use an xml function to create a similar cte. A final alternative is to create a user-defined table type with just rows of strings and pass this into your stored proc from a datatable on the front end, but that's probably overkill.

1
votes

You could try this:

Define an StrList data type in SQL Server:

USE [YourDB]
GO
CREATE TYPE [dbo].[StrList] AS TABLE(
    [Value] [varchar](100) NULL
)
GO

Alter your stored procedure to accept that kind of parameter instead of the string:

ALTER PROCEDURE [dbo].[usp_select]
    @countryList AS StrList READONLY
AS
BEGIN
   SELECT * 
   FROM dbo.table 
   WHERE country IN (SELECT Value FROM @countryList)
END

Pass the country names as a StrList instead of a string. I am not sure how to do it with the SessionParameter, however. In code-behind, I do it like this:

Dim tbl As new DataTable()
tbl.Columns.Add("Value", System.Type.GetType("System.String"));
For Each li In ListBoxComp.Items
    If li.Selected = True Then
        tbl.Rows.Add(li.Text)
    End If
Next
Dim prm As new SqlParameter("@countryList", SqlDbType.Structured)
prm.Direction = ParameterDirection.Input
prm.Value = tbl

I hope that this can give you some idea, and that my translation from C# to VB.NET is correct...