Hi,
I have a stored procedure that returns an output parameter (Claim ID) after a new row is entered. I need to use this output later on so I am trying to store it in a hidden field. I originally did this through a function temporarily so I could see how things worked and try to figure out how to do it using the DAL/BL of DNN.
My Original test function went something like this: (edited for length)
Public Shared Function InsertMMSPreClaim(ByVal Adjuster_ID As Integer, ByVal Client_ID As Integer, ByVal LocationCode As String, _
ByVal DateOfLoss As DateTime, ByVal TimeOfLoss As String, ByVal DateEntered As DateTime, _
...) As Integer
Dim preClaimID As Integer
Dim myConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("connection").ConnectionString)
myConn.Open()
Dim myCommand As New SqlCommand("InsertInformation", myConn)
myCommand.CommandType = CommandType.StoredProcedure
Dim preClaimIDParam As New SqlParameter("@prmPreClaimID", SqlDbType.Int)
preClaimIDParam.Direction = ParameterDirection.Output
myCommand.Parameters.Add(New SqlParameter("@prmUser_ID", SqlDbType.Int))
myCommand.Parameters("@prmUser_ID").Value = Adjuster_ID
.
.
.
myCommand.Parameters.Add(New SqlParameter("@prmLocationCode", SqlDbType.NVarChar))
myCommand.Parameters("@prmLocationCode").Value = LocationCode
myCommand.Parameters.Add(preClaimIDParam)
Dim reader As SqlDataReader = myCommand.ExecuteReader()
preClaimID = Convert.ToInt32(preClaimIDParam.Value)
Return preClaimID
End Function
This function worked great, returned the value correctly and everything. I then tried to move this to using the DataProvider/SqlDataProvider in DNN. I made some changes that I thought were correct and when I enter the data, it always returns 0 as the output number. Does anyone know what I am doing wrong here? (Note: I took out a whole bunch of parameters to reduce the size of the code, but I dont think that should make a difference to the errors I am having, If you need all of the code let me know.)
Stored Procedure
INSERT INTO dbo.PreClaimTable
(Adjuster_ID, Client_ID, LostTime, LostTimeDays)
VALUES
(@prmUser_ID, @prmClient_ID, @prmLostTime, @prmLostTimeDays)
set @PreClaimID = SCOPE_IDENTITY()
SELECT @prmPreClaimID = @PreClaimID
RETURN @PreClaimID
Controller.vb
Public Function AddMedicalManagement(ByVal objMedicalManagement As MedicalManagementInfo) As Integer
DataProvider.Instance().AddMedicalManagement(objMedicalManagement.Adjuster_ID, objMedicalManagement.Client_ID, _
objMedicalManagement.LostTime, objMedicalManagement.LostTimeDays)
End Function
Data Provider
Public MustOverride Function AddMedicalManagement(ByVal Adjuster_ID As Integer, ByVal Client_ID As Integer, ByVal LostTime As String, _
ByVal LostTimeDays As String) As Integer
SQLDataProvider
Public Overrides Function AddMedicalManagement(ByVal Adjuster_ID As Integer, ByVal Client_ID As Integer, ByVal LostTime As String, _
ByVal LostTimeDays As String) As Integer
Dim sqlOutputParameter = New SqlParameter("@prmPreClaimID", SqlDbType.Int)
sqlOutputParameter.Direction = ParameterDirection.Output
Dim preClaimID As Integer
SqlHelper.ExecuteReader(claimsConnectionString, "spInsertMMSPreClaimInfo", Adjuster_ID, Client_ID, LostTime, LostTimeDays, sqlOutputParameter)
preClaimID = Convert.ToInt32(sqlOutputParameter.Value)
Return preClaimID
End Function