How to bind multiple values at a time in cmd.parameters.addwithvalue() in asp.net

Question

Hi All,

I am developing asp.net application. I have 3 values for the same key in web.config eg: add key="ids" value="12333,43434343,434232".

In aspx.cs iam calling like this.

string merchantIds = ConfigurationManager.AppSettings["ids"];
string paramName = null;
foreach (string ids in merchantIds.Split(','))
{
     paramName = ids;           
}
com.Parameters.AddWithValue("@FBUserID", paramName);

I am only getting the last id output. I want to display outputs related to 3 ids here.

Please help me regarding this..

Thanks in advance, Ibrahim.

Answer 1

Parameters are singular, you can't have a parameter present multiple values. You need to either modify your proc to handle comma-delimited ids and do the work in the proc instead, or for each of the Ids, you run the procedure for each one...

EDIT The simplest way is doing this:

using (var conn = new SqlConnection(...)) 
{
  using (var command = new SqlCommand(..., conn))
  {
    command.CommandType = CommandType.StoredProcedure;

    // add other parameters here.

    var param = command.Parameters.Add("@FBUserID", SqlDbType.VarChar, 10);
    foreach (string merchant in ConfigurationManager.AppSettings["ids"].Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
    {
      param.Value = merchant;
      command.ExecuteNonQuery();
    }
  }
}

Ideally you should be considering whether handling your merchant Ids like this is really the best design.

Oh, and if you find the right answer with those that have been given, please make sure you accept it as such, otherwise all us StackOverflowers are really giving you answers for nothing....

Answer 2

Your foreach loop is writing the all the values to the same variable, so only the last one will be used. Like Matthew said, you have to edit the proc to take your comma delimited string, or run the proc 3 times

Answer 3

If you're using SQL Server 2008, you can pass in multiple values to a sproc to join on using Table Valued Parameters.

The alternatives are to pass in CSV as Matthew suggested, or supply an XML blob of values in. I've compared the 3 approaches here: http://www.adathedev.co.uk/2010/02/sql-server-2008-table-valued-parameters.html

In my tests on there, Table Valued Parameters performed best, followed by the XML approach, and then the CSV approach.