p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

proasp_codeclinic thread: RE: Return value from Stored proc


Message #1 by dont worry <aspmailbox@y...> on Fri, 1 Mar 2002 08:37:09 -0800 (PST)
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1C307.3B0A9060
Content-Type: text/plain

> I read the Books online and SET NOCOUNT ON does NOT
> return the Number of affect rows information back.  

Yes it does. NOCOUNT sets whether or not you want to be told how many rows
were affected. If you have a proc like yours the first recordset returned
will contain the number of records affected. There is a question titled
"More than One Result Set" lower down the page in this article that talks
about this. It is talking about C++, but this is still true for VB/ASP etc.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/sqlqa.asp

> So, before it was sending two pieces of information,
> so why did that cause a problem?

As I said before it returned 2 recordsets.

> IF I was setting code to grab only rs("Progress")? 

ditto as above.

> Is it the formatting of info. sent back?  

No, see above. You need to use NextRecordset to access the "Progress" Field


> So with Set NOCOUNT ON, I'm only getting rs("Progress") back?

And any other fields you might add in. In this case it is only going to
return the 1 field.

> DO i need to SET NOCOUNT OFF at the end of the statement?

Probably a good idea. Procs can be written that depend on the values
returned from row counts. Depends on what your database is like. I forget
whether it is a Global setting, or just a setting inside the proc. But
either way it is probably a good idea.

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: dont worry [mailto:aspmailbox@y...]
Sent: Saturday, 2 March 2002 3:37 AM
To: Code Clinic
Subject: [proasp_codeclinic] RE: Return value from Stored proc


David,
I read the Books online and SET NOCOUNT ON does NOT
return the Number of affect rows information back.  

So, before it was sending two pieces of information,
so why did that cause a problem?IF I was setting code
to grab only rs("Progress")?  Is it the formatting of
info. sent back?  
So with Set NOCOUNT ON, I'm only getting
rs("Progress") back?
DO i need to SET NOCOUNT OFF at the end of the
statement?
(IT WORKED)
Thank you for all your help,
DW


--- David Cameron <dcameron@i...> wrote:
> Sorry I forgot to add something. Add in the row SET
> NOCOUNT ON. At the
> moment you will get 2 recordsets returned. 1 telling
> you how many rows were
> affected by the insert and the second one containing
> Progress = 0. It is
> generally a good idea to add this into all procs.
> 
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
> 
> -----Original Message-----
> From: dont worry [mailto:aspmailbox@y...]
> Sent: Friday, 1 March 2002 3:29 AM
> To: Code Clinic
> Subject: [proasp_codeclinic] RE: Return value from
> Stored proc
> 
> 
> Tried it with the following
> Progress = rs("Progress")
> and it didn't work.
> DW
> --- David Cameron <dcameron@i...> wrote:
> > Try
> > 
> > SELECT 0 AS Progress
> > 
> > regards
> > David Cameron
> > nOw.b2b
> > dcameron@i...
> > 
> > -----Original Message-----
> > From: dont worry [mailto:aspmailbox@y...]
> > Sent: Wednesday, 27 February 2002 10:33 AM
> > To: Code Clinic
> > Subject: [proasp_codeclinic] Return value from
> > Stored proc
> > 
> > 
> > I get the following error from my stored proc.
> > "Item cannot be found in the collection
> > corresponding
> > to the requested name or ordinal"
> > 
> > 'NOTE, I want to return the Progress=0 at the end
> of
> > the statement using Select Progress=0  I do NOT
> have
> > a
> > field in the table named Progress , but want to
> use
> > that to tell me it was successful.  This is
> causing
> > the problem. why isn't it working. 
> > 
> > CREATE PROCEDURE [IOSD_QUESTIONNAIRE_INSERT]
> > 	(@Gender_1 	[nvarchar](10),
> > 	 @Age_2 	[int])
> > 
> > AS INSERT INTO [CCA].[dbo].[IOSD_QUESTIONNAIRE] 
> > 
> > 	 ( [Gender],
> > 	 [Age])
> > VALUES 
> > 	( @Gender_1,
> > 	 @Age_2)
> > 	Select Progress=0	
> > RETURN
> > GO
> > 
> > 
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Greetings - Send FREE e-cards for every
> > occasion!
> > http://greetings.yahoo.com
> > 
> > $subst('Email.Unsub').
> > 
> > 
> > $subst('Email.Unsub').
> > 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Greetings - Send FREE e-cards for every
> occasion!
> http://greetings.yahoo.com
> 
> $subst('Email.Unsub').
> 
> 
> $subst('Email.Unsub').
> 


__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com

$subst('Email.Unsub').


  Return to Index