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.

unfortunately it's not my call to make, there's a lot more code already in use — Filipe Lopes
– Filipe Lopes, Commented Oct 20, 2023 at 10:35
Does DB2 SQL have any sort of CAST() operator that you could use to force it to return a decimal value? Even just as a test? — StayOnTarget
– StayOnTarget, Commented Oct 20, 2023 at 18:24
@StayOnTarget, yes, I tried CAST(f2scop AS DECIMAL(19, 8)) Price, same result — Filipe Lopes
– Filipe Lopes, Commented Oct 23, 2023 at 8:38

Filipe Lopes · Accepted Answer · 2023-10-23 16:32:44Z

0

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

answered Oct 23, 2023 at 16:32

Filipe Lopes

113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A. Plevrakis · Accepted Answer · 2023-12-21 16:52:07Z

-1

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.

answered Dec 21, 2023 at 16:52

A. Plevrakis

11 bronze badge

1 Comment

GSerg Over a year ago

This only happens when you store numbers as strings, which you should not do to begin with. The OP stores numbers as numbers as seen from the Precision, NumericScale and Type.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related