Type Casting Issues - best practices

[randym]

I am writing a program to read through an Access database and use the records selected to create orders in a separate ERP package... I have successfully selected the records for processing from Access into a dataset, but I am having problems due to InvalidCastException errors.

I have two fields in Access (Price and Cost) that are typed as 'Currency'. The purpose of these two fields are to override the values in the ERP package, that is, if they have a value set in Access, use it, if not, use the default value in the ERP package.

So, it is not unusual for there to be no value set for price and cost. Attempts to access this data from my data set in my code generated the cast exception errors mentioned above. I have created the following as a workaround for this issue, but my question is - Is this the best way to accomplish this? Suggestions are appreciated.

#code

Dim wQty, wPrice, wCost As Double

wPrice = 0
If Not (IsDBNull(ds2.Tables("Dtl").Rows(i).Item("Price"))) Then
wPrice = ds2.Tables("Dtl").Rows(i).Item("Price")
End If

wCost = 0
If ds2.Tables("Dtl").Rows(i).Item("Cost") <> vbNull Then
wCost = ds2.Tables("Dtl").Rows(i).Item("Cost")
End If
#code

[jmcilhinney]

First up, if the column is Currency in Access then the values are Decimal in VB, not Double. The correct way to go about this would be:

Dim price As Decimal = Decimal.Zero 'The variable will default to zero anyway but it doesn't hurt to be explicit.
Dim row = ds2.Tables("Dtl").Rows(i)

If Not row.IsNull("Price") Then
    price = CDec(row("Price"))
End If

Note that you might also use a nullable Decimal so that you don't have to use a fake zero:

Dim price As Decimal?
Dim row = ds2.Tables("Dtl").Rows(i)

price = If(row.IsNull("Price"), Nothing, CDec(row("Price")))

[dunfiddlin]

Well, best practice would probably be not to allow Null values in the database and force a default zero value.

[techgnome]

not necessarily.... Null means the value is unknown ... different from a value of 0 ... just like with strings, a null value can and is usually different from an empty string. There's nothing wrong with null values in a database... now if it's a required field ... you can't leave it as NULL and then either have to default it to a value, or force the user to enter a value.

-tg