代码之家 › 专栏 › 技术社区 › SherlockSpreadsheets

SSRS System.InvalidCastException-位于OracleDataReader.GetDecimal(Int32 i)

reporting-services oracle sql-server

SherlockSpreadsheets · 技术社区 · 6 年前

我有一份SSRS报告 SQL Server views ,指向 Oracle tables . 我编辑了SSRS报表数据集,以便直接从Oracle数据库进行查询。这似乎是一个非常简单的改变,直到我得到这个错误信息:

System.InvalidCastException:指定的强制转换无效。

Field âUOM_QTYâ at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i) .

, (DELV_RECEIPT.INV_LBS/ITEM_UOM_XREF.CONV_TO_LBS) AS UOM_QTY

错误1

Severity    Code    Description Project File    Line    Suppression State
Warning     [rsErrorReadingDataSetField] The dataset âdsIngredientCostsâ contains a definition for the Field âUOM_QTYâ. The data extension returned an error during reading the field. System.InvalidCastException: Specified cast is not valid.
   at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
   at Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i)
   at Microsoft.ReportingServices.DataExtensions.DataReaderWrapper.GetValue(Int32 fieldIndex)
   at Microsoft.ReportingServices.DataExtensions.MappingDataReader.GetFieldValue(Int32 aliasIndex)      C:\Users\bl0040\Documents\Visual Studio 2015\Projects\SSRS\Project_ssrs2016\Subscription Reports\Feed Ingredient Weekly Price Avg.rdl   0

错误2

Severity    Code    Description Project File    Line    Suppression State
Warning     [rsMissingFieldInDataSet] The dataset âdsIngredientCostsâ contains a definition for the Field âUOM_QTYâ. This field is missing from the returned result set from the data source.       C:\Users\bl0040\Documents\Visual Studio 2015\Projects\SSRS\Project_ssrs2016\Subscription Reports\Feed Ingredient Weekly Price Avg.rdl   0

源表:

+------------+---------------+-------------+---------------+-----------+
| Source     | TABLE_NAME    | COLUMN_NAME | DataSize      | COLUMN_ID |
+------------+---------------+-------------+---------------+-----------+
| ORACLE     | DELV_RECEIPT  | INV_LBS     | NUMBER (7,0)  | 66        |
+------------+---------------+-------------+---------------+-----------+
| ORACLE     | ITEM_UOM_XREF | CONV_TO_LBS | NUMBER (9,4)  | 3         |
+------------+---------------+-------------+---------------+-----------+
| SQL SERVER | DELV_RECEIPT  | INV_LBS     | numeric (7,0) | 66        |
+------------+---------------+-------------+---------------+-----------+
| SQL SERVER | ITEM_UOM_XREF | CONV_TO_LBS | numeric (9,4) | 3         |
+------------+---------------+-------------+---------------+-----------+

2 回复 | 直到 6 年前

SherlockSpreadsheets 6 年前

   , CAST(DELV_RECEIPT.INV_LBS/ITEM_UOM_XREF.CONV_TO_LBS AS NUMERIC(9,4)) AS UOM_QTY

Code Project forum 说。。。

为什么不使用类型化数据集?你头痛是因为 IDE使生活更美好、更安全、更轻松,而且您不必使用它是的。我真的不明白。

Markus1980Wien 6 年前

下面是一种使用扩展方法而不是修改SQL查询来修复此错误的方法。

public static Decimal MyGetDecimal(this OracleDataReader reader, int i)
{
    try
    {
        return reader.GetDecimal(i);
    }
    catch (System.InvalidCastException)
    {
        Oracle.ManagedDataAccess.Types.OracleDecimal hlp = reader.GetOracleDecimal(i);
        Oracle.ManagedDataAccess.Types.OracleDecimal hlp2 = Oracle.ManagedDataAccess.Types.OracleDecimal.SetPrecision(hlp, 27);
        return hlp2.Value;
    }
}

Allen 5 年前

谢谢,但如果您的查询看起来像:

还有。GetDecimal不见了?

显然,这是一种老派的获取数据的方法。