SQL Query Returns a Null Value When Checking for Not Numeric

rkulp
rkulp used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Karrtik IyerSoftware Architect

Commented:
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 Developer

Commented:
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 / Consultant

Commented:
>>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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 / Consultant

Commented:
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.

Author

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.

Author

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.
Freelance programmer / Consultant
Commented:
>>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

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial