Validating user input using Sql DataType

  • Thread starter Thread starter Penn
  • Start date Start date
P

Penn

I need to validate user's input against all possible SqlServer data types
(int, tinyint, money, etc, etc.) Not only it needs to satisfy data types, it
also needs to satisfy precision and scale.

Are there any tools for this? Any suggestions on the most efficient method
to achieve this? I hate to write a class that does manual checking, maybe
trapping it someway and throw the error?

Thanks!
 
Penn,

Well, the provider itself should have some checks in it. Since you are
using SQL Server, you can always SET NOEXEC ON on the connection you will
execute the statement on, and then try and execute the statement. It will
not execute the statement, but compile the statement (I use compile in a
general sense, in the sense that SQL Server 2005 Books Online uses it) and
return errors if there are any.

Of course, the drawback is that you will have to execute the statement
twice, in which case it might just be better to execute the statement as-is
and catch whatever errors arise.
 
Thanks for your reply Nicholas.

We have a requirement to validate user input before it's executed so user
knows exactly which entry is off (they will be entering a big form). A
generic SQL error like "Arithmetic overflow error converting expression to
data type tinyint." is not user-friendly and is not specific to which value
that generates this error.

Any other options? Thanks again!


Nicholas Paldino said:
Penn,

Well, the provider itself should have some checks in it. Since you are
using SQL Server, you can always SET NOEXEC ON on the connection you will
execute the statement on, and then try and execute the statement. It will
not execute the statement, but compile the statement (I use compile in a
general sense, in the sense that SQL Server 2005 Books Online uses it) and
return errors if there are any.

Of course, the drawback is that you will have to execute the statement
twice, in which case it might just be better to execute the statement as-is
and catch whatever errors arise.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Penn said:
I need to validate user's input against all possible SqlServer data types
(int, tinyint, money, etc, etc.) Not only it needs to satisfy data types,
it
also needs to satisfy precision and scale.

Are there any tools for this? Any suggestions on the most efficient method
to achieve this? I hate to write a class that does manual checking, maybe
trapping it someway and throw the error?

Thanks!
Click to expand...
Click to expand...
 
Penn,

If that is the case, then you really have no option to duplicate the
code to perform all the checks before you execute the statement. You could
try to use the classes in the System.Data.SqlTypes namespace to try and
perform the conversions for you, but specific errors like that (the
arithmetic overflow) you will have to code for yourself.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Penn said:
Thanks for your reply Nicholas.

We have a requirement to validate user input before it's executed so user
knows exactly which entry is off (they will be entering a big form). A
generic SQL error like "Arithmetic overflow error converting expression to
data type tinyint." is not user-friendly and is not specific to which
value
that generates this error.

Any other options? Thanks again!


Nicholas Paldino said:
Penn,

Well, the provider itself should have some checks in it. Since you
are
using SQL Server, you can always SET NOEXEC ON on the connection you will
execute the statement on, and then try and execute the statement. It
will
not execute the statement, but compile the statement (I use compile in a
general sense, in the sense that SQL Server 2005 Books Online uses it)
and
return errors if there are any.

Of course, the drawback is that you will have to execute the
statement
twice, in which case it might just be better to execute the statement
as-is
and catch whatever errors arise.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Penn said:
I need to validate user's input against all possible SqlServer data
types
(int, tinyint, money, etc, etc.) Not only it needs to satisfy data
types,
it
also needs to satisfy precision and scale.

Are there any tools for this? Any suggestions on the most efficient
method
to achieve this? I hate to write a class that does manual checking,
maybe
trapping it someway and throw the error?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top