C# – SQL Execution Error. Error converting data type nvarchar to real

csql

Presently I am troubleshoot a SQL Server 2008 query that started failing. Here is the query:

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

When I remove the cast and execute the query it returns about 1 400 000 rows, so I take the DataValue results and run a small C# program against this output to verify that all the data is in fact numeric:

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++;
}

The program indicates that there are no data rows that are not numeric, so does anyone have any suggestions as to why I would be receiving this error? TIA.

UPDATE:
Here is the code I wrote to verify that it was checking every line of data:

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++;
}

This gives results like:

....

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

....

UPDATE 2:
Pasted code above from the full original view.

Best Answer

Use this to figure out which values cannot be parsed by SQL

select * from tblBenchData where ISNUMERIC(DataValue) = 0

or this

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

My guess would be a culture issue (e.g. "," vs ".")

Related Topic