Solved

Prevent inserting duplicate values in SQL Server database

Posted on 2014-10-30
3
307 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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 was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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