?
Solved

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

Posted on 2006-07-04
5
Medium Priority
?
567 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
[X]
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
  • 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 1500 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…

800 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