Solved

How to have a stored procedure in Interbase return a result when invoking ExecuteNonQuery in ASP.Net

Posted on 2006-07-04
5
556 Views
Last Modified: 2013-12-09
When using ASP.Net components and using a Command's method ExecuteNonQuery, with an SQL string like
INSERT INTO bla bla I get an integer as a result, this usually is the number of records affected.

All is fine with this.
Now - I want to put this INSERT etc. into a stored procedure. And I have successfully done so, all works.
The problem is that now I get 0 - a zero as a result, when invoking ExecuteNonQuery.

So - what kind of syntax to use in the procedure to have it return an integer of my desire?
the RETURNS(bla bla) is not the answer (or probably not) I am after, please don't confuse this with my question, because I already know how to retrieve that kind of return values.

--
Ingvarius
0
Comment
Question by:IngvarNilsen
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
are you using interbase or firebird? what version?
0
 

Author Comment

by:IngvarNilsen
Comment Utility
I am using Firebird 1.5.3.4870
But I am not tied to it, in case a newer stable build is available, I might upgrade.
(This is a new project)

--
Ingvarius
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
firebird 1.5 is fine for this, anything earlier doesn't have this. You want the context variable ROW_COUNT

INSERT INTO ......
return_param = ROW_COUNT;
0
 

Author Comment

by:IngvarNilsen
Comment Utility

Ok, I am almost there.
To get the result returned from the ADO.Net components, I need to do it this way:

For "INSERT INTO bla bla" I must use ExecuteNonQuery
For "EXECUTE PROCEDURE FOO_INSERT bla bla" I must first use your solution and then use ExecuteScalar

What I wanted was to still use ExecuteNonQuery with the stored procedure, but this always returns 0
So, when there is a context variable like ROW_COUNT, could there be a context variable for the returning result?
Since "INSERT INTO.." do return a usable result, why can't a stored procedure do the same?

If you did not understand this, please tell me and I will try to explain.
And please note - I am not absolutely sure about the differences between ExecuteScalar and ExecuteNonQuery.
And BTW, finally. ExecuteScalar does NOT return the rows affected when using it with "INSERT INTO.." :-)

--
Ingvarius
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 total points
Comment Utility
what you can do is define your SP as returning a value, say rcount, set rcount = ROW_COUNT inside the SP
ensure the SP then has a SUSPEND and then you can call it as "select rcount from mysp (val1, val2)"
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now