Failed SQL Queries

[Rex the Strange]

Hi All,

Help please! I have a formview on my page and I've created an update
command of the form:

"update

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.

 

[Rex the Strange]

Anyone? Anyone?

The lack of response is leading me to believe that this is impossible
(and a shortcoming of .NET)

Anyone?

 

[Jasbird]

Hi All,

Help please! I have a formview on my page and I've created an update
command of the form:

"update

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.

 

[Jasbird]

The return result from a query that succeeds should be 1 - any failed
query will naturally return a non-zero value

Apologies for the confusion here, my mistake.

The return value (for a good query) is naturally zero not 1

 

[Rex the Strange]

Post your question in a T-SQL forum to get a better explanation of SQL
error handling.

Sorry, I wasn't aware I was posting to the wrong group. I saw this as
an asp.net issue which is why I posted it here.

Can you recommend a good T-SQL forum?