Nice Err msg for Numeric to Int Problem 2

[nzgirl]

Hi all...
I have a screen field that accepts any char (Can't change that portion)
and saves into an Int field.

I check with IsNumeric(fieldname) to make sure it only has numeric data. Then I realised that a number like 3214123456 won't fit into the Int field as the largest size is 2147483647.

My question is: Is there a 'nice way' to check the size of the number without hardcoding the int number (ie I thought of doing
if fieldname - 2147483647 > 0 then errmsg = "number too long&quot

Thanks
Beth

 

[tlbroadbent]

Are you performing the validation on the SQL Server or at the client via VB code?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[nzgirl]

I thought there might be a way to do a cast to convert.. I think thats why I used this forum...
Can I move to the ASP forum?
It'll have to be in the ASP code before the insert statement that is currently failing...

any ideas?
Thanks

 

[SQLSister]

My personal preference is to do this type of error checking before sending the information to SQL Server. No sense wasting the network resources for a process that won't happen.

We do all the validation checks when the user clicks the submit button and then return and error message (if needed that describes all the errors.) I mention this because some sites check one at a time and then if you have more than one rror it sends you back to the form multiple times. I find this extremely annoying.

 

[nzgirl]

Yes I plan to do the validation first and all in one hit... but I want a 'non hardcoded' way to do the check...
can I look at the defn of the field and return its size?

Thanks

 

[SQLSister]

I suppose you could put the values that are in the valid range for each datatype in a table and then use that to pull in the parameters for the check, but these values are not really subject to a lot of change, I don't think I'd bother. However, since you may need to do this sort of check on many forms, I would create an object containing the values and the validation code and just instantiate it whenever I need to. Then you only need to change the hard-coded values once if they ever change. We have a library of validation objects that we pick and choose from when designing the validation for a particular page. I don't know if regular ASP supports this, but .Net certainly does. Makes both programming and maintenance easier.

 

[sunila7]

Hi

The best way to do this is to use a javascript function to validate the field before submitting the form to the database for inserting/ updating the table in the DB...


Sunil

 

[tlbroadbent]

You can use the VBScript CInt to convert the value to integer. CInt returns an overflow error if the value is not within the range of integer values.

On Error Resume Next
iTestValue = CInt(fieldname)
If Err.Number<>0 Then
Msgbox 'Invalid integer value'
End If




If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[nzgirl]

the CInt way looks good. When I get to go back to that project I'll test it out....
Thanks

 

[nzgirl]

Small prob with CInt. It doesn't assign a value if it overflows... it just errors :-(
Current thought is to define the variables in a file and use them.
Any better ideas appreciated
Thanks

 

[nzgirl]

Please accept my mondaymorningitis apologies.
Of course things don't work if you only use 1/2 the code....
Thanks all!!