how can i get the result set from an stored procedure

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

I have a stored procedure which is used for performing some operation and execute a query. i want that result set back in vb.

-- Anonymous, January 12, 2005

Answers

Malik,

Here is an example of code that runs a stored procedure that returns a value.

Hope this helps,

Eric

==========

Public Function GetRemainingBalance(ByVal lngReceivableId As Integer) As Decimal

' Create command objects

cmd = New ADODB.Command

Dim cnLink As DataAccess.DataLink

cnLink = New DataAccess.DataLink

' Set command properties

cmd.ActiveConnection = cnLink.GetDataLink

cmd.CommandText = "p_receivable_get_balance_by_id"

cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

params = cmd.Parameters

' Define stored procedure params and append to command.

params.Append(cmd.CreateParameter("@RETURN_VALUE", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamReturnValue, 0))

params.Append(cmd.CreateParameter("@rcvbl_id", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 0))

params.Append(cmd.CreateParameter("@receivable_balance_amt", ADODB.DataTypeEnum.adCurrency, ADODB.ParameterDirectionEnum.adParamOutput, 0))

' Specify input parameter values

params("@rcvbl_id").Value = lngReceivableId

' Execute the command

cmd.Execute(lngRecordsAffected, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)

' Retrieve stored procedure return value and output parameters

GetRemainingBalance = params("@receivable_balance_amt").Value

GetRemainingBalanceExit:

cnLink = Nothing

Exit Function

End Function

-- Anonymous, January 24, 2005


Moderation questions? read the FAQ