c# - SQL 执行错误。将数据类型 nvarchar 转换为 real 时出错

Question

目前我正在对开始失败的 SQL Server 2008 查询进行故障排除。这是查询:

SELECT     TOP (100) PERCENT dbo.tblBenchmarkData.FieldDataSetID, dbo.tblBC.BCID, CAST(dbo.tblBenchmarkData.DataValue AS float(8)) AS DataValue, 
                  dbo.tblBC.BCMnemonic, dbo.tblDataType.DataTypeMnemonic
FROM         dbo.tblFieldDataSet RIGHT OUTER JOIN
                  dbo.tblBenchmarkData ON dbo.tblFieldDataSet.FieldDataSetID = dbo.tblBenchmarkData.FieldDataSetID LEFT OUTER JOIN
                  dbo.tblBC LEFT OUTER JOIN
                  dbo.tblDataType ON dbo.tblBC.DataTypeID = dbo.tblDataType.DataTypeID RIGHT OUTER JOIN
                  dbo.tblZEGCode ON dbo.tblBC.BCID = dbo.tblZEGCode.BCID ON dbo.tblBenchmarkData.ZEGCodeID = dbo.tblZEGCode.ZEGCodeID
WHERE     (dbo.tblDataType.DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}') AND (dbo.tblFieldDataSet.OriginalFieldDataSetID IS NULL) AND 
                  (dbo.tblFieldDataSet.Duplicate = 0)
ORDER BY dbo.tblBC.BCMnemonic, DataValue 

当我删除转换并执行查询时，它返回大约 1,400,000 行，因此我获取 DataValue 结果并针对此输出运行一个小型 C# 程序，以验证所有数据实际上都是数字:

List<String> lstLinesOfFile = new List<string>();
Int64 intLineCounter = 0;
ReadFile("data.txt");
double dblNum;

foreach (string strValue in lstLinesOfFile)
{
   bool isNum = double.TryParse(strValue, out dblNum);

   if (!isNum)
   {
      Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Value = " + strValue);
   }
   intLineCounter++;
}

该程序表明没有非数字数据行，那么有人对我为什么会收到此错误有任何建议吗？ TIA。

更新:
这是我编写的代码，用于验证它是否正在检查每一行数据:

List<String> lstLinesOfFile = new List<string>();
Int64 intLineCounter = 0;
ReadFile("data.txt");
double dblNum;

foreach (string strValue in lstLinesOfFile)
{
   bool isNum = double.TryParse(strValue, out dblNum);

   if (!isNum)
   {
      Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Value = " + strValue);
   }
   else
   {
       Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Number = " + strValue);
   }
   intLineCounter++;
}

结果如下:

....

Line: 241564, Number = 1843.2
Line: 241565, Number = 18430
Line: 241566, Number = 18430.9
Line: 241567, Number = 18431.6
Line: 241568, Number = 18433.9
Line: 241569, Number = 1844.52

....

更新 2:从完整的原始 View 粘贴上面的代码。

Answer 1

用它来找出哪些值不能被SQL解析

select * from tblBenchData where ISNUMERIC(DataValue) = 0

或者这个

select * from tblBenchData where DataValue like '%[a-Z]%'

我的猜测是文化问题(例如“,”与“.”)