How do I prevent duplicate entries in my database using VB.NET ?

Question

0.00/5 (No votes)

See more:

VB

Dim ds AS String = ""

ds = sqlFunction.ExecuteSQL("INSERT INTO employee.empdata (empid,fname,lname,cnumber) VALUES ('" & empid.text & "','" & fname.text & "', '" & name.text & "','" & cnumber.text & "') ",sqlFunction.connstring)

What I have tried:

What i want is to ensure that i don't insert the same empid in my database

Posted 13-Dec-18 17:01pm

Member 14076249

Updated 13-Dec-18 19:13pm

ChauhanAjay

v2

Add a Solution

Comments

ChauhanAjay 13-Dec-18 23:12pm

Please check if the empid exists in your table or not. If it does not exists then insert the data.

Member 14076249 13-Dec-18 23:59pm

what i want is to set a query that checks or display if the data that you have entered is already in the database

Bryian Tan 13-Dec-18 23:29pm

empid is primary key?

Member 14076249 14-Dec-18 0:02am

Bryian, yes empid is primary

Bryian Tan 14-Dec-18 0:25am

if yes, then how the table end up with duplicates?

Member 14076249 14-Dec-18 0:46am

it don't accept multiple entries or same empid but i want to display it as a message in my program

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2018-12-13T19:13:00

1. If EmpID is the Primary Key, you will not be allowed to insert a duplicate. This should throw an error reflecting the constraint violation.

2. Insert, Update, and Delete statements will only return an integer reflecting the amount of rows affected. If the value is 0, the row was not inserted.

3. NEVER EVER concatenate a string together to make your command. You should be utilizing a parameterized statement to avoid SQL Injection, which was identified 20 years ago and is still in the top 5 website vulnerabilities. Even a non-malicious entry will break your command; try using a last name of O'Rourke and see what happens

Now for some simple answers to your question
A. Try running a SELECT command with a WHERE clause for the EmpID you are checking
B. Use a Stored Procedure, which can contain the SELECT statement from (A) and only insert if it does not exist. A stored procedure can also contain OUTPUT parameters which you can pass more information back to the program

Bryian Tan · Accepted Answer · 2018-12-13T18:59:00

Not clear how your application being structure, but there are several way to do it,
1. Try, catch statement, assuming the insert query will complaint if inserting duplicate key: example Using Try... Catch..., Finally![^]
2. Write another select statement to check if the empid exists before inserting

Also the code might vulnerable to SQL injection/ cross site scripting attack. Here is an example how the mentioned vulnerability works.
SQL Injection and Cross-Site Scripting[^]

Try use Parameterized Queries if possible
Using Parameterized Queries and Reports in VB.NET Database Applications[^]

How do I prevent duplicate entries in my database using VB.NET ?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0