How to pass multiple listbox items (characters such as A,S,I,SIP,SIPA etc...)to parameterized SQL query using IN?

Question

I have found numerous posts on this but I haven't been able to make any of them work. The code below is the closest I have come to making it work. I read out the values for the ddlSIPA listbox below and the result looks correct but it seems SQL server isn't handling the IN statement for the listbox items.

public void LoadChecklist(Object sender, EventArgs e)
{       
 System.Data.DataTable SearchResultsTable = new System.Data.DataTable();
    SqlCommand cmd = new SqlCommand("sp_get_QUADRA_CHECKLIST", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    //create sql adapter by passing command object
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);

    SearchResultsTable.Clear();

    string strYourIDs = "";
    int[] yourSelectedIndexes = ddlSIPA.GetSelectedIndices();
    for (int i = yourSelectedIndexes.Length - 1; i >= 0; i--)
    {
        strYourIDs += "'" + ddlSIPA.Items[yourSelectedIndexes[i]].Value + "',";
    }
    if (strYourIDs != "")
        strYourIDs = strYourIDs.TrimEnd(",".ToCharArray());
    try
    {
        cmd.Parameters.AddWithValue("@SIPA", strYourIDs);
        Response.Write(strYourIDs);
        cmd.Parameters.AddWithValue("@AP_DEV", CbAPDev.Checked);
        cmd.Parameters.AddWithValue("@PROD_DEV", cbProdDev.Checked);
        cmd.Parameters.AddWithValue("@ROTYPE",                                  ddlROTYPE.SelectedItem.Value);
        adapter.Fill(SearchResultsTable);
        if (SearchResultsTable.Rows.Count > 0)
        {
            //SearchResultsTable.ToString();
            GV1.DataSource = SearchResultsTable;
            GV1.DataBind();
        }
        else if (SearchResultsTable.Rows.Count == 0)
        {
            //Response.Write("No records found!");
            ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('No records found!');", true);
            GV1.DataBind();
        }
    }
    catch (System.Data.SqlClient.SqlException ex)
    {
        Response.Write(ex);
    }
    finally
    {
        conn.Close();
    }
}

SQL Query:

USE [VISIBILITY_BOARD]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



ALTER PROCEDURE [dbo].[sp_get_QUADRA_CHECKLIST] (
    @AP_DEV bit ''
   ,@PROD_DEV bit = ''
   ,@ROTYPE nvarchar(255) = ''
   ,@SIPA nvarchar(255) = '') AS
 --,@RO nvarchar(255) = '') AS


SELECT h.QUES_ANSWER
, h.COMMENTS
, cl.RO_TYPE
, cl.RO
, cl.QUES_ID
, cl.DFQRO AS QDRO
, cl.QUADRA_QUES

FROM Tbl_QUADRA_CL cl
LEFT JOIN TBL_QUADRA_ASSMNT_HIST h
ON cl.QUES_ID = h.QUES_ID 
WHERE (cl.RO_TYPE = @ROTYPE OR @ROTYPE IS NULL)
AND (cl.SIPA IN (@SIPA) OR @SIPA IS NULL)
AND (cl.AP_DEV = @AP_DEV OR @AP_DEV IS NULL)
AND (cl.PROD_DEV = @PROD_DEV or @PROD_DEV IS NULL)
GROUP BY h.QUES_ANSWER
, h.COMMENTS
, cl.RO_TYPE
, cl.RO
, cl.QUES_ID
, cl.DFQRO
, cl.QUADRA_QUES

SET QUOTED_IDENTIFIER On
GO

Answer 1

Here is your problem: AND (cl.SIPA IN (@SIPA) OR @SIPA IS NULL)

You are making a very common mistake - The IN operator expects a list of values separated by a comma, but you are giving it a single value that happens to contain a comma-separated list.

Since you are using c# and sql-server, I would advise to use a table valued parameter instead.

Please note that there are also some other problems in your code:

• Using a class level SQLConnection - That's a mistake. A correct use of SQLConnection would be as a local variable inside a using statement.
• Using instances of classes that implements the IDisposable interface and not disposing them - SQLCommand and SQLDataAdapter in your case.
• Using AddWithValue - Read Can we stop using AddWithValue() already? for details.

A better c# code would look more like this:

public void LoadChecklist(Object sender, EventArgs e)
{
    var SearchResultsTable = new DataTable();
    using (var con = new SqlConnection("<ConnectionStringGoesHere>"))
    {
        using (var cmd = new SqlCommand("sp_get_QUADRA_CHECKLIST", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            using(var adapter = new SqlDataAdapter(cmd))
            {
                using(var dtSIPA = new DataTable())
                {
                    dtSIPA.Columns.Add("Id", typeof(int)); -- assuming you are looking for a list of int values
                    int[] yourSelectedIndexes = ddlSIPA.GetSelectedIndices();
                    for (int i = yourSelectedIndexes.Length - 1; i >= 0; i--)
                    {
                        dtSIPA.Rows.Add(ddlSIPA.Items[yourSelectedIndexes[i]].Value);
                    }

                    cmd.Parameters.Add("@AP_DEV", SqlDbType.Bit).Value = CbAPDev.Checked;
                    cmd.Parameters.Add("@PROD_DEV", SqlDbType.Bit).Value = cbProdDev.Checked;
                    cmd.Parameters.Add("@ROTYPE", SqlDbType.NVarChar, 255).Value = ddlROTYPE.SelectedItem.Value;
                    cmd.Parameters.Add("@SIPA", SqlDbType.Structured).Value = dtSIPA;
                }
                try
                {
                    adapter.Fill(SearchResultsTable);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    Response.Write(ex);
                }                    
            }
        }
    }
 }

As for your stored procedure, you need to create a user defined table type for the @SIPA parameter:

CREATE TYPE SIPA AS TABLE
(
    Id int
)

and change the condition to AND (cl.SIPA IN (SELECT Id FROM @SIPA) OR (SELECT COUNT(*) FROM @SIPA) = 0)