如何防止sql存储过程中的重复更新 [英] How to prevent duplicate update in sql stored procedure

问题描述

我在 Sql 和插入 Form 我的项目中有这个表，我阻止用户为相同的 HNumber 输入相同的 HDate

I have this table in Sql and in insert Form of my project I prevent the user from entering same HDate for Same HNumber

RecID       HDate        HNumber     HComb
----------------------------------------------
1           2017-1-30       1          12
3           2017-1-29       1          15
5           2017-1-30       2          12
6           2017-1-30       3          12
9           2017-1-30       4          12

但在编辑表单中我不知道如何防止这种情况发生，

But in Edit Form I don't know how to prevent that,

我在存储过程中尝试了这段代码，但它适用于某些 HNumber，但它阻止了其他一些 HNumber 在它自己的日期中被编辑

I try this code in stored procedure but it work for some HNumber, But it prevent some other HNumber to be edited in it's own date

Create Procedure UpdCombHarByRecID

@RecID int,
@HarvestDate Date,
@HiveNumber int,
@HoneyComb Float, 

as

if NOT Exists (Select * From tHoneyHarvest Where RecID=@RecID)
return 0   
//there is no record to be updated

if Exists (Select * From tHoneyHarvest Where HarvestDate=@HarvestDate AND
 HiveNumber=HiveNumber And
 RecID!=@RecID) 
 // I hoped this should do the job 
 //(RecID is PrimaryKey and it is identity)
return 2

Update tHoneyHarvest 

Set HarvestDate=@HarvestDate,
HoneyType=@HoneyType,
HoneyComb=@HoneyComb,
HoneyDetails=@HoneyDetails

Where RecID=@RecID
return 1

现在问题出在哪里?

推荐答案

感谢评论中的帮助.问题是由于程序中的拼写错误造成的.

thanks for the help in comment. The problem was due to a typo in the procedure.

if Exists (Select * From tHoneyHarvest Where HarvestDate=@HarvestDate AND
  HiveNumber=@HiveNumber And
  RecID!=@RecID) 

我忘记在 HiveNumber 前添加 @

I forgot to add @ before HiveNumber

这篇关于如何防止sql存储过程中的重复更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！