2

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,

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

now where is the problem?

SaraniO
  • 587
  • 6
  • 26
  • 1
    Use a *single* UPDATE...FROM...WHERE statement whose `WHERE` clause eliminates duplicates. The multiple `Exists` statements just make the code harder to read and understand. – Panagiotis Kanavos Jan 30 '17 at 11:14
  • @PanagiotisKanavos user can't change HNumber, but can edit HDate, I want to check that if HNumber already has a record in new edited HDate prevent to update any feild and do nothing – SaraniO Jan 30 '17 at 11:20
  • 1
    HiveNumber=HiveNumber will always be true. Should be HiveNumber=@HiveNumber? – Vijay Jan 30 '17 at 11:27
  • 1
    You could create a unique index on the Hdate and HNumber columns. check this post http://stackoverflow.com/questions/11754754/how-do-i-create-a-multiple-column-unique-constraint-in-sql-server – Raphael Jan 30 '17 at 11:27
  • @Vijay yeeees that was the problem, I've forget to write @, now it works but seems had a huge impact on performance and searching time, is there a better way for this ? – SaraniO Jan 30 '17 at 11:40
  • @Raphael I check that topic but I'm new to SQL and don't understand what should i do, could you help me as an answer ? – SaraniO Jan 30 '17 at 11:42
  • If the performance is bad you might need index on HarvestDate and HiveNumber. – Vijay Jan 30 '17 at 12:07
  • 1
    @SaraniO what you should do is launch a command like this from SSMS "CREATE UNIQUE INDEX IX_Date_Number ON tHoneyHarvest (HDate, HNumber);" when you try to enter the same twice the same date on the 2 indexed fields you'll get and exception, here you find the microsoft documentation https://msdn.microsoft.com/en-us/library/ms187019.aspx – Raphael Jan 30 '17 at 12:54

2 Answers2

2

The best way is use non Clustered index .non Clustered index prevent the duplicate records when Inserts or update is going to occur .

CREATE UNIQUE INDEX MyIndex ON ExcelTable(HDate, HNumber)

please refer to

Community
  • 1
  • 1
BehrouzMoslem
  • 9,053
  • 3
  • 27
  • 34
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)

I forgot to add @ before HiveNumber

response.write
  • 175
  • 5
  • 23
SaraniO
  • 587
  • 6
  • 26