Error using money datatype parameter and DBNull.
- From: Jeff Mason <je.mason@xxxxxxxxxxx>
- Date: Sat, 03 Mar 2007 13:52:41 -0500
Hi,
Hi,
I am getting an error executing a SQL Server stored procedure with parameters of type
money. The following code fails:
With cmd
...
If mActualCost = Decimal.MinValue Then
.Parameters.AddWithValue(PRM_ActualCost, System.DBNull.Value)
Else
.Parameters.AddWithValue(PRM_ActualCost, mActualCost)
End If
...
.ExecuteNonQuery
End With
mActualcost is defined as type decimal. PRM_ActualCost is a string constant defining
the parameter name. By convention I define a decimal value type member variable to
have the value decimal.MinValue when it's value is unknown and therefore to be stored
as null in the database table being updated.
The stored procedure being executed is doing a simple INSERT into a table (there are
other parameters but they are irrelevant to this discussion). The ActualCost column
in the table is defined as a nullable column of type money. The parameter to the
stored procedure is defined as type money.
When my mActualCost variable has a value other than Decimal.MinValue, this code
succeeds and the database table is correctly updated. However, if it has the value
Decimal.MinValue, the code above fails. An error exception is thrown on the
ExecuteNonQuery. The error is:
"Implicit conversion from datatype nvarchar to money is not allowed. Use the CONVERT
function to run this query."
If I change this code to explicitly define the parameter's datatype as:
If mActualCost = Decimal.MinValue Then
With .Parameters.Add(PRM_ActualCost, SqlDbType.Money)
.Value = System.DBNull.Value
End With
Else
.Parameters.AddWithValue(PRM_ActualCost, mActualCost)
End If
then this code succeeds when the variable value is decimal.minvalue.
I have not done exhaustive tests for all possible datatypes, but I know similar code
succeeds with datatypes such as integer and string.
What's going on here with the money datatype and DBNull?
- Jeff
-- Jeff
.
- Prev by Date: Re: Getting out of memory when DataSet.GetXml()
- Next by Date: Re: Using TransactionScope and connection pool
- Previous by thread: GetOrdinal problem
- Next by thread: Enterprise Library and Access databases
- Index(es):
Relevant Pages
|
