On 6 Nov 2006 15:02:59 -0800, "Rex the Strange"
<ro********@wid getinc.comwrote :
>Hi All,
Help please! I have a formview on my page and I've created an update
command of the form:
"update [table] set valuex=@valuex, valuey=@valuey where
valuez=@valuez "
which I can get to execute....sort of. The problem is, if it failed I
want to know exactly what the database was trying to execute but all I
get is a "your query failed, so suck on it" type of message with only
the vaguest direction of what could be the problem.
How can I "trap" the exception and create my own custom message that
tells me the exact query string?
tia,
rts
ps: extremely new to asp.net - I know asp classic like the back of my
hand but this is a whole new world.
This message should be in a T-SQL forum, which is why no one has
answered it here.
Design your SQL sproc such that you detect the error there and pass back
the result as an integer variable from your sproc. to your ASP.NET code.
It will be the SQL RETURN value.
DECLARE @QueryResult INT
// do your update ...
RETURN @QueryResult
If you use RETURN like this you will need to explicitly set the
@QueryResult depending upon whether your UPDATE works or not. But you
don't need to do this. Most people don't. The RETURN value is naturally
available from a sproc anyway whether or not you return a value (as
above) or even use the RETURN keyword.
The return result from a query that succeeds should be 1 - any failed
query will naturally return a non-zero value (even if you don't declare
one in your sproc). Look-up these sproc return values in SQL books
online to make sense of them. Likewise lookup RETURN in SQL books
online.
Lookup RAISERROR, WITH SETERROR | LOG | NOWAIT, @@ERROR as well. This
should be covered in any good book on T-SQL but I guess there are no v.
good comprehensive books on new features added to SQL-2005 yet. Viera's
book on SQL 2000 remains excellent (if well out of date) but I haven't
read his book on SQL 2005. Errors are covered in pp 387-405 of Viera's
SQL 2000 book. (his new Professional SQL 2005 book is due any day now)
Post your question in a T-SQL forum to get a better explanation of SQL
error handling.
