[thelist] Calling a sproc from VB

Tab Alleman Tab.Alleman at MetroGuide.com
Thu Feb 5 08:32:12 CST 2004

Ken Schaefer wrote:
> ' First recordset
> Set objRS = objCommand.Execute
> ' Second recordset
> Set objRS = objRS.NextRecordSet
> to get to the next resultset

AHA!  This is going to help a lot.

> b) Have you tried checking the Return Value from the sproc? 

That's what I wasn't able to do because I was getting type mismatch
errors in the VB app, trying to assign the return value to a string
>  Using SET NOCOUNT ON (for example) can be used to
> supress spurious resultsets like "xx records affected". 

Uh-huh, this was part of the AHA moment here.  I added SET NOCOUNT ON
and my code worked like magic.  I hadn't realized that all those "xx
records affected" results were actually *stacking up* in the return
value as multiple result sets.  I thought maybe just the last resultset
(or specified RETURN value) would be available.  That's why I couldn't
force the return value into a string variable.  

I originally had NOCOUNT OFF because I use @@ROWCOUNT in the sproc.
According to BOL, @@ROWCOUNT should still work even with NOCOUNT ON, but
my results seem to indicate otherwise.  However, maybe my implementation
is flawed.  (ya think maybe?..hehe)  I'll be checking into that today.

Big Thanks!

The contents of this email and any attachments are confidential.
It is intended for the named recipient(s) only.
If you have received this email in error please notify the system manager or  the 
sender immediately and do not disclose the contents to anyone or make copies.

** eSafe scanned this email for viruses, vandals and malicious content **

More information about the thelist mailing list