Stored Procedure (output variable) Problem

[stingers]

Ok here's the problem. I am a beginner SQL programmer and am having trouble getting this stored procedure to work. There is a bunch more code but I have only pasted the part giving me thus far. When I run the procedure in Query Editor and supply the same values for the parameters that the ACCESS CODE below passes. The problem is that when I pass the value for lngLnDataConcurrencyID to SQL it is '6' as an example, but when it comes back (where i have indicated the problem) it is 0? The wierd thing is that when I put the same BDstudentID '2' into the query analyzer and the same lnglndataconcurrencyid as the access routine passes, '6' it does not return the error. Where can i look to find the problem?

in the table tblLOANDATA BDStudentID is an int field and LnDataConcurrencyID is an Int field

I am sure the access routine is psing the parameters i want to SQL but then something goes wrong.

I have burned two days on this already PLEASE HELP

Thanks in advance

Ryan Paul
:-(

******************************
******************************
STORED PROCEDURE
******************************
******************************



cREATE PROCEDURE procFrmCollectionUpdate(
@BDStudentID int = null,
@IAC bit = NULL,
@PDC varchar(50) = null,
@qckComment varchar(1000) = NULL,
@CollectionCode varchar(50) = null,
@RetCode int = NULL OUTPUT,
@LnDataConcurrencyID int = null OUTPUT,
@RetMsg varchar(100) = NULL OUTPUT)
AS
SET NOCOUNT ON
DECLARE @SQLBASE varchar(200)
DECLARE @SQLComplete varchar(200)
DECLARE @CHECKLOAN int

/* Use LnDataConcurrencyID to check
whether the loan has changed
since it was retrieved. */
SELECT @CheckLoan = LnDataConcurrencyID FROM tblLoanData
WHERE BDStudentID = @BDStudentID

IF @CheckLoan <> @LnDataConcurrencyID
BEGIN
SELECT @LnDataConcurrencyID = @CheckLoan,
@RetCode = 0,
@RetMsg = 'Another user updated this Loan ' +
'while you were editing it. ' + char(10) +
' LnDConID ' + CONVERT(VarChar, @LnDataConcurrencyID) + char(10) +
' CkLn ' + CONVERT(VarChar, @CheckLoan) + char (10) +
' BDstudentID ' + CONVERT(VarChar, @BDStudentID)
RETURN
END

GO



******************************
*****************************
ACCESS CODE
******************************
******************************



Private Sub cmdSave_Click()

'****************************************
'****************************************
'****************************************
'save the changes
Dim lngLnDataConcurrencyID As Long
Dim fOK As Boolean
Dim strMsg As String
Dim strOK As String
Dim cmd As ADODB.Command
Dim rstDetails As ADODB.Recordset
Dim strDetails As String
Dim strCkLoan As String

On Error GoTo HandleErr
' Validate the connection
If Not OpenConnection() Then
MsgBox &quot;Unable to connect to SQL Server.&quot;, , &quot;Login Required&quot;
Forms!frmlogin.Visible = True
GoTo ExitHere
End If
' In case this takes a while,
' put up an hourglass,
' and prevent the user from clicking
' save again or cancel while it's running.
DoCmd.Hourglass True

' Edit order
lngLnDataConcurrencyID = Me.LnDataConcurrencyID
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = gcnn
.CommandText = &quot;procFrmCollectionUpdate&quot;
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter( _
&quot;RETURN_VALUE&quot;, adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter( _
&quot;@BDStudentID&quot;, adInteger, adParamInput, , Me.bdStudentID)
.Parameters.Append .CreateParameter( _
&quot;@IAC&quot;, adBoolean, adParamInput, , Me.IAC)
.Parameters.Append .CreateParameter( _
&quot;@PDC&quot;, adVarChar, adParamInput, 50, Me.PDC)
.Parameters.Append .CreateParameter( _
&quot;@qckComment&quot;, adVarChar, adParamInput, 1000, Me.qckComment)
.Parameters.Append .CreateParameter( _
&quot;@CollectionCode&quot;, adVarChar, adParamInput, 50, Me.CollectionCode)
.Parameters.Append .CreateParameter( _
&quot;@LnDataConcurrencyID&quot;, adInteger, adParamInputOutput, , lngLnDataConcurrencyID)
.Parameters.Append .CreateParameter( _
&quot;@RetCode&quot;, adInteger, adParamOutput)
.Parameters.Append .CreateParameter( _
&quot;@RetMsg&quot;, adVarChar, adParamOutput, 100)

.Execute

fOK = .Parameters(&quot;@RetCode&quot
strMsg = .Parameters(&quot;@RetMsg&quot

'****** MsgBox strMsg
->PROBLEM lngLnDataConcurrencyID = .Parameters(&quot;@LnDataConcurrencyID&quot
'****** End With

' Process results
If lngLnDataConcurrencyID <> Me.LnDataConcurrencyID Then
' A write-after-write conflict happened here
strMsg = strMsg & _
&quot;Order was changed by another user while you were editing it.&quot; & _
vbCrLf & &quot;Your changes will overwrite the &quot; & _
&quot;other changes if you click Save again. Click Cancel to see the other user's changes.&quot;
fOK = False
' Update ConcurrencyID
Me.LnDataConcurrencyID = lngLnDataConcurrencyID
Else
' Update succeeded; increment ConcurrencyID
Me.LnDataConcurrencyID = lngLnDataConcurrencyID + 1
End If
'End If
Set cmd = Nothing
If fOK Then
Me.IsDirty = False
'Me.IsNew = False
Else
'Me.cmdSave.Enabled = True
'Me.cmdCancel.Enabled = True
End If
DoCmd.Hourglass False
MsgBox strMsg, , &quot;Save&quot;

'****************************************
'****************************************
'****************************************





ExitHere:
DoCmd.Hourglass False
Exit Sub

HandleErr:
MsgBox Err & &quot;: &quot; & Err.Description, , &quot;cmdSave error&quot;
Resume ExitHere
Resume
End Sub