SQL Query Returns a Null Value When Checking for Not Numeric

Can you also please post
1> the actual data in the table and
2> the result that you get when you fire the above query in your code?

First of all: This is not a valid T-SQL query. Cause ISNUMERIC() returns an INT (0 or 1) not a Boolean value which is an internal data type not exposed to queries.

As you said, you run it against different sources, what provider do you use?

The correct records are returned but fields with non-numeric entries have a null value returned instead of the actual contents

Select all Open in new window

Post sample source data and the result data, cause it makes not really sense.

>>but fields with non-numeric entries have a null value returned instead of the actual contents.

Just how do you inspect the field / get it into a local variable.  This sounds like you are attempting to put the returned value into a variable that isn't the correct type.

Thanks to all for your quick responses.  I am using the Microsoft.ACE.OLEDB.12.0 provider. Here is an example of the data

InvoiceNumber      Category      Asmt      InvoiceAmt      Stratum
108                                   3                            0      982.38                 3
124                                   3                            0      3.5                      Detail
160                                   3                                         314.58                 2
203                                   3                            0      146.42                2
794                                   3                     73.04      X913                3

The results should be the rows for invoice numbers 124, 160 and 794. "Detail" should appear rather than null and X913 should appear rather than null.

Would it be better to use  
     [Field2] Not Like '%[^0-9]%'
rather than
      IsNumeric([Field2]) = False
or is there a better way to check for numeric?

To repeat myself:
Just how do you inspect the field / get it into a local variable.

The result you want is a string, if you attempt to put it into a numeric variable in code then it will not result in anything sensible.

All I do is fill the datatable with the results of the query. The Excel worksheet I used for this example does not have any kind of definition for the fields as one would have in a database. The datatable is then bound to the datagridview. I do nothing else with it except to look at each cell in the datagridview. I will try to intercept the results at an earlier stage to see what is there.

OK. I stand corrected on the Excel spreadsheet. I changed the cell format for one column to text when almost all values were numberic. I then imported it into a SQL Server CE database as SQL Server Management Console is my only method to check a query. I ran the query

SELECT * FROM [TableName] WHERE ISNUMERIC(tablename.dbo.columnname) = 0

The correct rows were returned and the text column returned values were NULL. That means to me that we can't easily get the actual values back from that query. I will have to settle for setting the background color with no value displayed.

Please let me know if we should continue this any further. Thanks for your input. If you have suggestions on a simple way to get the value, please share them.

Andy,

Excellent comments. They led me to the solution. Here is how I create the datatable and fill it:

    Dim InputDataTable As DataTable
    Dim drCmd As New OleDbCommand
    With drCmd
        .Connection = oConn
        .CommandText = SelectCommand
    End With
    dr = drCmd.ExecuteReader
    InputDataTable = New DataTable
    InputDataTable.Load(dr)
 

Select all Open in new window

I built the Excel worksheet at a test file so know exactly what is in  it. The second version of the test file where the column was changed from numeric to text revealed what you and the others have been saying: If the original data type was numeric and contained a non-numeric then the returned value would be null. If the original data type was text then the returned value would be the non-numeric text value.

The ACE provider is not the problem. I think the same thing happens with other providers.

The problem was a combination of data type (small amount) and programmer ignorance (very large amount).

Thanks to all of you for your patience.