How can I capture multiple output from a storded procedure in my VB6 application?

Hi Experts -

How can I capture multiple output from a stored procedure in my VB6 application?  This is the code I'm playing with.  I get a runtime error on the rstX.Open line -> Syntax error or access violation.  

Private Sub Command1_Click()

Dim cnxX As ADODB.Connection
Dim comX As ADODB.Command
Dim rstX As ADODB.Recordset
Dim strSQL As String

strSQL = ""
strSQL = "@OPEN MONEY, @USED MONEY, @AVAILABLE MONEY" & vbCrLf
strSQL = strSQL & "EXEC <dbname>..sb '04F', 1000406, @OPEN OUTPUT, @USED OUTPUT, @AVAILABLE OUTPUT " & vbCrLf
strSQL = strSQL & "SELECT @OPEN AS SB_OPENED, @USED AS SB_USED, @AVAILABLE AS SB_AVAIL"

Set cnxX = New ADODB.Connection
cnxX.ConnectionString = "Provider=SQLOLEDB.1;Data Source=" & strS & ";Persist Security Info=False;Password=" & strP & ";User ID=" & strU & ";Initial Catalog=tempdb"
cnxX.ConnectionTimeout = 30
cnxX.Open

Set comX = New ADODB.Command
comX.ActiveConnection = cnxX

Set rstX = New ADODB.Recordset
rstX.Open strSQL, comX.ActiveConnection, , , adCmdStoredProc
 
Do While Not rstX.EOF
    Debug.Print rstX.Fields(0).Value '@OPEN
    Debug.Print rstX.Fields(1).Value '@USED
    Debug.Print rstX.Fields(2).Value '@AVAILABLE
    rstX.MoveNext
Loop

rstX.Close
Set rstX = Nothing

Set comSAO = Nothing

cnxX.Close
Set cnxX = Nothing

End Sub

FYI:

1. '04F'  and 1000406 are parameters I'm  passing in

2.  @OPEN, @USED, @AVAILABLE are the output from the stored procedure.  They are not table fields, rather they are the result of several calculations from tables.

3.  If I copy & paste strSQL into my SQL query tool, it executes and returns the output with no problem.  So the SQL works but how can I get it to work with my VB code?
MariaHaltAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MariaHaltAuthor Commented:
Experts, I'm increasing the point value to get some responses.  If my question is unclear please let me know.  Please help!
0
leonstrykerCommented:
Can you change your store procedure to return the variables with a select statement instead of Output parameters?

Leon
0
MariaHaltAuthor Commented:
Not sure I understand...I'm not inserting the data into any existing tables and can not create a temp table for each user accessing it...but if you want to check out my sp...here it is....

CREATE PROC sb @TERM VARCHAR(6),
                          @ID INT,
                          @SB_OPEN MONEY OUTPUT,
                          @SB_USED MONEY OUTPUT,
                          @SB_BALANCE MONEY OUTPUT AS

SET NOCOUNT ON

DECLARE      @SB_NONTAXABLE_TOTAL MONEY,
      @SB_TAXABLE_TOTAL MONEY,
      @SB_CASH_SPLIT MONEY,
      @SB_TOTAL MONEY,
      @SB MONEY,
      @SB_AVAILABLE MONEY

/* Determine sum of SB transactions where tax was not charged */
SELECT @SB_NONTAXABLE_TOTAL = 0
SELECT @SB_NONTAXABLE_TOTAL = <query goes here>

/* Determine sum of SB transactions where tax was charged */
SELECT @SB_TAXABLE_TOTAL = 0
SELECT @SB_TAXABLE_TOTAL = <query goes here>

/* Determine sum of cash splits */
SELECT @SB_CASH_SPLIT = 0
SELECT @SB_CASH_SPLIT = <query goes here>

SELECT @SB_TOTAL = 0
SELECT @SB_TOTAL = @SB_NONTAXABLE_TOTAL + @SB_TAXABLE_TOTAL - @SB_CASH_SPLIT

SELECT @SB = 0
SELECT @SB = <query goes here>

SELECT @SB_AVAILABLE = 0
SELECT @SB_AVAILABLE = @SB - @SB_TOTAL

/*Return opening amount */
SELECT @SB_OPEN = @SB

/*Return amount used */
SELECT @SB_USED = @SB_TOTAL

/*Return balance */
SELECT @SB_BALANCE = @SB_AVAILABLE

Looking forward to your response.


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

leonstrykerCommented:
Try this:

CREATE PROC sb @TERM VARCHAR(6),
                          @ID INT
AS

SET NOCOUNT ON

DECLARE     @SB_NONTAXABLE_TOTAL MONEY,
     @SB_TAXABLE_TOTAL MONEY,
     @SB_CASH_SPLIT MONEY,
     @SB_TOTAL MONEY,
     @SB MONEY,
     @SB_AVAILABLE MONEY

/* Determine sum of SB transactions where tax was not charged */
SELECT @SB_NONTAXABLE_TOTAL = 0
SELECT @SB_NONTAXABLE_TOTAL = <query goes here>

/* Determine sum of SB transactions where tax was charged */
SELECT @SB_TAXABLE_TOTAL = 0
SELECT @SB_TAXABLE_TOTAL = <query goes here>

/* Determine sum of cash splits */
SELECT @SB_CASH_SPLIT = 0
SELECT @SB_CASH_SPLIT = <query goes here>

SELECT @SB_TOTAL = 0
SELECT @SB_TOTAL = @SB_NONTAXABLE_TOTAL + @SB_TAXABLE_TOTAL - @SB_CASH_SPLIT

SELECT @SB = 0
SELECT @SB = <query goes here>

SELECT @SB_AVAILABLE = 0
SELECT @SB_AVAILABLE = @SB - @SB_TOTAL

/*Return opening amount, amount used,  balance  */
SELECT @SB, @SB_TOTAL, @SB_AVAILABLE

Private Sub Command1_Click()

Dim cnxX As ADODB.Connection
Dim rstX As ADODB.Recordset
Dim strSQL As String

strSQL = "EXEC <dbname>..sb '04F', 1000406

Set cnxX = New ADODB.Connection
cnxX.ConnectionString = "Provider=SQLOLEDB.1;Data Source=" & strS & ";Persist Security Info=False;Password=" & strP & ";User ID=" & strU & ";Initial Catalog=tempdb"
cnxX.ConnectionTimeout = 30
cnxX.Open

Set rstX = New ADODB.Recordset
rstX.Open strSQL, cnxX, adOpenStatic, adLockOptimistic
 

Debug.Print rstX.Fields(0).Value ' OPEN
Debug.Print rstX.Fields(1).Value ' USED
Debug.Print rstX.Fields(2).Value ' AVAILABLE

rstX.Close
Set rstX = Nothing
cnxX.Close
Set cnxX = Nothing

End Sub


Leon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MariaHaltAuthor Commented:
Can't believe I didn't think to return all 3 values in one select...Thanks!
0
leonstrykerCommented:
Sure, but why did you give me a "B"?
0
MariaHaltAuthor Commented:
Because I thought you gave me a good answer, better than average but not excellent.  That's all.  Again, Thank you.
0
leonstrykerCommented:
Np, I will be sure ot avoid your questions in the future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.