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.
LVL 1
rkulpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karrtik IyerSoftware ArchitectCommented:
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?
ste5anSenior DeveloperCommented:
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.
AndyAinscowFreelance programmer / ConsultantCommented:
>>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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rkulpAuthor Commented:
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?
AndyAinscowFreelance programmer / ConsultantCommented:
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.
rkulpAuthor Commented:
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.
rkulpAuthor Commented:
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.
AndyAinscowFreelance programmer / ConsultantCommented:
>>All I do is fill the datatable with the results of the query.
How do you create the fields in the datatable ?

>> I then imported it into a SQL Server CE database
Did you look at the values in the table itself, not the results of the query ?

>>That means to me that we can't easily get the actual values back from that query.
Such a query will return ALL fields, including text, without problems.


You could try a different provider just to test if there is really a problem in the Microsoft.ACE.OLEDB.12.0 provider

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rkulpAuthor Commented:
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)
 

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.