Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2006-07-04
5
560 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
ID: 17038889
are you using interbase or firebird? what version?
0
 

Author Comment

by:IngvarNilsen
ID: 17038896
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
ID: 17040413
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
ID: 17040787

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
ID: 17061369
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…

860 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