"Specified cast is not valid" when populating DataTable from OracleDataAdapter.Fill()
Solution 1
Answering my own question:
So it seems that the Oracle number type can hold many more decimal places than the C# decimal type and if Oracle is trying to return more than C# can hold, it throws the InvalidCastException.
Solution?
In your sql, round any results that might have too many decimal places to something sensible. So I did this:
SELECT acct_no, ROUND(market_value/mv_total, 8) -- rounding this division solves the problem
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0
And it worked.
The take away is: Incompatibility between Oracle number type and C# decimal. Restrict your Oracle decimal places to avoid the invalid cast exceptions.
Hope this helps someone else!
Solution 2
I know this thread is really old.. However I had a similar issue.
The best solution I have to use the Oracle method TO_BINARY_DOUBLE on Oracle Decimal column
Solution 3
I know this has been answered already, but I also found another alternative that I use as well. I used a CAST on the field that was giving me troubles.
Based on OP's SELECT command:
SELECT acct_no, CAST((market_value/mv_total) AS DECIMAL(14,4)) -- CAST to decimal here
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0
Related videos on Youtube
namford
Updated on July 08, 2022Comments
-
namford almost 2 years
I can't seem to find this question anywhere on Google (or StackOverflow), which really surprised me, so I'm putting it on here to help others in the same situation.
I have a SQL query which runs fine on Oracle Sql Developer, but when I run it through
C#
usingadapter.Fill(table)
to get the results, I getSpecified cast is not valid
errors (System.InvalidCastException
).Here is cut-down version of the C# code:
var resultsTable = new DataTable(); using (var adapter = new OracleDataAdapter(cmd)) { var rows = adapter.Fill(resultsTable); // exception thrown here, but sql runs fine on Sql Dev return resultsTable; }
And here is a simplified version of the SQL:
SELECT acct_no, market_value/mv_total FROM myTable WHERE NVL(market_value, 0) != 0 AND NVL(mv_total, 0) != 0
If I remove the division clause, it doesn't error - so it's specific to that. However, both market_value and
mv_total
are of type Number(19,4) and I can see that the Oracle adapter is expecting a decimal, so what cast is taking place? Why does it work on SqlDev but not in C#? -
Chrisi almost 9 yearsThanks for posting a solution to your own problem. probably saved me a couple of hours (and gray hair^^). But is there a way that avoids rounding numbers within the query?
-
TheRoadrunner over 7 yearsI know this post is kinda old, but this just happened to me, and rounding didn't help. In my case, I had some very high values. For completeness (hopefully not needed for market values :-) ) write: ROUND(GREATEST(LEAST(market_value/mv_total,7.9E28),-7.9E28),8)
-
Ali Umair over 5 yearsYou brother saved my day
-
Lucas925 over 5 yearsYou saved my life with this. Thanks!
-
Crismogram over 4 yearswhile I did not follow this fixed. I salute you for posting it. I was running in circles for hours.
-
Allen about 4 yearsOuch, Bitten again. So I did this: SELECT ...., ,to_char(Round(b.Length,2),'9,999.00') AS Feet,... from <some_table> which not only addressed the overly precise return value but also formatted the data in a tidy way, since I only needed it for reporting purposes.
-
Allen about 4 yearsFor consistency I use a function taking the input value and returning a string to defend against this problem... plus it will round and format as desired. If I knew how I could post it but it is pretty trivial.