Using SqlDBType.Decimal in Prepared Statement C#

am using a Prepared Statement in C#.

 SqlCommand inscommand = new SqlCommand(supInsert, connection);
 inscommand.Parameters.Add("@ordQty", SqlDbType.Decimal,18);
 inscommand.Prepare();
 u = inscommand.ExecuteNonQuery();

The above code throws below Exception:

SqlCommand.Prepare method requires parameters of type ‘Decimal’ have an explicitly set Precision and Scale.

EDIT: How to avoid this Exception

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The following would set a Decimal with Precision 18 and Scale 8 (Decimal (18,8))

SqlCommand insertCommand= new SqlCommand(supInsert, connection);
insertCommand.Parameters.Add("@ordQty", SqlDbType.Decimal,18);

insertCommand.Parameters["@ordQty"].Precision = 18;
insertCommand.Parameters["@ordQty"].Scale = 8;

insertCommand.Prepare();
u = insertCommand.ExecuteNonQuery();

Method 2

As the exception pointed out, you have to explicitly set the SqlParameter.Precision and SqlParameter.Scale properties in order to use the decimal type as a parameter.

Let’s say your SQL field is of type decimal(18,8). The way to do this inline is to use brace-initialization for your SqlParameter while adding it to the SqlParameterCollection, as follows:

cmd.Parameters.Add(new SqlParameter("@ordGty", SqlDbType.Decimal) {
    Precision = 18, Scale = 8 });

You can also do

cmd.Parameters.Add(new SqlParameter("@ordGty", SqlDbType.Decimal) {
    Precision = 18, Scale = 8}).Value = 0.4m; // or whatever

to add the value, if you need one. You could even do

cmd.Parameters.Add(new SqlParameter("@ordGty", SqlDbType.Decimal) {
    Precision = 18, Scale = 8, Value = 0.4m /* or whatever */});

if you prefer. Brace initialization is really powerful.

Side note: I realize that this is an old question, but I think this form is much more readable than creating the object while adding it to the list and then setting the scale and precision. For Posterity! (since this is a high-listing google search result)

Method 3

try this:

SqlParameter parameter = new SqlParameter("@ordQty", SqlDbType.Decimal);
parameter.Precision = 18;
parameter.Scale = 0;
parameter.Value = YOURVALUEHERE;
inscommand.Parameters.Add(parameter);

Method 4

You will have to explicitly define precision and scale for this parameter.

SqlParameter ordQty = cmd.Parameters.Add("@ordQty", SqlDbType.Decimal);
ordQty.Precision = x; //Replace x with what you expect in Sql Sp
ordQty.Scale = y; //Replace y with what you expect in Sql Sp
ordQty.Value = 18; //Set value here
inscommand.Parameters.Add(ordQty);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0