variable value to store procedure parameter

Question

Hello I'm having an issue passing the value to SP parameter, inline query is working fine. My task is to select multiple items from the dorpdownlist and display it in a gridview. I have two dropdown when I select the value from first dropdown then second dropdown is filled on the basis of the first dropdown from db on button click.

Here is what I have done so far,

     protected void btnGetResult_Click(object sender, EventArgs e)
    {
        string selectedListValue = hdnSelections.Value;
        if (!string.IsNullOrEmpty(selectedListValue))
        {
            string[] finalList = selectedListValue.TrimEnd(',').Split(',');

            var parameters = new string[finalList.Length];
            var cmd = new SqlCommand();
            for (int i = 0; i < finalList.Length; i++)
            {
                if (!string.IsNullOrEmpty(finalList[i]))
                {
                    parameters[i] = string.Format("@cardcode{0}", i);
                    cmd.Parameters.AddWithValue(parameters[i],finalList[i]);
                }
            }

           cmd.CommandText = string.Format("Select cardcode, FirstName from 
          nonSAPcustomer where cardcode IN ({0})", string.Join(", ", 
            parameters));
          // cmd.CommandText= string.Format("sp_nonSAPcustomer", 
            parameters);
            cmd.Connection = Connection.con;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            lstBoxcities.DataSource = dt;
            lstBoxcities.DataValueField = "cardcode";
            lstBoxcities.DataTextField = "FirstName";
            lstBoxcities.DataBind();
        }

    }

What is the code doing now? What do you want it to do instead? — mjwills, Mar 19 '18 at 11:34
@mjwills , my code is slecting the name name from the first dropdown list and on the selection of first dropdown , second dropdown is filling — Oliver james, Mar 19 '18 at 11:37
@mjwills , this code is using the inline query to fill the dropdown from the database, i want to use the SP instead of inline query — Oliver james, Mar 19 '18 at 11:46
I suggest you look at the answer here: https://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable — Onga Leo-Yoda Vellem, Mar 19 '18 at 14:58

score 0 · Answer 1 · answered Mar 19 '18 at 12:04

You can use Table-Valued parameters.

First, create a Type:

CREATE TYPE CardCodes AS TABLE ( CardCode INT);

Then, add a parameter of that type

CREATE PROCEDURE dbo.YourProc
    @Codes CardCodes READONLY 
AS
BEGIN
     SELECT *
     FROM FOO
     WHERE ID IN(SELECT Code From @Codes)
END

And then send the values using, though I think there are other recipes, I use DataTables:

var dataTable = new DataTable();
dataTable.Columns.Add("CardCode", typeof(int));

PopulateTable(dataTable);

cmd.Parameters.Add("@Codes", dataTable);

variable value to store procedure parameter

1 Answers1