Help please,

Am having failures in receiving output parameter from sp_execute sql.

Here's the vb code:

sql_statement_for_summmation = "select sum(amount) from cdv_dtl "

Dim tempcom As ADODB.Command
Set tempcom = New ADODB.Command

   With tempcom
    .ActiveConnection = globalconnection
    .CommandText = "get_auto_no_sp_tran_compute_detail_total"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@fieldfilter", adVarChar, adParamInput, 100, sql_statement_for_summmation)
    .Parameters.Append .CreateParameter("@lastno", adVarChar, adParamOutput, 10, "0")
    .Execute
   End With


MsgBox tempcom.Parameters(1).Value


here's the stored procedure:

CREATE PROCEDURE get_auto_no_sp_tran_compute_detail_total

@passparam varchar(1000) ,
@lastno varchar(10) output

AS
DECLARE @SQLString NVARCHAR(1500)
DECLARE @ParmDefinition NVARCHAR(1500)
declare @IntVariable int
declare @lastno2 varchar(10)

SET @SQLString =@passparam  
SET @ParmDefinition = N' @lastno2 varchar(30) output  '
SET @IntVariable = 100

EXECUTE  sp_executesql @SQLString , @ParmDefinition,  @IntVariable


select @lastno=(select @lastno2)

Help please

Where are you getting the error? and what error?

try this:

sql_statement_for_summmation = "select sum(amount) from cdv_dtl " Dim tempcom As ADODB.Command Set tempcom = New ADODB.Command    With tempcom     .ActiveConnection = globalconnection     .CommandText = "get_auto_no_sp_tran_compute_detail_total"     .CommandType = adCmdStoredProc     .Parameters(0).Value=sql_statement_for_summmation     .Execute    End With MsgBox tempcom.Parameters(1).Value

____________________________
AndreaVB

Hi,

Example on

Executing a Stored Procedure  having INPUT and OUTPUT parameter  using a Command object.


Stored procedures may contain output parameters and return values. For example, the procedure GetAccountName, can return the name of the Account for an Account Code passed as a parameter. First, create the stored procedure and then write code in the frmAccountDetails form.

Create Procedure GatAccountName
@AccCode varchar(6),
@AccName varchar(30) OUTPUT
As

SELECT  ,@AccName=AccountName FROM AccountsTable WHERE AccountCode =@AccCode
Go


The following code shows how to retrieve the output parameter from a stored procedure.

Option Explicit
Dim CN As Connection
Dim comobj As Command
Dim prmByAcc As Parameter
Dim strAcc As String

Private Sub Form_Load()
Set CN = New Connection
Set comobj = New Command

With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open
End With

With comobj
.ActiveConnection = CN
.CommandText = “GetAccountName”
.CommandType = adCmdStoredProc
End With

strAcc = Trim(InputBox(“Enter Customer Code.:”))
Set prmByAcc = comobj.CreateParameter(“ACode”, adBSTR, adParamInput, 6, strAcc)
comobj.Parameters.Append prmByAcc

Set prmByAcc = comobj.CreateParameter(“AName”, adBSTR, adParamOutput, 30)
comobj.Parameters.Append prmByAcc
comobj.Execute
MsgBox comobj.Parameters(“AName”)
End Sub