ADO and Null values

I'm hoping someone can help out out on this. I am trying to populate an ADODB.Parameter collection to submit values from a VB form to a SQL server database. (The parameter collection corresponds to the parameters of a stored procedure.)

Here's my problem ... some of the fields can (and will) be left empty. I would like to have the database populate these fields with a NULL value. I am trying to populate the parameter collection as follows:

Dim sMileage as string
Dim prmMileage as ADODB.Parameter

Let sMileage = txtMileage.text

Set prmMileage = New ADODB.Parameter

With prmMileage
.Type = adDecimal
.Size = 9
.Direction = adParamInput
If sMileage = "" Then
.Value = Null
Else
.Value = CDec(sMileage)
End If
End With

When I do it this way, I get a message about an improperly formed Parameter object. The only way I have gotten around this in the past was to pass a dummy value ("null") to the stored procedure and have it take care to change that value from "null" to NULL. In the case I have identified here, I would have to pass something like "9999.9999" since I am sending a decimal value to the database.

Any suggestions on how I should handle a case like this? I also am passing Dates to the database, so I may have to handle that situation as well.

Any help would be GREATLY appreciated!!!!

Thanks in advance ...

Dan

[JHausmann]

try:

With prmMileage
.Attributes = adParamNullable
.Type = adDecimal
.Size = 9
.Direction = adParamInput
If sMileage = "" Then
.Value = Null
Else
.Value = CDec(sMileage)
End If
End With