ADO data conversion errors for adVarChar fields (VB6 example included)

From: Andy (akchan_at_telus.net)
Date: 09/27/04 

Date: 27 Sep 2004 15:08:52 -0700

I've inherited some code (VB6) that I'm to maintain. I'd like to
re-code it at some point (proper solution), but due to the size of code
and tight time constraints, I need a "quick fix".

Essentially, we have some data which should really be stored in a field
of datatype adDouble (or even adSingle, adNumeric, etc.), but because a
recordset is created "on the fly" with unknown datatypes (from a bulk
data import), they have been declared as adVarChar. As a result, ADO
is casting values and mangling them in the process.

Steps to reproduce:
1. Launch VB6, select New Standard EXE Project
2. From menu, select Project | References | Microsoft ActiveX Data
Objects 2.7 Library (actually, all versions that I've tried, since 2.0,
exhibit the problem).
3. Add a command button to the form.
4. Add the following code:

Private Sub Command1_Click()
Dim rstTest As ADODB.Recordset

Set rstTest = New ADODB.Recordset

With rstTest
.Fields.Append "SomeNumber", adVarChar, 255, adFldIsNullable
.Open
.AddNew Array("SomeNumber"), Array(0.3)
MsgBox .Fields("SomeNumber").Value
End With
End Sub

5. Run the app. Click on the button.

The value expected is 0.3, but the value displayed is
0.29999999999999999. If the value is changed to 0.2, ADO converts this
to 0.20000000000000001. ADO converts 0.4 to 0.40000000000000002.

Eventually, I will build the recordset fields based on a specification
where the datatype is defined, but for now, I'd like to hear of any
workarounds for this quirk. I can't find any MS KB article on this
topic...

Many thanks.
AKC