CODEWITHSUNDEEP
c#sqlparameters
fishmong3r
fishmong3r

Reputation: 1434

C#: How to set a string as SqlParameter?

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?

Upvotes: 0

Views: 2456

Answers (3)

D Stanley
D Stanley

Reputation: 152624

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]);

Upvotes: 1

Steve
Steve

Reputation: 216343

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

Upvotes: 1

Karthik D V
Karthik D V

Reputation: 926

Use the following code:

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

Upvotes: 0

Related Questions