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!
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:
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.
SELECT * FROM [TableName] WHERE [Field1] IS NULL OR IsNumeric([Field2]) = False
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
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?
Post sample source data and the result data, cause it makes not really sense.
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
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.
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?
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.
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.co
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.
SELECT * FROM [TableName] WHERE ISNUMERIC(tablename.dbo.co
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.
>>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
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
Andy,
Excellent comments. They led me to the solution. Here is how I create the datatable and fill it:
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.
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)
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial