Cannot update records in SQL Server

See more:

SQL

MS-Access

I have a front end (MS access) to call a stored procedure MS SQL Server that would insert or update records based on the parameters. The insert and update stored procedure are separate SP's and very straight forward statements involving the primary key as part of the where conditions in the update statement. The insert stored procedure runs smoothly but the update didn't change the records at all and does not produce any errors at all. When I tried to run through the debug it executes but no update in the server side. I tried to run the SP manually by setting up the parameters exactly the same as the one I have in the front end and it works perfectly fine. I been pulling up my hair on this and couldn't fine any solution, I think the problem is in my front end. I used the same function in access to call the SQL stored procedure. Anyone can please help me, truly appreciate it. The below are the sample codes:

On button click event,

..

XCmd "EXEC sp_Update @pk = " & Me.txtpk & ", @Value = " & Me.txtVal
..

below is my function in MS Access

Public Function XCmd(sSQL as String) as Boolean
   Dim Comm As ADODB.Command
   Dim lngRecordsAffected As Long

   If con.State = adStateClosed Then
      con.ConnectionString = conConnection
      con.Open
   End If

   Set Comm = New ADODB.Command
   With Comm
      .ActiveConnection = con
      .CommandText = sSQL
      .Execute lngRecordsAffected
   End With
   ExecuteMyCommand = True
End Function

and updae SP..

CREATE PROCEDURE [dbo].[sp_Update]
(
@pk int
,@Value varchar(30)
)
BEGIN TRY
    SET NOCOUNT ON;

    BEGIN TRANSACTION

       UPDATE [dbo].[Simpletbl]
       SET [FldValue] = @Value
       WHERE PKId = @pk

    IF @@ROWCOUNT = 0
       BEGIN
           RAISERROR('Cannot update!',16,1);
       END
       COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    BEGIN
        IF @@TRANCOUNT > 0
           BEGIN
              ROLLBACK TRANSACTION;
           END
    END;
END CATCH