Yippy Execute Returns RecordsAffected

They have: 25 posts

Joined: Aug 2001

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's picture

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

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's picture

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
'I looked up CreateParameter() in an ASP book I have, and it was the next page after my bookmark. Sad From what I can tell, there is nothing special about ioAction and ioMessage. They are simply the names assigned to those parameters.

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. Roll eyes

Mark Hensler
If there is no answer on Google, then there is no question.

Peter J. Boettcher's picture

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

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.