Click here to Skip to main content
15,947,389 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Posted

1 solution

SQL
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

SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900