Link to home
Avatar of rkulp
rkulpFlag for United States of America

asked on

SQL Query Returns a Null Value When Checking for Not Numeric

I have a SQL query that I run against either Access, dBase or Excel. In that query I check for null fields and also, depending on user preferences, non numeric values in fields that should only contain numerics. The correct records are returned but fields with non-numeric entries have a null value returned instead of the actual contents. Is this normal or have I just messed up (which is very likely)? Here is a sample query:

SELECT * FROM [TableName] WHERE [Field1] IS NULL OR IsNumeric([Field2]) = False

Open in new window

There could be several fields checked, some only for null and others for both null or non-numeric. All conditions are joined with "OR" as above.
Avatar of Karrtik Iyer
Karrtik Iyer
Flag of India image

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?
Avatar of ste5an
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

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.
Avatar of rkulp


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.
Avatar of rkulp


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.
Avatar of rkulp


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.
Avatar of AndyAinscow
Flag of Switzerland image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rkulp



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

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.