Double oddity

  • Thread starter Thread starter Jamie Collins
  • Start date Start date
J

Jamie Collins

Can anyone explain why I get different (wrong) results each time I run
the Take2 code (using ADO 2.8)?

Sub Take1()
' Create test schema and data
Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"

With .ActiveConnection
.Properties("Jet OLEDB:Transaction Commit Mode") = 1
.Properties("Jet OLEDB:Lock Delay").Value = 1
.Execute _
"CREATE TABLE DropMe (" & _
" anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Sub Take2()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
.CursorLocation = 3
.Open

Dim rs
Dim counter As Long
For counter = 1 To 2
Set rs = .Execute( _
"SELECT 2147483647 + 1 FROM DropMe")
Debug.Print CStr(rs(0))
rs.Close
Next
.Close
End With
End Sub

Jamie.

--
 
Hi, Jamie.
Can anyone explain why I get different (wrong) results each time I run
the Take2 code (using ADO 2.8)?
Click to expand...

Of course. You're exceeding the number range of a Long data type, the
default data type that will be used with 2147483647, which causes an
overflow error when one is added to it. The values in the registers are
incorrect when an overflow error occurs, but for some reason the overflow
register isn't being checked when this operation is performed, so no error
message is displayed, but the incorrect values are returned to the calling
program.

You can avoid this silent overflow error by using a number that will
automatically use a data type that will accommodate the range you need. For
example, the following equivalent equation will prevent a Long data type
from being used, but will still give the correct answer:

Set rs = .Execute("SELECT 2147483647.1 - 0.1 + 1 FROM DropMe")

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
'69 Camaro said:
Of course. You're exceeding the number range of a Long data type, the
default data type that will be used with 2147483647, which causes an
overflow error when one is added to it. The values in the registers are
incorrect when an overflow error occurs, but for some reason the overflow
register isn't being checked when this operation is performed, so no error
message is displayed, but the incorrect values are returned to the calling
program.
Click to expand...

Hi Gunny. Interesting. Which registers are these?

Note that when I use a server side cursor (whatever that means for Jet
You can avoid this silent overflow error by using a number that will
automatically use a data type that will accommodate the range you need. For
example, the following equivalent equation will prevent a Long data type
from being used, but will still give the correct answer:

Set rs = .Execute("SELECT 2147483647.1 - 0.1 + 1 FROM DropMe")
Click to expand...

Ah yes, the trick of operating on a numeric using a native DECIMAL type
to coerce the result to a DECIMAL (when will the Access team get around
to fixing the broken CDEC() casting function in their private version
of Jet, I wonder?)

As you probably guessed, I was testing to which type an INTEGER gets
promoted; I wasn't too surprised that the result was a FLOAT
(Double)...until I looked more closely at the result!

There's an even easier way to get the result as a DECIMAL <g>:

SELECT 2147483648 AS data_value,
TYPENAME(2147483648) AS data_type
FROM DropMe;

Cheers,
Jamie.

--
 
Jamie said:
Can anyone explain why I get different (wrong) results each time

Set rs = .Execute( _
"SELECT 2147483647 + 1 FROM DropMe")
Click to expand...

I tested this in Access 2007 (beta) in the UI (rather than ADO). No
overflow error but the data type is promoted to FLOAT (Double) rather
than DECIMAL:

SELECT 2147483647+1 AS data_value,
TYPENAME(2147483647+1) AS data_type
FROM DropMe;

Jamie.

--
 
Back
Top