Why is a string expected at this point?

Question

From an answer or comment to my question here, I was directed here.

So I changed my code from this:

double _imdbThreshold = 0.0;
(IMDBRating is a Decimal(2,1) data type in the SQL Server (Express) Database.)
...
cmd.Parameters.AddWithValue("@IMDBMinRating", _imdbThreshold);

...to this:

cmd.Parameters.Add("@IMDBMinRating", SqlDbType.Decimal, 2, 1).Value = _imdbThreshold;

...based on this example in the article linked above:

cmd.Parameters.Add("@Parameter", SqlDbType.Decimal, 11, 4).Value = MyDecimalVariable;

But I get, "Cannot convert from int to string":

?!?

Why is it finding fault with my "1" int arg there?

Answer 1

I can't find a reference any more, but years ago when using SqlHelper, the guidance came with a number of extension methods that simplify working with SqlCommand and SqlParameter.

The following extension method will allow your code to work, I can only speculate but I suspect the code you referenced used a similar extension method and they were not aware it was not part of the standard runtime. a very easy mistake to make!

/// <summary>
/// Adds a System.Data.SqlClient.SqlParameter given the specified parameter name,
/// System.Data.SqlDbType, size <i>AND</i> precision.
/// </summary>
/// <param name="self">The SqlParameterCollection to add the parameter to.</param>
/// <param name="parameterName">The name of the parameter to map.</param>
/// <param name="dbType">One of the System.Data.SqlDbType values.</param>
/// <param name="size">The length of the parameter.</param>
/// <param name="precision">The precision of the parameter.</param>
/// <returns>The System.Data.SqlClient.SqlParameter object that was initialized and added to the collection.</returns>
public static SqlParameter Add(this SqlParameterCollection self, string parameterName, SqlDbType dbType, int size, byte precision)
{
    return self.Add(new SqlParameter(parameterName, dbType, size) { Precision = precision });
}

I find this syntax simpler to read when you stare at a lot of these all day, but it is also consistent with the standard Add methods for types that do not support precision:

decimal myDecimalVariable = 19.8M;
int myIntVariable = 2;
string myStringVariable = "Test";

cmd.Parameters.Add("@Parameter1", SqlDbType.Decimal, 11, 4).Value = myDecimalVariable;
cmd.Parameters.Add("@Parameter2", SqlDbType.Int).Value = myIntVariable;
cmd.Parameters.Add("@Parameter3", SqlDbType.Char, 4).Value = myStringVariable;

---

Given the number of other existing methods, I can only imagine that the original developers simply forgot to include this simple method in the SqlParameterCollection definition.

Answer 2

A different article on the "evils" of AddWithValue() uses this example for a Decimal type parameter:

command.Parameters.Add( new SqlParameter("@InvoiceTotal", SqlDbType.Decimal) { Precision = 18, Scale = 4, Value = 1234.56m });

So I changed my code to this:

cmd.Parameters.Add(new SqlParameter("@IMDBMinRating", SqlDbType.Decimal) { Precision = 2, Scale = 1, Value = _imdbThreshold });

...and it compiles now.

It's all good now, this way:

cmd.Parameters.Add(new SqlParameter("@IMDBMinRating", SqlDbType.Decimal) { 
                       Precision = 2, Scale = 1, Value = _imdbThreshold });
cmd.Parameters.Add("@EarliestYear", SqlDbType.Char, 4).Value = _yearBegin;
cmd.Parameters.Add("@LatestYear", SqlDbType.Char, 4).Value = _yearEnd;