Yippy Execute Returns RecordsAffected
Yup, here I am again!
I have just been converting a bunch of pages to use stored procedures and was wondering if there is anyway of ensuring that the updates occur correctly or catching errors.
I found a reference to "ioAction" and "ioMessage" parameters in an article on the 15Seconds. It says that these to parameters set with inputoutput will return a completion code and any message. Unfortunatly the code in the example links to an Oracle Database. I tryed to use the two parameters in Access and got to Nowheresville.
As as we speak I just discovered the RecordsAffected property retured by the Execute method ( A Long variable to which the provider returns the number of records that the operation affected). So I have sort of answered my own question but has any one heard of the ioAction or ioMessage parameters or anything else tricky like that in Access/SQL
Paul Curtis
Peter J. Boettcher posted this at 14:28 — 15th November 2001.
They have: 812 posts
Joined: Feb 2000
Paul,
Whenever I want to check the result of a stored procedure I usually use a "return" value or an "output" value. This return value could be the number of returned records, an error number, whatever you want. Here's a quick sample stored proc:
CREATE PROCEDURE ClienteleAdmin.Client_Site_Search_Inventory
(
@SPResult varchar(50) = NULL OUTPUT
)
AS
DECLARE @Records integer
SELECT @Records = ID FROM Sometable
IF @@ROWCOUNT > 0 @SPResult = "Found Records" ELSE @SPResult = "Found None"
RETURN
I just did a select because I didn't feel like typing to much, but the same methodology can be used for inserts/updates/deletes.
I have never used (or heard of) ioAction & ioMessage, I'll have to check out that article.
PJ | Are we there yet?
pjboettcher.com
PaulCAust posted this at 22:58 — 15th November 2001.
They have: 25 posts
Joined: Aug 2001
The 15Seconds Article
The article is titled Calling Stored Procedures From Active Server Pages (a very creative title I thought) by Al Hetzel.
URL: http://www.15seconds.com/Issue/000810.htm
Paul
Mark Hensler posted this at 07:18 — 16th November 2001.
He has: 4,048 posts
Joined: Aug 2000
This is what I found from the link above...
Set pAction = oCmd.CreateParameter("ioAction",131,3,50,0)
oCmd.Parameters.Append pAction
Set pMessage =
oCmd.CreateParameter("ioMessage",200,3,50,"")
oCmd.Parameters.Append pMessage
oCmd.Execute
if ( oCmd("ioAction") <> 0 ) then
Response.Write( oCmd("ioMessage") )
end if
That's all I can say. I haven't used ASP in about a year, so I haven't had any motivation to finish that book.
Mark Hensler
If there is no answer on Google, then there is no question.
Peter J. Boettcher posted this at 13:23 — 16th November 2001.
They have: 812 posts
Joined: Feb 2000
Yeah, those are just the variable names to pass the values between the ASP page and the stored procedure. So "ioAction" is the same as "SPResult" from my example.
PJ | Are we there yet?
pjboettcher.com
PaulCAust posted this at 22:39 — 18th November 2001.
They have: 25 posts
Joined: Aug 2001
Right OK. I will have to play around with this a bit. The RecordsAffected property retured by the Execute method seems to be giving me what I need within Access.
Thanks Guys.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.