How Do I Prevent Duplicate Record With My Code

Question

1.00/5 (1 vote)

See more:

VB

Imports MySql.Data.MySqlClient
Public Class UserAdmin
Public mycon As New MySqlConnection
Public myadap As New MySqlDataAdapter
Public mycmd As New MySqlCommand
Public myds As New DataSet
Public rec As Integer
Public sb As Boolean
Public idnum As String
Public trec As Integer

Sub connect()
        mycon = New MySqlConnection
        mycon.ConnectionString = "server=localhost;user id=root;password=;database=dbase"
        mycon.Open()
        mycmd.Connection = mycon
        mycmd.CommandText = "select*from tbluseradmin"
        myds = New DataSet
        myadap.SelectCommand = mycmd
        myadap.Fill(myds, "tbl")
        trec = myds.Tables("tbl").Rows.Count - 1
        rec = 0
End Sub

Private Sub UserAdmin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       Call connect()
       Call display()
end sub

Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
        If txtid.Text = "" Or txtname.Text = "" Or txtmn.Text = "" Or txtln.Text = "" Or cbogender.Text = "" Or cbosub.Text = "" Or cbopos.Text = "" Then
            MsgBox("Complete All Fields Needed", MsgBoxStyle.Information, "")
            txtid.Focus()
        ElseIf IsNumeric(txtid.Text) = False Then
            MsgBox("ID number should be numbers only")
        ElseIf sb = True Then
            mycmd.Connection = mycon
            mycmd.CommandText = "insert into `tbluseradmin`(`ID`,`Name`,`MiddleName`,`LastName`,`Gender`,`Subject`,`Position`)values('" & txtid.Text & "','" & txtname.Text & "','" & txtmn.Text & "','" & txtln.Text & "','" & cbogender.Text & "','" & cbosub.Text & "','" & cbopos.Text & "')"
            mycmd.ExecuteNonQuery()
            mycon.Close()
            My.Computer.FileSystem.CopyFile(ofd.FileName, Application.StartupPath & "\images\" & txtid.Text & ".jpg")
            MsgBox("Record Save", MsgBoxStyle.Information, "")
            Call connect()
            Call display()
            Call textboxlocked()
            Call buttonunlock()
            Call cmdlock()
            Call cmd2unlock()
        Else
            mycmd.Connection = mycon
            mycmd.CommandText = "update `tbluseradmin` set`ID`='" & txtid.Text & "',`Name`='" & txtname.Text & "',`MiddleName`='" & txtmn.Text & "',`LastName`='" & txtln.Text & "',`Gender`='" & cbogender.Text & "'where`ID`='" & idnum & "'"
            mycmd.ExecuteNonQuery()
            mycon.Close()
            My.Computer.FileSystem.CopyFile(ofd.FileName, Application.StartupPath & "\images\" & txtid.Text & ".jpg")
            MsgBox("Record Update", MsgBoxStyle.Information, "")
            Call connect()
            Call display()
            Call textboxlocked()
            Call buttonunlock()
            Call cmdlock()
            Call cmd2unlock()
        End If
End Sub

Posted 20-Sep-14 19:07pm

Member 11096118

Updated 20-Sep-14 20:00pm

George Jonsson

v3

Add a Solution

Comments

ChauhanAjay 21-Sep-14 1:19am

Basing on which column will you check for duplicates?

1 solution

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)

ChauhanAjay · Accepted Answer · 2014-09-20T20:22:00

The solution below is with the assumption that the "ID" column is not auto incremented value.

1. The user enters the value of the 'ID' from the front end.
2. Check if that value exists in your dataset.
3. If it does not exists in your dataset then insert the value in the database.
4. If that value exists in the dataset then display a message.

Below I will just show you how to change the insert part

VB

Dim result() As DataRow = myds.Tables(0).select("ID = '"+ txtid.Text +"'")
If (result.Length == 0) Then
	mycmd.Connection = mycon
    mycmd.CommandText = "insert into `tbluseradmin`(`ID`,`Name`,`MiddleName`,`LastName`,`Gender`,`Subject`,`Position`)values('" & txtid.Text & "','" & txtname.Text & "','" & txtmn.Text & "','" & txtln.Text & "','" & cbogender.Text & "','" & cbosub.Text & "','" & cbopos.Text & "')"
    mycmd.ExecuteNonQuery()
    mycon.Close()
    My.Computer.FileSystem.CopyFile(ofd.FileName, Application.StartupPath & "\images\" & txtid.Text & ".jpg")
    MsgBox("Record Save", MsgBoxStyle.Information, "")
Else
	MsgBox("ID already exists")
End

Hope the above code helps.