System.Data.SqlTypes and NULL

Question

If I have a variable, X, of type SqlDecimal, that was instantiated without providing a decimal value parameter to the constructor, then the .IsNull property is True:

Dim X As New SqlDecimal '.Value = Null

If I have a Stored Procedure that acepts a single parameter, @SqlDecimalParameter, of type Decimal, the value of this this parameter could be NULL or a decimal value. This is how I would normally call the stored procedure:

Dim Cmd As New SqlClient.SqlCommand

 If X.IsNull Then
    Cmd.Parameters.Add(New SqlClient.SqlParameter(parameterName:="@SqlDecimalParameter", value:=DBNull.Value))
Else
    Cmd.Parameters.Add(New SqlClient.SqlParameter(parameterName:="@SqlDecimalParameter", value:=X.Value))
End If

However, I would expect the following to work, but it does not when the X.IsNull is true.

Cmd.Parameters.Add(New SqlClient.SqlParameter(parameterName:="@SqlDecimalParameter", value:=X))

It seems like the 1st example should work. Am I missing something? after all, the Sql datatypes are designed to work with SQL Server, I thought.

When X is set to the following

commissionDue = New SqlDecimal(3.2)

I get the following error:

Failed to convert parameter value from a SqlDecimal to a Decimal

It works when X.IsNull is True.

How do you set the value of the PARAM object when it can be NULL?

I thought I was getting an error that was something like SqlDecimal could not be converted to a Decimal, but now the damn thing seems to be working. I swear the thing is inconsistant. It must be me. I'm investigating. Are you agreeing that it sounds like the 2nd approach should work? — Chad
– Chad, Commented Oct 26, 2010 at 0:20
Did you mean to say "If Not X.IsNull" then add a param object, otherwise don't and, as a result, Null will be passed? If so, that sounds like a better way of doing it, but still it seems longer than necessary/than the way I expected it to work, which required no if. Good suggestion. — Chad
– Chad, Commented Oct 26, 2010 at 0:27
Nope, not passing the parm if it is NULL doesn't work: Procedure or function 'UpdateCustomerPerformance' expects parameter '@commissionDue', which was not supplied — Chad
– Chad, Commented Oct 26, 2010 at 1:14

ntziolis · Accepted Answer · 2010-10-26 00:41:28Z

1

First idea:
You need to specify the type explicitly when creating a new instance of a parameter. Otherwise the framework will try to infer the type, which is never something you should count on going the right way.

Dim parameter As New SqlParameter("@SomeName", SqlDbType.[Decimal])
parameter.Value = 3.2

Second idea:
Instead of creating a SqlDecimal value try using a simple nullable decimal. This way it does not have to be converted and you might be able to avoid the error.

Dim test As System.Nullable(Of Decimal) = 3.2

answered Oct 26, 2010 at 0:41

ntziolis

10.2k1 gold badge36 silver badges50 bronze badges

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

5 Comments

Chad Over a year ago

How do you set Test to Null? Test = Nothing? Pretty sure that is how...When I do that I get: Procedure or function 'UpdateCustomerPerformance' expects parameter '@commissionDue', which was not supplied.<BR>

Chad Over a year ago

I did specify the type, sorry for not including it in my example.

ntziolis Over a year ago

So the second one works with an actual value (3.2) but not with a null value? Try to additionally specify on construction that the parameter is nullable.

Chad Over a year ago

No, it worked with the NULL value, but could not convert the SqlInt32 object to an int. how do I specify that the PARAM is NULLABLE?

ntziolis Over a year ago

There is another constructor overload that takes an additional boolean flag to signal the parameter that its nullable.

Collectives™ on Stack Overflow

System.Data.SqlTypes and NULL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related