JMA JMA - 27 days ago 18
MySQL Question

prevent duplicate entries to database

I want to prevent duplicate entries to my inventory form using vb.net and MySQL as the database, here is my code:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim myCommand As New MySqlCommand
Dim conn As MySqlConnection
Dim i As String
conn = New MySqlConnection
conn.ConnectionString = "server = localhost;username= root;password= a;database= secret"
Try
conn.Open()
Catch mali As MySqlException
MsgBox("connot establish connection")
End Try

Dim intReturn As Integer
Dim strSql As String = " select * from personnel where pcode = @pcode"

Dim sqlcmd As New MySqlCommand(strSql, conn)
With sqlcmd.Parameters
.AddWithValue("@pcode", CType(pcode.Text, String))
End With

intReturn = sqlcmd.ExecuteScalar

If (intReturn > 0) Then
cmd = New MySqlCommand("Insert into personnel values('" & pcode.Text & "','" & lname.Text & "','" & fname.Text & "','" & office.Text & "','" & designation.Text & "')")
i = cmd.ExecuteNonQuery


If pcode.Text <> "" Then
ElseIf i > 0 Then
MsgBox("Save Successfully!", MessageBoxIcon.Information, "Success")
mrClean()
ListView1.Tag = ""
Call objLocker(False)
Call LVWloader()
Call calldaw()
Else
MsgBox("Save Failed!", MessageBoxIcon.Error, "Error!")
End If
Else
MsgBox("Personnel ID Already Exist!", MessageBoxIcon.Error, "Error!")

End If


end sub

i found this while i search for answer, but when i tried to run it, it does not read the insert command but rather it goes directly to the msbox "Personnel ID Already Exist" even if theres no thesame Personnel ID.

can someone check why it does not read the insert please,

my Database tables values:

pcode = primary key

lname = longtext

fname = longtext

office = longtext

designation = longtext

any help will be much appreciated, thanks,

Answer

Sorry to say this is the wrong approach.

Databases have a built in system to prevent data being duplicated. That's through primary keys or unique key constraints. In your case, you have already created a primary key. So there is absolutely no need for you to do that SELECT COUNT(*) query.

Instead, just directly insert into the table and catch the integrity error when the pcode already exists.

Try
    cmd = New MySqlCommand("Insert into personnel values('" & pcode.Text & "','" & lname.Text & "','" & fname.Text & "','" & office.Text & "','" & designation.Text & "')")

    i = cmd.ExecuteNonQuery


    If pcode.Text <> "" Then
    ElseIf i > 0 Then
        MsgBox("Save Successfully!", MessageBoxIcon.Information, "Success")
        mrClean()
        ListView1.Tag = ""
        Call objLocker(False)
        Call LVWloader()
        Call calldaw()
    Else
        MsgBox("Save Failed!", MessageBoxIcon.Error, "Error!")
    End If
Catch ex As MySqlException
    MsgBox("Personnel ID Already Exist!", MessageBoxIcon.Error, "Error!")
End Try
Comments