I have the following stored procedure working with an Access 2000 front end. The output parameters returned to Access are both Null when the record is successfully updated (ie when @.@.Rowcount = 1), but the correct parameters are returned when the update fails. I'm a bit new to using output parameters, but I have them working perfectly with an insert sproc, and they look basically the same. What bonehead error have I made here? The fact that the record is updated indicates to me that the Commit Trans line is being executed, so why aren't the 2 output parameters set?
TIA
EDIT: Solved, sort of. I found that dropping the "@.ResNum +" from "@.ResNum + ' Updated'" resolved the problem (@.ResNum is an input parameter). This implies that the variable lost its value between the SQL statement and the If/Then, since the SQL correctly updates only the appropriate record from the WHERE clause. Is this supposed to happen? I looked in BOL, and if it's addressed there I missed it.
CREATE PROCEDURE [procResUpdate]
Various input parameters here,
@.RetCode as int Output, @.RetResNum as nvarchar(15) Output
AS
Declare @.RowCounter int
Begin Tran
UPDATE tblReservations
SET Various set statements here, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified
SELECT @.RowCounter = @.@.ROWCOUNT
If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = @.ResNum + ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = 'Update Failed'
End
GOPost the complete DDL for the proc (including @.ResNum) and a sample of calling it that produces the problem you describe.
Regards,
hmscott|||Here's the complete sproc (I added back the offending part in red):
CREATE PROCEDURE [procResUpdate]
@.ReqDate as datetime, @.PassName as nvarchar(25), @.DispDate as datetime, @.PassPhone as nvarchar(25),
@.PassQuant as smallint, @.AuthBy as nvarchar(25), @.AcctID as smallint, @.PuLandmark as smallint, @.PuStreet as nvarchar(50),
@.PuCity as smallint, @.PuXStreet as nvarchar(50), @.PuPoint as nvarchar(50), @.DestLandmark as smallint, @.DestStreet as nvarchar(50),
@.DestCity as smallint, @.DestXStreet as nvarchar(50), @.DestPoint as nvarchar(50), @.Operator as smallint, @.Comments as nvarchar(250),
@.DriverReq as nvarchar(25), @.CarType as smallint, @.EstHours as money, @.EstPrice as money, @.CCType as nvarchar(25), @.CCnum as nvarchar(25),
@.CCAuthNum as nvarchar(25), @.CarQuant as tinyint, @.ResNum as int, @.Status as smallint, @.LastModified as tinyint,
@.RetCode as int = Null Output, @.RetResNum as nvarchar(15) = Null Output
AS
Declare @.RowCounter int
Begin Tran
UPDATE tblReservations
SET ReqDateTime = @.ReqDate, PassName = @.PassName, DispDateTime = @.DispDate, PassPhone = @.PassPhone, PassQuant = @.PassQuant, AuthBy = @.AuthBy, AcctID = @.AcctID,
PuLandmarkID = @.PuLandmark, PuStreet = @.PuStreet, PuCityID = @.PuCity, PuXStreet = @.PuXStreet, PuPoint = @.PuPoint, DestLandmarkID = @.DestLandmark, DestStreet = @.DestStreet,
DestCityID = @.DestCity, DestXStreet = @.DestXStreet, DestPoint = @.DestPoint, OperatorID = @.Operator, Comments = @.Comments, DriverReq = @.DriverReq, CarType = @.CarType,
EstHours = @.EstHours, EstPrice = @.EstPrice, CCType = @.CCType, CCNum = @.CCnum, CCAuthNum = @.CCAuthNum, CarQuant = @.CarQuant, Status = @.Status, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified
SELECT @.RowCounter = @.@.ROWCOUNT
If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = @.ResNum + ' Update Failed'
End
GO
and the VBA that calls it. The 2 highlighted lines will be null if the "@.ResNum + " is included. Otherwise, it runs fine.
Public Sub cmdUpdate_Click()
Dim strSQL As String
Dim strField As String
Dim fOK As Integer
Dim intCount As Integer
Dim x As Integer
Dim strMsg As String
Dim cmd As ADODB.Command
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim dteReqDate As Date
Dim dteDispDate As Date
On Error GoTo ErrorHandler
dteReqDate = Me.txtReqDate & " " & Me.txtReqTime
dteDispDate = DateAdd("n", -Nz(Me.txtMinutesAhead, 0), dteReqDate)
Call EstablishConnection
Set db = CurrentDb()
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
Set tdf = db.TableDefs("tblReservations_Local")
rst.ActiveConnection = objConn
With cmd
.ActiveConnection = objConn
.CommandText = "procResUpdate"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ReqDate", adDBTimeStamp, adParamInput, , dteReqDate)
.Parameters.Append .CreateParameter("@.PassName", adVarChar, adParamInput, 25, Me.txtPassName)
.Parameters.Append .CreateParameter("@.DispDate", adDBTimeStamp, adParamInput, , dteDispDate)
.Parameters.Append .CreateParameter("@.PassPhone", adVarChar, adParamInput, 25, Me.txtPassPhone)
.Parameters.Append .CreateParameter("@.PassQuant", adInteger, adParamInput, , Me.txtPassQuant)
.Parameters.Append .CreateParameter("@.AuthBy", adVarChar, adParamInput, 25, Me.txtAuthBy)
.Parameters.Append .CreateParameter("@.AcctID", adInteger, adParamInput, , Me.cboAcctNum)
.Parameters.Append .CreateParameter("@.PuLandmark", adInteger, adParamInput, , Me.cboLandmark)
.Parameters.Append .CreateParameter("@.PuStreet", adVarChar, adParamInput, 50, Me.txtPuAddress)
.Parameters.Append .CreateParameter("@.PuCity", adInteger, adParamInput, , Me.cboPuCity)
.Parameters.Append .CreateParameter("@.PuXStreet", adVarChar, adParamInput, 50, Me.txtPuXStreet)
.Parameters.Append .CreateParameter("@.PuPoint", adVarChar, adParamInput, 50, Me.txtPuPoint)
.Parameters.Append .CreateParameter("@.DestLandmark", adInteger, adParamInput, , Me.cboDestLandmark)
.Parameters.Append .CreateParameter("@.DestStreet", adVarChar, adParamInput, 50, Me.txtDestAddress)
.Parameters.Append .CreateParameter("@.DestCity", adInteger, adParamInput, , Me.cboDestCity)
.Parameters.Append .CreateParameter("@.DestXStreet", adVarChar, adParamInput, 50, Me.txtDestXStreet)
.Parameters.Append .CreateParameter("@.DestPoint", adVarChar, adParamInput, 50, Me.txtDestPoint)
.Parameters.Append .CreateParameter("@.Operator", adInteger, adParamInput, , Me.txtOperator)
.Parameters.Append .CreateParameter("@.Comments", adVarChar, adParamInput, 250, Me.txtComments)
.Parameters.Append .CreateParameter("@.DriverReq", adVarChar, adParamInput, 25, Me.txtDriverReq)
.Parameters.Append .CreateParameter("@.CarType", adInteger, adParamInput, , Me.cboCarType)
.Parameters.Append .CreateParameter("@.EstHours", adCurrency, adParamInput, , Me.txtEstHours)
.Parameters.Append .CreateParameter("@.EstPrice", adCurrency, adParamInput, , Me.txtEstPrice)
.Parameters.Append .CreateParameter("@.CCType", adVarChar, adParamInput, 25, Me.cboCCType)
.Parameters.Append .CreateParameter("@.CCnum", adVarChar, adParamInput, 25, Me.txtCCNum)
.Parameters.Append .CreateParameter("@.CCAuthNum", adVarChar, adParamInput, 25, Me.txtCCAuthNum)
.Parameters.Append .CreateParameter("@.CarQuant", adInteger, adParamInput, , Me.txtCarQuant)
.Parameters.Append .CreateParameter("@.ResNum", adInteger, adParamInput, , Me.txtResNum)
.Parameters.Append .CreateParameter("@.Status", adInteger, adParamInput, , Me.cboStatus)
.Parameters.Append .CreateParameter("@.LastModified", adInteger, adParamInput, , Me.txtLastModified)
.Parameters.Append .CreateParameter("@.RetCode", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@.RetResNum", adVarChar, adParamOutput, 15)
.Execute
End With
fOK = cmd.Parameters("@.RetCode")
strMsg = cmd.Parameters("@.RetResNum")|||Update for anyone interested. It seems that there was a type mismatch occurring. @.ResNum is a numeric value (data type int), and apparently in this line that caused a problem:
Select @.RetResNum = @.ResNum + ' Update Failed'
I assumed it would concatenate the two values. If I create a new varchar variable and populate it with the value of @.ResNum (converted to varchar) and use that, everything works fine. What threw me was that not only was that return parameter null, but the other one was too.
