C#: How to set a string as SqlParameter?

Question

I have a DataTable. How to set a certain value of it as one of my SqlParameters? I receive this error message: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Either I do:

myCommand.Parameters.Add("@managedBy");
myCommand.Parameters["@managedBy"].Value = comp.Rows[i][4].ToString();

myCommand.Parameters.Add("@modified_at");
myCommand.Parameters["@modified_at"].Value = DateTime.Now.ToString("MM/dd/yyyy");

myCommand.Parameters.Add("@cn");
myCommand.Parameters["@cn"].Value = comp.Rows[i][1].ToString();

or:

myCommand.Parameters.Add("@managedBy");
myCommand.Parameters["@managedBy"].Value = (SqlParameter)comp.Rows[i][4];

myCommand.Parameters.Add("@modified_at");
myCommand.Parameters["@modified_at"].Value = DateTime.Now.ToString("MM/dd/yyyy");

myCommand.Parameters.Add("@cn");
myCommand.Parameters["@cn"].Value = (SqlParameter)comp.Rows[i][1];

What am I missing? How to set the parameter from a DataTable?

D Stanley · Accepted Answer · 2014-01-08 14:03:29Z

1

The SqlParameterCollection.Add method overloads with one argument requires that the argument be a SqlParameter. To create an argument with a string name you also need to specify the type (and optionally a size):

myCommand.Parameters.Add("@managedBy", SqlDbType.NVarChar);

To set the value in one call you can use AddWithValue, wihch will infer the SQL type from the value type:

myCommand.Parameters.AddWithValue("@managedBy", comp.Rows[i][4]);

edited Jan 8, 2014 at 14:03

answered Jan 8, 2014 at 13:57

D Stanley

153k12 gold badges189 silver badges257 bronze badges

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

Comments

Steve · Accepted Answer · 2014-01-08 14:02:25Z

1

The SqlParametersCollection inside the SqlCommand object has an Add method that wants an SqlParameter object.

So your code should be something like this

 myCommand.Parameters.Add(new SqlCommand("@managedBy", SqlDbType.AnsiString)).Value = .......;

There are numerous possibilities to add a new parameter to the collection, the easiest of which is

 myCommand.Parameters.AddWithValue("@managedBy", comp.Rows[i][4].ToString());

This particular version however requires particular care as explained in this very detailed article on MSDN

edited Jan 8, 2014 at 14:02

answered Jan 8, 2014 at 13:56

Steve

217k22 gold badges242 silver badges296 bronze badges

Comments

Karthik D V · Accepted Answer · 2014-01-08 13:58:51Z

0

Use the following code:

myCommand.Parameters.AddWithValue("ParameterName", anyObject);

answered Jan 8, 2014 at 13:58

Karthik D V

9261 gold badge11 silver badges19 bronze badges

Collectives™ on Stack Overflow

C#: How to set a string as SqlParameter?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related