Avoid duplicate data using vb.net and ms access

Question

what can I use if I want to put a message box saying the data is already exist. Here is my code.

Private Sub bttnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnSave.Click

    con.Open()
    Dim fname As String = Trim(txtFName.Text)
    Dim mname As String = Trim(txtMName.Text)
    Dim lname As String = Trim(txtLName.Text)
    Dim add As String = Trim(txtAddress.Text)
    Dim num As String = Trim(txtNumber.Text)
    Dim email As String = Trim(txtEmail.Text)
    Dim stat As String = "Active"
    Dim remark As String = "Available"

    If fname = Nothing Or mname = Nothing Or lname = Nothing Or add = Nothing Or num = Nothing Then
        MsgBox("Please Fill All Fields", vbInformation, "Note")
    Else
        Dim add_guest As New OleDbCommand("INSERT INTO tblGuest(GuestFName,GuestMName,GuestLName,GuestAddress,GuestContactNumber,GuestGender,GuestEmail,Status,Remarks) values ('" &
                                          fname & "','" &
                                          mname & "','" &
                                          lname & "','" &
                                          add & "','" &
                                          num & "','" &
                                          cboGender.Text & "','" &
                                          email & "','" &
                                          stat & "','" &
                                          remark & "')", con)
        add_guest.ExecuteNonQuery()
        add_guest.Dispose()
        MsgBox("Guest Added!", vbInformation, "Note")
        txtFName.Clear()
        txtMName.Clear()
        txtLName.Clear()
        txtAddress.Clear()
        txtNumber.Clear()
        txtEmail.Clear()
    End If
    con.Close()
    display_guest()
End Sub

You need a field that uniquely identity a person from another. Email or a social securiry number are usually good indicators of uniqueness. So you first try to read the database with that key and insert only if you don't find any match. — Steve
– Steve, Commented Aug 16, 2022 at 7:00
However you should first fix a bigger problem in your code. You concatenate strings to build an sql command. This will create a lot of problems here. Just to start try to insert a person with a single quote in its lastname like O'Brian then search about Sql Injection — Steve
– Steve, Commented Aug 16, 2022 at 7:01

Ananth MK · Accepted Answer · 2022-08-16 07:19:23Z

0

Yes, as @Steve said, add Primary or Unique key for tblGuest for column email. Then check the existence of the record before executing insert statement.

Primary key - Add Primary key

Check exists or not - Check exists or not 1

Check exists or not 2

answered Aug 16, 2022 at 7:19

Ananth MK

3921 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Avoid duplicate data using vb.net and ms access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related