How to Catch Duplicate Entry - Primary Key

Submitted by: 
Language: 
Visitors have accessed this post 30275 times.

This tutorial will guide you on how to determine if the primary key entered is already exist in the table. I created this tutorial in the hope to help newbie’s programmer solve this common problem.

Instead of displaying the default message cast by Visual Basic a message “Employee ID already exist. Please enter another Employee ID.” will appear.
For those who would like to see the source code without downloading the attachment here it is:

  1. Private Sub CmdAdd_Click()
  2. On Error GoTo err_CmdAdd_Click
  3.  
  4. Dim cnData As New Connection
  5. Dim rst As New Recordset
  6.  
  7. cnData.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Data.mdb;Persist Security Info=False"
  8.  
  9. With rst
  10. .CursorLocation = adUseClient
  11. .Open "SELECT * FROM Employees", cnData, adOpenStatic, adLockOptimistic
  12.  
  13. .AddNew
  14.  
  15. !EmployeeID = txtEmployeeID.Text
  16. !Firstname = txtFirstname.Text
  17. !Lastname = txtLastname.Text
  18.  
  19. .Update
  20.  
  21. MsgBox "Record added successfully...", vbInformation
  22. End With
  23.  
  24. exit_err_CmdAdd_Click:
  25. Exit Sub
  26.  
  27. err_CmdAdd_Click:
  28. If Err = -2147467259 Then
  29. MsgBox "Employee ID already exist. Please enter another Employee ID.", vbCritical
  30. Else
  31. MsgBox Err.Description, vbInformation
  32. End If
  33. End Sub

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Comments

Sir,
Thank you for your
###### How to Catch Duplicate Entry - Primary Key ###### code.
its really a good code.

Jahangir Alam
From Bangladesh
[email protected], [email protected], Facebook.com/Jahangir2330

how to Textbox form click add button then msgbox (this number already exits in datagridivew enter another") in visual basic code
!
any body guys helpme
[email protected]

Nice

very useful thanks for this code ^_^

how to install msdn ??

jmoclassic.beatpro

tnx a lot sir

A BIG THANKS !!!! MUAH MUAH

Good Job

Dear Sir,
I am a beginner, Can I get same Code (check "Data already exist" ) for VB.NET

Regards,
Sumesh. AV
Email: [email protected]

Most databases (all of the major ones) will return in some variable the key of the record just created. The name of the variable used differs from one database to another, but they all return it in some form. So, when you create a record, save the returned key, then use that to find the record that you want to add data to.

ayos to tol salamat ala ako masabi 1st time ko na pumonta d2 sa site na 2 at aUs dito
tnx ulit...this code is really useful to beginners like me......galing mo

How to do this in PHP?

if possible please help.

Best Regards,
Vilart

The concept is still the same except PHP is using different approach when calling a recordset.

First you need to execute a query with the WHERE clause and check if the record exist.

same query ""check Duplicate reocrd"
How to do this in PHP?

if possible please help.
I wan source code
Regards
Lubna

i have a thesis and im programming charge invoice i'v got a problem in save button after i add the data in the header and then seek for the customer number to store the last CI number in the customer file i go for a check of NOMATCH? if it is not found then i go for clear all my fields but the problem is that the date is already in the database..
how can i trap this without duplicating the data or not to have a run time error.please help tnx..

im looking forward for your reply...

proud to be pinoy..

we are having a defense and our program really sucks we dont have yet code in computing the installment and cash basis alson the code on printing the statement of account plz help us thanks!!!!!!!!!!!!!1v//.+) u . . .

******************code on printing the statement of account************
If rsc.State <> 0 Then rsc.Close
rsc.Open "Select * from TblCash where paidday ='" & CMBsalesday.Text & "' and paidmonth='" & CMBsalesmonth.Text & "' and paidyear='" & CMBsalesyear & "'", xec, 1, 3

Set CasherReport.DataSource = rsc

CasherReport.Sections(2).Controls("Label1").Caption = "Date:" + " " + CMBsalesday.Text + " " + CMBsalesmonth.Text + " " + CMBsalesyear.Text + " " + "Income"

CasherReport.Show

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.