?
Solved

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

Posted on 2004-11-19
8
Medium Priority
?
205 Views
Last Modified: 2010-05-02
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?
0
Comment
Question by:MariaHalt
  • 4
  • 4
8 Comments
 

Author Comment

by:MariaHalt
ID: 12629713
Experts, I'm increasing the point value to get some responses.  If my question is unclear please let me know.  Please help!
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12630100
Can you change your store procedure to return the variables with a select statement instead of Output parameters?

Leon
0
 

Author Comment

by:MariaHalt
ID: 12630221
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Accepted Solution

by:
leonstryker earned 1500 total points
ID: 12630325
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
 

Author Comment

by:MariaHalt
ID: 12710145
Can't believe I didn't think to return all 3 values in one select...Thanks!
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12712756
Sure, but why did you give me a "B"?
0
 

Author Comment

by:MariaHalt
ID: 12715418
Because I thought you gave me a good answer, better than average but not excellent.  That's all.  Again, Thank you.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12716602
Np, I will be sure ot avoid your questions in the future.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question