Hi all,

i have a sp that produce an error when execute by some special
parameters. i write a program in vb.net that execute it, but i come
accross a strange problem:

when i execute the sp with EXECUTENONQUERY the error is raised, but
if i use EXECUTEREADER, nothing happens(no error is shown).

Can anybody help me?
Has anybody come accross something like this problem?

Thanks
man shahi

Without seeing the code or understanding what the stored procedure is
supposed to be doing, it's impossible to say. Or what you're hoping to
achieve by executing it two different ways, one to return a result set
and one to not. In general, SQLS errors with a severity between 11 and
16 can be handled by iterating through the errors in your SqlException
handler. See SQL Books Online and the .NET help file for more info.

-- Mary
MCW Technologies
http://www.mcwtech.com

On 6 Oct 2003 03:50:10 -0700, (E-Mail Removed) (shahi) wrote:

>Hi all,
>
>i have a sp that produce an error when execute by some special
>parameters. i write a program in vb.net that execute it, but i come
>accross a strange problem:
>
> when i execute the sp with EXECUTENONQUERY the error is raised, but
>if i use EXECUTEREADER, nothing happens(no error is shown).
>
>Can anybody help me?
>Has anybody come accross something like this problem?
>
>Thanks
>man shahi

Hi Mary,

it is a big and complex stored procedure, that i have not written it. in
this sp there is a transaction and on that there is some update command
and after committing the transaction there is a select statement that
select some fields from some tables. i can send it,if u want, but i
think this is not helpful.

Okey, return to my question, as i mentioned ago if i execute the sp by
cm.EXECUTENONQUERY (cm is a SQLCOMMAND object) i get the error but if i
replace EXECUTENONQUERY with EXECUTEREADER and run the project i never
get the error.


regards



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Basically I'm just guessing here based on my experience with SQL
Server. If you are using a datareader, then you're just getting back a
result set. If the result set is empty, then there's no error
involved, it's just an empty result set. If the stored procedure
doesn't return a result set, errors can be returned by RAISERROR
statements in the stored procedure code, which you would see with
executenonquery. Usually the stored procedure writer returns error
information because, for the most part, SQLS doesn't. In other words,
your stored procedure doesn't halt on an error line, SQLS just resets
the error code to 0 and executes the next statement. Very few things
cause SQLS to halt entirely. So you need to look at your stored
procedure code to analyze where things are breaking down. You can't
trap errors that don't get passed to your calling code.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 07 Oct 2003 11:17:48 -0700, Mansoor Shahi <(E-Mail Removed)>
wrote:

>Hi Mary,
>
>it is a big and complex stored procedure, that i have not written it. in
>this sp there is a transaction and on that there is some update command
>and after committing the transaction there is a select statement that
>select some fields from some tables. i can send it,if u want, but i
>think this is not helpful.
>
>Okey, return to my question, as i mentioned ago if i execute the sp by
>cm.EXECUTENONQUERY (cm is a SQLCOMMAND object) i get the error but if i
>replace EXECUTENONQUERY with EXECUTEREADER and run the project i never
>get the error.
>
>
>regards
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

Do you mean that i must execute sp twice?
first execute it by EXECUTEREADER and if any result set has returned
then execute it by EXECUTENONQUERY to trap the errors.

Note: if i write a simple sp contains for example an update command and
a select command (the update command raise a referential integrity
error) then i run this sp from VB by EXECUTEREADER or EXECUTENONQUERY
both of them will trap the error.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

No, I meant you have to go in and manually READ your stored procedure
code. Different things are happening based on how you are executing
the stored procedure from .NET, and I was attempting to explain why
the results are different for those different things. Some SQL
statements cause trappable errors, some do not, and a good stored
procedure writer will know the difference and add RAISERROR statements
where SQLS wouldn't necessarily return anything on its own. Also, the
presence of a SET NOCOUNT ON statement as the first line of code in
the sproc can also make a difference in what gets returned to the
client.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Wed, 08 Oct 2003 01:33:13 -0700, Mansoor Shahi <(E-Mail Removed)>
wrote:

>Do you mean that i must execute sp twice?
>first execute it by EXECUTEREADER and if any result set has returned
>then execute it by EXECUTENONQUERY to trap the errors.
>
>Note: if i write a simple sp contains for example an update command and
>a select command (the update command raise a referential integrity
>error) then i run this sp from VB by EXECUTEREADER or EXECUTENONQUERY
>both of them will trap the error.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!