If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.