Error : The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects

Question

public int set(string procName , object[] param)
{
      SqlConnection conn = new SqlConnection(constr);
      conn.Open();

      SqlCommand cmd = new SqlCommand(procName,conn);
      cmd.CommandType = CommandType.StoredProcedure;

      foreach(object o in param)
      {
         cmd.Parameters.Add(o);      // Error
      }
      int res = cmd.ExecuteNonQuery();
      conn.Close();
      return res;
}

The values are correctly passed via the calling function... The 2nd parameter of the function 'set' contains 2 string values.

Even at the ERROR statment the 'o' contains the first value "Computer" but represent ERROR as stated above.

could you please explain what are you trying to achieve it looks like a "generic" db access is this right? — Karl_Schuhmann
– Karl_Schuhmann, Commented Apr 24, 2013 at 13:09

Habib · Accepted Answer · 2013-04-24 10:48:29Z

2

You need to add new SqlParameter in your collection like:

cmd.Parameters.Add(new SqlParameter(o.ToString(), o));

The above statement is just to show how to add parameters to the command parameters. I am not really sure why you want to send a parameter with just a single value to the stored procedure, Your method is accepting and object type array, if the array holds SqlParameter then you can do:

cmd.Parameters.Add((SqlParameter) o);

Otherwise you need to specify the parameter name and its value.

You can modify your method to accept an array of SqlParameter type objects and then add those to the command parameters.

edited Apr 24, 2013 at 10:48

answered Apr 24, 2013 at 10:34

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akansha Bansal Over a year ago

I tired using it, but unfortunately no constructor of SqlParameter takes 1 argument.

Habib Over a year ago

@AkanshaBansal, yes, actually your object should have atleast two values, once is the parameter name and the other is its value atleast. Why are you sending object[] as parameter, and does your object contains SqlParameter ?

Akansha Bansal Over a year ago

Yes! I am trying to make a generic function to set values via Stored Procedure into the respective tables with the required parameters.

DevProve · Accepted Answer · 2013-04-26 06:52:43Z

1

try change the param type.

public int set(string procName , SqlParameter[] param)
        {
              SqlConnection conn = new SqlConnection(constr);
              conn.Open();

          SqlCommand cmd = new SqlCommand(procName,conn);
          cmd.CommandType = CommandType.StoredProcedure;

          foreach(SqlParameter o in param)
          {
             cmd.Parameters.Add(o);      // Error
          }
          int res = cmd.ExecuteNonQuery();
          conn.Close();
          return res;
    })

You can get how to create SqlParameter SqlParameter Constructor

If u want create array: SqlParameter[] parameter = { new SqlParameter(...), new SqlParameter(...), new SqlParameter(...) };

edited Apr 26, 2013 at 6:52

answered Apr 24, 2013 at 10:39

DevProve

1921 silver badge16 bronze badges

1 Comment

Akansha Bansal Over a year ago

If i does so then i am unable to create a SqlParameter[] to pass to the function 'set'.

Collectives™ on Stack Overflow

Error : The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related