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?
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?
Can you change your store procedure to return the variables with a select statement instead of Output parameters?
Leon
Leon
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can't believe I didn't think to return all 3 values in one select...Thanks!
Sure, but why did you give me a "B"?
ASKER
Because I thought you gave me a good answer, better than average but not excellent. That's all. Again, Thank you.
Np, I will be sure ot avoid your questions in the future.
ASKER