output parameters

[James Brett]

Hi

I've got this function that executes a stored procedure. One of the
parameters is an output parameter but for some reason it always returns 0.

Any clues?

Cheers
James


Public Shared Sub StoredProcExecuteNonQuery(ByVal strSP As String, ByVal
ParamArray aryParams() As SqlParameter)

'connection obj

Dim objConn As SqlConnection

objConn = DBConnect()

'create the command obj

Dim objCMD As New SqlCommand

objCMD.CommandText = strSP

objCMD.Connection = objConn

objCMD.CommandTimeout = 1440

objCMD.CommandType = CommandType.StoredProcedure

'parameters

Dim Ct As Integer

For Ct = 0 To UBound(aryParams)

If aryParams(Ct).ParameterName <> "" Then

Dim cmdParam As New SqlParameter

cmdParam.ParameterName = aryParams(Ct).ParameterName

cmdParam.SqlDbType = aryParams(Ct).SqlDbType

cmdParam.Direction = aryParams(Ct).Direction

cmdParam.Value = aryParams(Ct).Value

objCMD.Parameters.Add(cmdParam)

End If

Next

'create and exec the data reader

Try

objCMD.ExecuteNonQuery()

objConn.Close()

Catch ex As Exception

End Try

End Sub



I call this by

Dim sqlDatasetCode As New SqlParameter("@code", sCode)

Dim sqlDatasetID As New SqlParameter

sqlDatasetID.ParameterName = "@dataset"

sqlDatasetID.SqlDbType = SqlDbType.Int

sqlDatasetID.Size = 4

sqlDatasetID.Direction = ParameterDirection.Output

Database.StoredProcExecuteNonQuery("lookup_dataset_by_code", sqlDatasetCode,
sqlDatasetID)

If IsDBNull(sqlDatasetID.Value) = False Then

Return sqlDatasetID.Value

Else

Return 0

End If

 

[Ben Lucas]

Can you post the stored procedure too?

 

[James Brett]

CREATE PROCEDURE [lookup_dataset_by_code]
@code VARCHAR(16),
@dataset INT OUTPUT
AS

SELECT @dataset = p_dataset
FROM dataset
WHERE code = @code
GO

cheers j

 

[Ben Lucas]

In you procedure StoredProcExecuteNonQuery( ) you are making a copy of the
parameter that you sent in, and you pass the copy to the command object.
Thus, the parameter you are using will not have the data since it is not
used in the execution.

In order for that parameter to have the output value, you would have to use
that specific object. For example:


--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

CREATE PROCEDURE [lookup_dataset_by_code]
@code VARCHAR(16),
@dataset INT OUTPUT
AS

SELECT @dataset = p_dataset
FROM dataset
WHERE code = @code
GO

cheers j

Can you post the stored procedure too?

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

returns

0.