' is not a valid integer value' This seems to happen mostly on Windows 8, but we do have a few Windows 7 reports. The setup files have not been changed recently. ' Is not a valid integer value I just opened my pc this morning and this annoying thing started to appear ı searched the web for a bit some guys were having this because of some tax things mine is not something like that ı need help please.
![]() By: Bhavesh Patel | Updated: 2017-05-11 | Comments (6) | Related: More >Functions - User Defined UDFProblem![]()
I have a CustomerDetails table with 15 columns in my SQL Server database. I need to insert data from a CSV file into the CustomerDetails table and have created a stored procedure called Import_CustomerDetails. In the stored procedure there is validation logic for positive decimal, negative decimal and positive integer values. I used the SQL Server ISNUMERIC system function for validation, but I couldn't validate the data as required because the ISNUMERIC function returns 1 for some characters that are not numbers such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). So, how can I validate data with these use cases?
Solution
Let me further explain my situation with additional examples. Here is a sample file and input columns for demo purposes. With this sample data set the custcontact column should be positive integer values. The latitude column should be positive decimal values and the longitude column should be a negative decimal value, for my purposes. I didn't need to validate the dollar sign character (i.e. $), but this file was created by multiple users manually so the wrong value may have been entered in the file.
![]()
Below is an example of the SQL Server ISNUMERIC system function use cases:
As shown above, the ISNUMERIC function returns 1 for some values that are not strictly numbers. The function returns 1 for numbers that include symbols like +, -, $, etc. As per my use case, I need to validate decimal and integer values.
SQL Server User Defined Functions for Integer and Decimal Validation
Next, I will create new user defined functions to validate integer and decimal values as per my use case.
First I will create the dbo.IsInt scalar function to validate a positive integer value.
In this code, I replace symbols (i.e. +, -) with the 'A' character in lieu of specific characters in which the ISNUMERIC function has returned 1 (true) in addition I have concated the '.0e0' exponential value which is a floating point notation trick. Now I can validate a positive integer value without any symbols.
Now I am going to call this function for demo purposes to validate the data in the custcontact column.
Now, I have a single positive integer value to meet my requirements.
Next, I am creating the dbo.IsABSdecimal function to validate positive decimal values.
This logic builds off the previous function's REPLACE logic and adds the charindex logic to find '.' with a bit a as return type which is Boolean.
Now I am going to call this function for demo purposes to validate the data in the latitude column.
Based on this logic, I got result as expected. It has validated the positive decimal value.
Now I am creating the dboIsDecimal function to validate all decimal values.
Now I am going to call this function for demo purposes to validate the data in the longitude column.
Based on this logic, I got the results as expected. It has validated the decimal values.
Next Steps
Last Updated: 2017-05-11 About the author
Bhavesh Patel is a SQL Server database professional with 10+ years of experience. View all my tips Re: Using DataAnnotations to verify integer
Nov 10, 2010 01:34 PM|francesco abbruzzese|LINK
Hi, jstrope
Save yuor time. I know quite well the sources of the model binder. Validation attributes are controlled only after model binder is attempted to conver a field value into its target type. The message you see is generated from the model binder that catch the wrong format exceptions generated by the converter and transform them into validation messages. You cannot specify a different message for each field, but you can override the default messages created by the model binder as follows:
The DefaultModelBinder class that is the one creating the validation error has a static field named: ResourceClassKey. Here you can put your own resource dictionary(resource dictionary are file to be added to the project, just do create new item and then select resource dictionary).
Once you have done this you can populate the dictionary with key and error messages. The key for the wrong format error is: 'PropertyValueInvalid'. To the righght of this key you can specify your GENERAL error message. General means that it is a string to be used in a string.format method. It has two parameters one for rhe field name and the other for its value. something like: 'bla bla bla {0} bla bla {1}'. if your property has a display annotation. the display name of the annotation is used as name of the filed in the error string.
Hope this solve your prblem...because at best of my knowledge it is the only solution.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |