1

This SqlDataSource, which is pulling from an Oracle db, throws "Specified cast is not valid" ONLY when I include c.item_cost in the SQL. If I remove this column, no error. It does not matter if I actually reference the field in code.

<asp:SqlDataSource ID="mySqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" ProviderName="Oracle.ManagedDataAccess.Client" 
     SelectCommand="select msi.segment1 partnumber, 
                           msi.description, 
                           msi.primary_uom_code uom, 
                           c.item_cost
                    from apps.cst_item_costs c, apps.mtl_system_items_b msi, APPS.ORG_ORGANIZATION_DEFINITIONS org
                         and msi.organization_id=org.ORGANIZATION_ID
                         and c.organization_id=org.ORGANIZATION_ID
                         and c.cost_type_id=1 
                         and  ((msi.inventory_item_id=c.inventory_item_id) 
                         and (msi.organization_id=c.organization_id))  
                    order by msi.segment1">

Also, if I add SQL to trim the number of rows down to a small number, the error is NOT thrown. I'm guessing it's some piece of data in the column causing the issue.

Is the SqlDataSource making some assumptions on datatypes?

Even if I can pinpoint the data, which appears to all be valid decimals, how can this error be avoided?

Please let me know if more information is needed. I am currently trying to pinpoint the data that causes this but everything appears to be a valid decimal though some have more digits. The SQL does successfully run with a db tool such as TOAD.

Update: The value in the c.item_cost is 15.032221226116 which is far more decimal digits than other data.

Tony L.
  • 17,638
  • 8
  • 69
  • 66
  • might be translation of types from Oracle to .Net Do you really need all the precision for the price? Does ROUND(c.item_cost,6) throw the error? – kevinskio Feb 09 '16 at 21:18
  • @kevinsky It works. At the same time, I could ROUND(c.item_cost,12) and it still works. Not up to me how far I can round but you've got me moving. Write it up and I'll accept. – Tony L. Feb 09 '16 at 21:39

1 Answers1

1

This is a problem involving conversion of Oracle data types to .Net types. Entity Framework 3 and 4 had a similar problem. If you specified an Oracle type as Number(10) it was interpreted as a .Net Float.

I suspect this is a similar issue with .Net casting the Oracle number into a float instead of a decimal. This answer has more details.

I recommend trying ROUND(c.item_cost,12) or even CAST(c.item_cost as DECIMAL(3,12)) as item_cost

Community
  • 1
  • 1
kevinskio
  • 4,431
  • 1
  • 22
  • 36