无法更新SQL Server中的记录 [英] Cannot update records in SQL Server

问题描述

我有一个前端（MS访问）来调用存储过程MS SQL Server，它将根据参数插入或更新记录。插入和更新存储过程是单独的SP和非常直接的语句，涉及主键作为update语句中where条件的一部分。插入存储过程运行顺利，但更新根本没有更改记录，并且根本不会产生任何错误。当我尝试运行调试时，它执行但在服务器端没有更新。我尝试通过设置与前端完全相同的参数来手动运行SP，它的工作原理非常好。我一直在拔头发，无法解决任何问题，我认为问题出在我的前端。我在访问中使用相同的函数来调用SQL存储过程。任何人都可以帮助我，真的很感激。以下是示例代码：



按钮点击事件，



..

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

推荐答案

试试这个： -



CREATE PROCEDURE [dbo]。[sp_Update]

（

@pk INT，

@Value VARCHAR（30）

）

AS

BEGIN

SET NOCOUNT ON;



UPDATE [dbo]。[Simpletbl]

SET [FldValue] = @Value

WHERE PKId = @pk

END

Try this :-

CREATE PROCEDURE [dbo].[sp_Update]
(
@pk INT,
@Value VARCHAR(30)
)
AS
BEGIN
SET NOCOUNT ON;

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

这篇关于无法更新SQL Server中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！