Solved

Prevent inserting duplicate values in SQL Server database

Posted on 2014-10-30
3
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

615 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