IngvarNilsen
asked on
How to have a stored procedure in Interbase return a result when invoking ExecuteNonQuery in ASP.Net
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
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
Nick Upson
are you using interbase or firebird? what version?
ASKER
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
But I am not tied to it, in case a newer stable build is available, I might upgrade.
(This is a new project)
--
Ingvarius
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;
INSERT INTO ......
return_param = ROW_COUNT;
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.