使用存储过程更新信息 [英] updating information using stored procedure

问题描述

大家好，

我有一个关于使用存储过程更新信息的问题，并将Sql server作为后端访问项目。请有人可以帮我解决一下吗？我想在FORM ACCESS中更新多个记录。

谢谢

Hello all,
I have a question about updating information using stored procedure and access project form Sql server as a back end. Please anyone can help me about it? I want update multiple records IN FORM ACCESS.
thanks

推荐答案

CREATE PROCEDURE [update_TBL_INFO_4]
(@ASSET_NUMBER  [nvarchar],
 @PORT_NUMBER   [nvarchar],
 @MACHINE_NAME  [nvarchar],
 @TYPE  [nvarchar],
 @BRANCH    [nvarchar],
 @BUILDING_NUMBER   [nvarchar],
 @FLOOR     [nvarchar],
 @ROOM_NUMBER   [nvarchar],
 @TELEPHONE_NUMBER  [nvarchar],
 @USERS     [nvarchar],
 @SERIAL    [nvarchar])

AS
BEGIN
UPDATE [datasystems].[dbo].[TBL_INFO]

SET  [ASSET_NUMBER]  = @ASSET_NUMBER,
 [PORT_NUMBER]   = @PORT_NUMBER,
 [MACHINE_NAME]  = @MACHINE_NAME,
 [TYPE]  = @TYPE,
 [BRANCH]    = @BRANCH,
 [BUILDING_NUMBER]   = @BUILDING_NUMBER,
 [FLOOR]     = @FLOOR,
 [ROOM_NUMBER]   = @ROOM_NUMBER,
 [TELEPHONE_NUMBER]  = @TELEPHONE_NUMBER,
 [USERS]     = @USERS,
 [SERIAL]    = @SERIAL

WHERE
( [ASSET_NUMBER]     = @ASSET_NUMBER AND
 [PORT_NUMBER]   = @PORT_NUMBER AND
 [MACHINE_NAME]  = @MACHINE_NAME AND
 [TYPE]  = @TYPE AND
 [BRANCH]    = @BRANCH AND
 [BUILDING_NUMBER]   = @BUILDING_NUMBER AND
 [FLOOR]     = @FLOOR AND
 [ROOM_NUMBER]   = @ROOM_NUMBER AND
 [TELEPHONE_NUMBER]  = @TELEPHONE_NUMBER AND
 [USERS]     = @USERS AND
 [SERIAL]    = @SERIAL)
END
GO

Since your WHERE clause checks all of the params, if any of them are different (ie, when you're passing in an updated value), the WHERE isn't going to find any records that match.

If you have a Primary Key defined, use that in the WHERE clause and update the rest. Otherwise, you may need to pass in the old values as well to find the appropriate match.

UPDATE:

With the primary key being ASSET_NUMBER, you'd want to update the fields that aren't the primary key, and use the primary key alone in the WHERE clause, like this:

UPDATE
    [datasystems].[dbo].[TBL_INFO]
SET
    [PORT_NUMBER] = @PORT_NUMBER,
    [MACHINE_NAME] = @MACHINE_NAME,
    [TYPE] = @TYPE,
    [BRANCH] = @BRANCH,
    [BUILDING_NUMBER] = @BUILDING_NUMBER,
    [FLOOR] = @FLOOR,
    [ROOM_NUMBER] = @ROOM_NUMBER,
    [TELEPHONE_NUMBER] = @TELEPHONE_NUMBER,
    [USERS] = @USERS,
    [SERIAL] = @SERIAL
WHERE
    [ASSET_NUMBER] = @ASSET_NUMBER

这篇关于使用存储过程更新信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！