Solved

Prevent inserting duplicate values in SQL Server database

Posted on 2014-10-30
3
297 Views
Last Modified: 2014-10-30
Hi Experts,
I'm using the following code to insert values in my table:
INSERT INTO OT_Table (OT_Line, Emp_Id, Timestamp) VALUES ('"& strOT_Line &"','"& strEmp_Id &"','"& strTimeStamp &"')

Open in new window

I would like to prevent insertion if the OT_Line and Emp_Id are the same.
In that case I would just like to update the value of the TimeStamp field.

How can I accomplish that on my classic ASP page?
Thank you for your help
0
Comment
Question by:romsom
3 Comments
 
LVL 20

Accepted Solution

by:
Daniel Van Der Werken earned 400 total points
ID: 40413552
This is what I do for my INSERT script:

IF NOT EXISTS(SELECT * FROM OT_Table WHERE OT_Line = @strOT_Line  AND TimeStamp  = @strTimeStamp )
   BEGIN
     INSERT INTO OT_Table(OT_Line, Emp_Id, TimeStamp) VALUES (@strOT_Line, @Emp_Id, @strTimeStamp)
   END
0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 100 total points
ID: 40413556
I normally call a stored procedure:

create procedure updOTTable
@Ot_Line int,
@emp_id int,
@time datetime
as

if (select count(*) from ot_table where ot_line = @ot_line and emp_id  = @emp_id )  = 0
   -- insert statement
INSERT INTO OT_Table (OT_Line, Emp_Id, Timestamp) VALUES (@ot_line, @emp_id, @timestamp)
else
   -- update statement
    update ot_table
set timestamp = @time where ot_line = @ot_line and emp_id  = @emp_id



You would then call the stored proc in your code.
0
 

Author Closing Comment

by:romsom
ID: 40413652
Thank you very much; it's working now. This is classic ASP so I've used the following code:

IF NOT EXISTS(SELECT * FROM OTEWR WHERE OT_Line = '"& strOT_Line &"' AND Emp_Id  = '"& strEmp_Id &"' ) BEGIN INSERT INTO OTEWR(OT_Line, Emp_Id, PersonAddedOn) VALUES ('"& strOT_Line &"', '"& strEmp_Id &"', '"& strPersonAddedOn &"') END
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question