How to prevent duplicate entries in Vb.net using Mysql database

[ramprasad.anumandla]

I am trying to create the code which Should not allow duplicates in the column 'Empid'.

What is the best way to prevent duplicates entries?

Can any one modify my insert code mentioned below so as to verify the entry and then if no duplicate it should insert.


My Insert code (VB.net and MYSQL):
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
cmd.Connection = cn

Dim sqlQuery As String = "INSERT INTO login (empid,password) VALUES('" & txtCreateempid.Text & "','" & txtCreatepassword.Text & "')"


With cmd
.CommandText = sqlQuery
.Connection = cn
.ExecuteNonQuery()
End With


MsgBox("Created New User")

txtCreateempid.Text = ""
txtCreatepassword.Text = ""
End Sub

[TysonLPrice]

Assuming MSSQL has an auto-incrementing column definition or indentity function I would do that. Saves maintaining your own numbering scheme. That's assuming empid is numeric. Otherwise you need to check if it already exists. IN MS SQL it would be:

if not exists (select empid from login where empid = xxxx)
	INSERT INTO login (empid,password) VALUES....

Maybe there is a MySQL equivalent.

[techgnome]

MSSQL does... but this isn't MSSQL ... but MySQL... which does have an auto increment capability... so I'd use that and not worry about inserting a value into EmpID.... but if EmpID is coming from somewhere else (like the Employee table) ... then the fix is just as easy... make a EmpID a Primary Key with a UNIQUE constraint on it. Then FKey it back to the parent table (again assuming Employee) and Cascade delete (so if you delete the parent Employee record, the Login record is also deleted.)

-tg

[SeanGrebey]

If you don't want to/can't for some reason use built in MySQL, you can always do a loop where you check to see if empid exists in the database before you insert, and you keep looping and incrementing by 1 until you find a free number.....

[TysonLPrice]

If you don't want to/can't for some reason use built in MySQL, you can always do a loop where you check to see if empid exists in the database before you insert, and you keep looping and incrementing by 1 until you find a free number.....

Looping like that seems expensive. If the functions are available in MySQL then I would:

1. Check if it exists as I posted above.
2. Get the next one with something like "set @empid = isnull((select max(empid) from Login with (updlock)"

Without knowing the actual key structure I can't say for sure. I would definately automate it somehow if I could using the database not code. In the shop I'm in now there are three ways of doing it based on the application sets.

1. Identity.
2. Select max(ID) + 1 from the existing keys.
3. A stored procedure that returns the next key.