Prevent inserting duplicate values in SQL Server database

Posted on 2014-10-30
Medium Priority
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
Question by:romsom
LVL 20

Accepted Solution

Daniel Van Der Werken earned 1600 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 )
     INSERT INTO OT_Table(OT_Line, Emp_Id, TimeStamp) VALUES (@strOT_Line, @Emp_Id, @strTimeStamp)
LVL 41

Assisted Solution

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

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

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)
   -- 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.

Author Closing Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…

588 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