locked
Add multiple parameter to Sql Command RRS feed

  • Question

  • User-1305858305 posted

    Hello

    Trying add multiple parameter to my sql command. 

                    cmd.Parameters.Add("@LotIds", SqlDbType.NVarChar, 4);
                    //Determine which checkboxes have been checked
                    List<string> selectedLotIDs = new List<string>();
                    {
    
                        if (CheckBoxColorlessLot.Checked)
                        {
                            selectedLotIDs.Add("0");
                        }
    
                        if (CheckBoxColoredLot.Checked)
                        {
                            selectedLotIDs.Add("1");
                        }
                    }
    
                    //Convert the List of DepartmentIDs into a comma-delimited string
                    string selectedLotIDsAsCommaDelimitedList = string.Join(",", selectedLotIDs.ToArray());
    
                    //Assign departmentIDsAsCommaDelimitedList to the @DepartmentIDs parameter
                    cmd.Parameters["@LotIDs"].Value = selectedLotIDsAsCommaDelimitedList;

    But getting error "Failed to convert parameter value from a String to a Int32." 

    How can i pass multiple parameter to my sql command?

    Saturday, March 22, 2014 6:10 AM

Answers

  • User281315223 posted

    You may have to actually wrap each of your existing values within your List using single quotes prior to combining them into your SQL statement as well through the String.Join() method

    // Example Query (notice the parameter wrapped in parentheses)
    SqlCommand myCommand = new SqlCommand("SELECT * FROM table WHERE myID IN (@Values)",myConnection);
    
    // Example List
    List<int> yourValues = new List<int>(){ 1, 2, 3, 4, 5 };
    
    // Combine your Values wrapping each value in single quotes
    string wrappedValues = String.Join(",", yourValues.Select(s => String.Format("'{0}'", s)).ToArray())
    
    // Now add your parameter using the AddWithValue() method
    myCommand.Parameters.AddWithValue("@Values",wrappedValues);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 22, 2014 8:33 AM

All replies

  • User-821857111 posted

    Which line of code throws the error?

    Saturday, March 22, 2014 6:33 AM
  • User-1305858305 posted

    Thank you for reply. 

    Trying pass parameters to stored procedure.

    Throwing error in this line. 

    something like this "failed convert string to int. 

    after this tried convert to Int32 like this but no luck..

     cmd.Parameters["@LotIDs"].Value =ConvertToInt32(selectedLotIDsAsCommaDelimitedList);
      cmd.Parameters["@LotIDs"].Value = selectedLotIDsAsCommaDelimitedList; //throwing
    Saturday, March 22, 2014 8:14 AM
  • User281315223 posted

    You may have to actually wrap each of your existing values within your List using single quotes prior to combining them into your SQL statement as well through the String.Join() method

    // Example Query (notice the parameter wrapped in parentheses)
    SqlCommand myCommand = new SqlCommand("SELECT * FROM table WHERE myID IN (@Values)",myConnection);
    
    // Example List
    List<int> yourValues = new List<int>(){ 1, 2, 3, 4, 5 };
    
    // Combine your Values wrapping each value in single quotes
    string wrappedValues = String.Join(",", yourValues.Select(s => String.Format("'{0}'", s)).ToArray())
    
    // Now add your parameter using the AddWithValue() method
    myCommand.Parameters.AddWithValue("@Values",wrappedValues);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 22, 2014 8:33 AM
  • User-1358137329 posted

    Please try the following in the place of the first line of your posted code and rest leave as it is

    cmd.Parameters.Add(new SqlParameter("@LotIds", string.Empty));

    Saturday, March 22, 2014 8:51 AM