sql-serverint32commandparameter

Why is a string expected at this point?


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":

enter image description here

?!?

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


Solution

  • 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.