Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

    Unanswered: @@Rowcount or output parameters error

    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.

    Code:
    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
    GO
    Last edited by pbaldy; 12-12-05 at 20:38.
    Paul

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Post the complete DDL for the proc (including @ResNum) and a sample of calling it that produces the problem you describe.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's the complete sproc (I added back the offending part in red):

    Code:
    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.

    Code:
    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")
    Paul

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •