How to avoid duplicate entries when saving in database in vb.net

Question

I have a button but it can save duplicate entries i don't know how to correctly put a if not exist operator pls help..

  cmd = New SqlCommand("INSERT INTO Students(Familyname,Firstname,Middlename,StudentID)VALUES('" & txtname.Text & "','" & txtfname.Text & "','" & txtmname.Text & "','" & txtid.Text & "')", cn)

    cn.Open()
    i = cmd.ExecuteNonQuery
    cn.Close()

    If txtname.Text <> "" Then
    ElseIf i > 0 Then
        MsgBox("Save Sucessfully!", MessageBoxIcon.Information, "Success")
        showrecord()
        clear()
    Else
        MsgBox("Save Failed!", MessageBoxIcon.Error, "Error")
    End If

http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Ňɏssa Pøngjǣrdenlarp, Jan 22 '15 at 02:05
What constitutes a duplicate? Determine that and add a unique index on it so the database won't allow it. Then have a prior database call that looks for that already existing and let the user know if they are trying to insert a duplicate. — UnhandledExcepSean, Jan 22 '15 at 02:12
It is the job of the database to provide Unique OK, so you ought not be INSERTing StudentID. Before you add a record check if one exists with the existing Names(s) depending on what you define as unique (just use a COUNT query). And **always** use SQL parameters for many reasons. — Ňɏssa Pøngjǣrdenlarp, Jan 22 '15 at 02:12

score 2 · Answer 1 · answered Jan 22 '15 at 02:25

You can use NOT EXISTS to prevent duplicate insert:

Dim sql = "INSERT INTO Students(Familyname, Firstname, Middlename, StudentID) " & _
            "VALUES(@FamilyName, @Firstname, @Middlename, @StudentID)" & _
            "WHERE NOT EXISTS(SELECT 1 FROM Students WHERE StudentId = @StudentID)"

        Using cn As New SqlConnection("Your connection string here")
            Dim cmd As SqlCommand = New SqlCommand(sql, cn)

            cmd.Parameters.Add("@FamilyName", SqlDbType.VarChar, 50).Value = txtname.Text
            cmd.Parameters.Add("@Firstname", SqlDbType.VarChar, 50).Value = txtfname.Text
            cmd.Parameters.Add("@Middlename", SqlDbType.VarChar, 50).Value = txtmname.Text
            cmd.Parameters.Add("@StudentID", SqlDbType.VarChar, 50).Value = txtid.Text

            Dim i = cmd.ExecuteNonQuery
        End Using

You should always use parameterized queries to avoid SQL Injection attacks.

NOTE: Please apply appropriate field types.

score 1 · Accepted Answer · answered Jan 22 '15 at 02:14

Try this one :

cn.Open()
Dim intReturn as integer
Dim strSql as string = "Select * from Students where StudentID = @StudentID"

sqlcmd = new sqlcommand(strSql, cn)
With sqlcmd.parameters
  .addwithvalue("@StudentID", ctype(txtid.text,string)
End with

intReturn = sqlcmd.ExecuteScalar

If(intReturn > 0)
    cmd = New SqlCommand("INSERT INTO Students(Familyname,Firstname,Middlename,StudentID)VALUES('" & txtname.Text & "','" & txtfname.Text & "','" & txtmname.Text & "','" & txtid.Text & "')", cn)

    i = cmd.ExecuteNonQuery

    If txtname.Text <> "" Then
    ElseIf i > 0 Then
        MsgBox("Save Sucessfully!", MessageBoxIcon.Information, "Success")
        showrecord()
        clear()
    Else
        MsgBox("Save Failed!", MessageBoxIcon.Error, "Error")
    End If
Else
    MsgBox("Student Already Exist", MessageBoxIcon.Error, "Error")
End If
cn.Close()

And don't forget to make your StudentID field as Unique in your database.

How to avoid duplicate entries when saving in database in vb.net

2 Answers2