Parameter '@stIdCity' was supplied multiple times asp.net c#-CodePudding

I am trying to send values to a stored procedure via a listbox containing country names I get them from the database. If I choose one option code works 100% 100%. But if I put 2 or 3 options, I get the following error:

Parameter '@stIdCity' was supplied multiple times.

Line 322: da.Fill(ds);

Full code:

protected void lstBoxTestCity_SelectedIndexChanged(object sender, EventArgs e)
{
    string str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    using (SqlConnection con = new SqlConnection(str))
    {
        using (SqlCommand cmd = new SqlCommand("Tprocedure", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (ListItem item in lstBoxTestCity.Items)
            {
                if (item.Selected)
                {
                   cmd.Parameters.AddWithValue("@stIdCity", item.Value);
                }
            }
                
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            gvProducts.DataSource = ds;
            gvProducts.DataBind();
        }
    }
}

Stored procedure:

CREATE PROCEDURE Tprocedure
    (@stIdCity NVARCHAR(20) = NULL, )
AS
BEGIN
    SELECT * 
    FROM employees
    INNER JOIN TCity ON employees.IdstICity = TCity.IdstICity 
    WHERE (employees.IdstICity IN (SELECT ITEM 
                                   FROM dbo.SplitString(@stIdCity, ',')) 
       OR ISNULL(@stIdCity, '') = '')
END

Image of list:

Please help to solve the problem.

Sorry if the question was repeated, but I could not find a solution to it.

CodePudding user response：

You have this:

foreach (ListItem item in lstBoxTestCity.Items)
{
   if (item.Selected)
   {
       cmd.Parameters.AddWithValue("@stIdCity", item.Value);
   }
}

so for each selected city, you add a new parameter. That is why you get that error message.

Apparently you want one parameter, with a comma-separated value. So build that:

var value = string.Join(",",lstBoxTestCity.Items.Where(it => it.IsSelected).Select(it => it.Value));

cmd.Parameters.Add("@stIdCity", SqlDbType.NVarChar, 20).Value = value;

First I use LINQ to filter for selected items, then I get their value. string.Join combines the resulting values with commas.

But do note that your stored procedure accepts just a nvarchar(20), so you may run out of space when multiple cities have been selected.

CodePudding user response：

You ONLY have the ONE parmater, and it is a comma delimited string of values.

So, you have to "build up" a comma delimited string. Say like this:

        string myparms = "";
        foreach (ListItem item in lstBoxTestCity.Items)
        {
            if (item.Selected)
            {
                if (myparms != "")
                    myparms  = ",";

                myparms  = item.Value;

            }
        }
        cmd.Parameters.Add("@stIdCity",SqlDbType.NVarChar).Value = myparms;

So, you only passing one value - but you have to build up the "string" of values to pass.