[thelist] Calling a sproc from VB

[Ken Schaefer]

a) You can return multiple result sets from a sproc, if you want, provided
your data access technology supports it. If you are using ADO in your VB
App, you would use:

' First recordset
Set objRS = objCommand.Execute

' Second recordset
Set objRS = objRS.NextRecordSet

to get to the next resultset

b) Have you tried checking the Return Value from the sproc? (not your custom
output parameter). On a side note: my personal feeling is that when an error
condition is returned, you want to set a custom return value, rollback the
transaction and return your custom error value to your calling app. However
you may have different requirements here which explain why you're doing it
the way you are.

c) If you are returning any resultsets to your VB application, then your
output parameters will *not* be available until you have closed the
resultset. Using SET NOCOUNT ON (for example) can be used to supress
spurious resultsets like "xx records affected". You could use SQL Server's
Profiler tool to see what's being returned from SQL Server to your callling
app.

d) If all else fails, try to simplify the sproc, and the calling code, and
post it here for us to have a look at.

Cheers
Ken


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Tab Alleman" <Tab.Alleman at MetroGuide.com>
Subject: RE: [thelist] Calling a sproc from VB


Anthony Baratta wrote:
> At 11:40 AM 2/4/2004, Tab Alleman wrote:

>> And a fatal error occurred, would I get to see what the error was?
>> Obviously it wouldn't be in my OUTPUT parameter.  Where would it be?
>> How can I get my VB app to spit it out?
>
> Like I said previously I don't use output parameters. By returning the
> error info via the Select statement it works just like getting
> data/rowset from the database.

That's the part I don't know.  The only way I know how to get Output
from a sproc is through OUTPUT parameters.

If I do several SELECTS in the sproc that create ResultSets which would
normally get printed in the ResultPane of QA, how could I access those
ResultSets in a VB App that called the Sproc?  Or can you only get one
ResultSet per sproc that way?