How do I pass multiple listbox items into one stored procedure parameter IN Clause?

Question

0.00/5 (No votes)

See more:

I have a list box for example the selected items are GREEN, RED, BLUE and I would like to pass this 3 selected items to the stored procedure to be used in the where condition IN clause which the data table will then be used to populate my grid view.

For a single selected item, it is working fine, however for multiple selection it will not display any record. Your help in kindly appreciated. Thanks in advance:)

ASPX.CS

C#

public void Bind()
       {
               using (SqlConnection conn = new SqlConnection(dbConn))
           {
               using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
               {

                   string selectedValue = "";

                   foreach (ListItem item in ListBox1.Items)
                   {
                       if (item.Selected)
                       {
                           selectedValue += item.Text + ',';
                       }
                   }
                   selectedValue = selectedValue.Substring(0, selectedValue.Length - 1);

                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.Add("@param", SqlDbType.VarChar).Value = selectedValue;


                   conn.Open();
                   SqlDataAdapter da = new SqlDataAdapter(cmd);
                   DataSet ds= new DataSet();
                   da.Fill(ds);
                   GRIDVIEW.DataSource = ds.Tables[0];
                   GRIDVIEW.DataBind();

               }
           }

STORED PROCEDURE

SQL

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

@param varchar(512)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;SELECT * FROM TABLENAME WHERE COLUMN IN (@param);
END

Posted 17-Sep-15 1:26am

Member 11878313

Updated 17-Sep-15 1:29am

v2

Add a Solution

Comments

F-ES Sitecore 17-Sep-15 7:41am

Google "pass array stored procedure c#" and you'll find various techniques to do this.

CHill60 17-Sep-15 7:41am

You will need to pass in @param in the correct format for the IN clause (i.e. comma separated, quotes as required) and create dynamic sql within the SP.

Member 11878313 17-Sep-15 21:29pm

Hi, I have tried using your dynamic sql with my own for each statement as coded ^. it works for the 1st page but not for the 2nd page onwards ( it only displays the 1st selected list item in the 2nd page and paging is removed. Anyway to solve the error?

CHill60 18-Sep-15 13:30pm

Not sure I can help with that one. How are you doing the paging?

2 solutions

Solution 1

You can pass the selected values as comma seperated (',') and create a function to get it back on the sql side

input -> apples,oranges,bananas
output -> |Value|
          apples
          oranges
          bananas

SQL

ALTER FUNCTION [dbo].[fnSimpleSplit] 
( @InputString NVARCHAR(MAX)
, @Delimiter   NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
  WITH cte AS
  (
    SELECT CAST(NULL AS NVARCHAR(MAX)) val , @InputString + @Delimiter s , CHARINDEX(@Delimiter,@InputString) offset
  UNION ALL
    SELECT SUBSTRING(s,1,offset-1) , SUBSTRING(s,offset+1,LEN(s)) , CHARINDEX(@Delimiter,s,offset+1)-offset
    FROM cte
    WHERE offset>0
  )
  SELECT val
  FROM cte
  WHERE val IS NOT NULL
)

Posted 17-Sep-15 2:14am

vagelis1

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-09-17T03:02:00

Further to solution 1, to use that in your Stored procedure you will need to change the code (in the SP) to use the output from the function ...

SQL

ALTER PROCEDURE [dbo].[SP]

@param varchar(512)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT * FROM TABLENAME WHERE [COLUMN] IN 
           (SELECT splitdata FROM dbo.fnSplitString(@param, ','))
END

(I am using my fnSplitString function sourced from sqlservercentral[^])
Ensure that there are no spaces in the comma separated list that you pass in e.g.

EXEC SP 'Indigo,Blue'

will list both 'Indigo' and 'Blue' but

EXEC SP 'Indigo, Blue'

will only list 'Indigo'. A neater way of producing that parameter in your C# code is to include using System.Linq and

XML

List<string> sItems = listBox1.SelectedItems.Cast<string>().ToList();
string parm = string.Join(",", sItems.ToArray());

An alternative approach would be to pass in the single parameter as a properly formatted IN clause e.g. 'Blue','Violet' (Note the single quotes are intended to be included in the string). E.g. called in sql as

exec SP '''Indigo'',''Blue'''

The SP would need to dynamically generate the SQL e.g.

SQL

ALTER PROCEDURE [dbo].[SP]

@param varchar(512)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max)
    SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
    EXEC sp_executesql @sql;
END

If you wanted to quickly create that parameter in your C# you can do it like this

XML

List<string> sItems = listBox1.SelectedItems.Cast<string>().ToList();
parm = string.Format("'{0}'", 
     string.Join("','", sItems.Select(i => i.Replace("'", "''")).ToArray()));