Databases
StackOverflow2013
Postsdbo
POADO returns wrong order of magnitude
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POADO returns wrong order of magnitude
    text
    copied!<p>i'm writing a VBA macro for Excel 2003. I'm importing various data from a sql database to the Excel sheets. I tried two ways to do that: 1. QueryTables.Add(...) 2. with ADO</p> <p>I was in favour of ADO, because it seemed to be the 'better' or 'cleaner" solution. Everything worked well until i tried to get a numeric value with ADO out of the database. Instead of returning the value 1842,47078 it returned 0,01842. (just to show what i mean) Strange thing is: when i try it with QueryTables.Add(...) i get the right value.</p> <p>I have totally no clue why this happens, searched the internet for a solution but didn't found anything. It just happens with numeric values. If i get a string from the database everything is fine.</p> <p>Can anyone help me with this one?</p> <p>Driver is Firebird/InterBase(r) driver connecting with ODBC. Here is a little example how i do things:</p> <pre><code> 'ADO solution = wrong value With adoConnection .Provider = "MSDASQL" sConnection = "ODBC;DSN=ABC;Driver=Firebird/InterBase(r) driver;Dbname=blaName.gdb;" ConnectionString = sConnection .Open End With SQL_Import = "SELECT A.PRICE AS ""Price"" FROM TABLE A WHERE A.KEY ='x1234' " adoRecordset.ActiveConnection = adoConnection adoRecordset.Open SQL_Import varSol = adoRecordset.Fields("Price") Sheets(3).Cells(1, 1).Value = varSol adoRecordset.Close adoRecordset.ActiveConnection = Nothing adoConnection.Close 'QueryTables solution = right value Set QueryTbl = Sheets(3).QueryTables.Add(Connection:=sConnection, Destination:=Sheets(3).Cells(1, 2)) With QueryTbl .CommandText = SQL_Import .AdjustColumnWidth = True .Refresh BackgroundQuery:=False .Delete End With </code></pre> <p>I hope anyone can help me.</p> <p>Update: I got it working somehow, but i don't know what was wrong. I get the right results if i use the query "SELECT Price as numeric(15, 2)...". Strange this is that i tried the whole thing with C# on my computer and it worked without any problems. So the error seems to be caused by anything in Excel and/or VBA.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload