In VB6, ADODB.Recordset values from sql query not returning decimals

Question

I'm fetching data from a db2 in VB6 using ODBC. The connection and setup is fine, but there's a problem in the returned data.

The fields in my record set are all rounded down to integer, so if the db column has a 0.99 it gets returned as 0, which isn't great.

The same query in Microsoft Query results in the proper values, with decimals.

This is the pertinent part of the code:

Set objCommand As New ADODB.Command
Set recordSet As New ADODB.Recordset

sqlQuery= "Select f2scop Price from database.mpline  where ibsuno='" & Order & "' order by ibpuno, ibpnli"
                   
With objCommand
   .ActiveConnection = ConnectDB2
   .CommandType = adCmdText
   .CommandText = sqlQuery
End With
                
With recordSet
   .CursorType = adOpenStatic
   .CursorLocation = adUseClient
   .LockType = adLockOptimistic
   .Open objCommand
End With

myVariable.value = recordSet("Price")

Debugging the recordSet("Price").OriginalValue returns the same rounded down value.
recordSet("Price").precision, .numericscale and .type return the expected results for fields that accept decimals.

Is there anything I could do about the recordset, ODBC, SQL query or anything else?

Any and all help is appreciated!

PS: I answer with a way to work around my problem.

Answer 1

I have come up with a workaround for this issue.

In the sql query I multiply the data by 100 and then, when assigning the value to the variable, divide it by 100.

Like so:

sqlQuery= "Select (f2scop * 100) Price from database.mpline where ibsuno='" & Order & "' order by ibpuno, ibpnli"

myVariable.value = recordSet("Price") / 100

It's not a solution, but it bypasses the problem, and that's good enough for me.

There might be someone for whom this answer is not applicable, so more help is always appreciated! Thx

Answer 2

I had such problems with databases due to locale! If you have eg greek windows, then the point in decimals is a comma, and the point is the thousands separator. Then, if the decimals in your database table is point- and not comma-separated, odbc will truncate the number.