Hi All
Im having trouble retrieving a output parameter. I have a function in a class file that exec a SP which passes a bunch of params to the SP which creates a record in the DB. It also returns a output parameter which i need to retrieve and assign to a querystring so i can use it on another webpage. my current code passes the params and the record is created in the db, i can retrieve the output param and assign to a integer variable in the class file. when i try and have the value of the variable passed to another variable on the code behind of the page where i call the SP the value gets reset to 0. Im not sure where im going wrong and any help would be appreciated. see code below. thanks
Brandon
function in Class file
Code behind of page
Im having trouble retrieving a output parameter. I have a function in a class file that exec a SP which passes a bunch of params to the SP which creates a record in the DB. It also returns a output parameter which i need to retrieve and assign to a querystring so i can use it on another webpage. my current code passes the params and the record is created in the db, i can retrieve the output param and assign to a integer variable in the class file. when i try and have the value of the variable passed to another variable on the code behind of the page where i call the SP the value gets reset to 0. Im not sure where im going wrong and any help would be appreciated. see code below. thanks
Brandon
function in Class file
VB.NET:
Public Shared Function ExecStoreProcspAddTenancyInstruction(ByVal retval)
Dim Centre As String
Dim NewCentre
Dim Unit As String
Dim NewUnit As String
Dim DateTenancyInstruction
Dim LeaseStatus
Dim EmployeeID As String
Dim IsProject As String
Dim NewIsProject
'Dim p7 As Integer
Dim Project
Dim tmpProject As Guid
Dim sqlconnExecStoreProc As SqlConnection
Dim cmd As New SqlCommand()
Dim Parameter As New SqlParameter
' Retrieve Parameter information
Centre = SSD.getCentre()
NewCentre = Convert.ToInt32(Centre)
Unit = SSD.getUnit().ToString
NewUnit = Trim(Unit)
DateTenancyInstruction = SSD.getDateTenancyInstruction()
LeaseStatus = 2
EmployeeID = "StevensBr"
IsProject = SSD.getIsProject()
NewIsProject = Convert.ToChar(IsProject)
If SSD.getProject().ToString = "default" Then
Project = DBNull.Value
Else
tmpProject = New Guid(SSD.getProject().ToString)
Project = tmpProject
End If
' Get connection string
sqlconnExecStoreProc = New SqlConnection(SSD.GetConnectionString())
cmd.CommandText = "sp_AddTenancyInstruction"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlconnExecStoreProc
' Create Parameters
cmd.Parameters.Add("@FKCentre", SqlDbType.Int)
cmd.Parameters.Add("@FKUnit", SqlDbType.VarChar)
cmd.Parameters.Add("@DateTenancyInstruction", SqlDbType.VarChar)
cmd.Parameters.Add("@FKLeaseStatus", SqlDbType.Int)
cmd.Parameters.Add("@EmployeeID", SqlDbType.VarChar)
cmd.Parameters.Add("@IsProject", SqlDbType.Char)
Dim retValParam As New SqlParameter("@PrimaryKeyOut", SqlDbType.Int)
retValParam.Direction = ParameterDirection.Output
cmd.Parameters.Add(retValParam)
cmd.Parameters.Add("@Project", SqlDbType.UniqueIdentifier)
' Pass collected information to the created parameters
cmd.Parameters("@FKCentre").Value = NewCentre
cmd.Parameters("@FKUnit").Value = NewUnit
cmd.Parameters("@DateTenancyInstruction").Value = DateTenancyInstruction
cmd.Parameters("@FKLeaseStatus").Value = LeaseStatus
cmd.Parameters("@EmployeeID").Value = EmployeeID
cmd.Parameters("@IsProject").Value = NewIsProject
cmd.Parameters("@Project").Value = Project
' Open Connection
sqlconnExecStoreProc.Open()
cmd.ExecuteNonQuery()
'Dim reader As SqlDataReader = cmd.ExecuteReader()
'Now you can grab the output parameter's value...
Dim retVal As Integer
retVal = retValParam.Value
' Close Connection
sqlconnExecStoreProc.Close()
' Return dataset
Return retVal
End FunctionCode behind of page
VB.NET:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim IssuedBy As String
Dim CentreName As String
Dim TenancyInstructionReference As String
Dim DateTenancyInstruction As Date
Dim UnitNumber As String
Dim ShopType As String
Dim retval As Integer
Dim ds As New DataSet
' Call first store procedure which will create the record in the database
SSD.ExecStoreProcspAddTenancyInstruction(retval)
