Prevent saving Duplicate entry in vb.net

Question

Satyam_1 0 Junior Poster

9 Years Ago

Hi, I hv vb.net applivcation.
I have following CODE1 to save data & to prevent duplicate data entry into table.
The following code I put in button save event.
However I want to put this CODE2 in perticluar textbox e.g. here SpecimenNoTextBox. so that when the user enter duplicate SpecimenNo.it says "Specimen No. already exist".
How can i do this?

CODE1

If SpecimenNoTextBox.Text = "" Then
            MsgBox("Enter")
        Else

            Dim cmd As New OleDbCommand
            Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Histopathology New.accdb")
            con.Open()
            Dim cmdlogin As New OleDbCommand(("SELECT SpecimenNo FROM HPReport"), con)
            Dim dalogin As New OleDbDataAdapter
            Dim dslogin As New DataSet
            dalogin.SelectCommand = cmdlogin
            cmdlogin.ExecuteNonQuery()
            dslogin.Clear()
            dalogin.Fill(dslogin, "HPReport")

            If dslogin.Tables(0).Rows.Count > 0 Then
                Dim i As Integer = 0

                While i < dslogin.Tables(0).Rows.Count
                    If SpecimenNoTextBox.Text = dslogin.Tables(0).Rows(i).Item(0).ToString Then

                        MsgBox("ID Already exist")
                        GoTo Line1
                    Else

                    End If
                    i = i + 1
                End While


            Else
                MsgBox("No Data Exist")
            End If

            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "insert into HPReport(ID,Status,RegNo,PatientName,Age,Sex,InchargeUnitDr,Ward,SColleDate,SReceDate,SIDNo,SpecimenNo) values ('" & IDTextBox.Text & "','" & StatusComboBox.Text & "','" & RegNoTextBox.Text & "','" & PatientNameTextBox.Text & "','" & AgeTextBox.Text & "','" & SexComboBox.Text & "','" & InchargeUnitDrComboBox.Text & "','" & WardComboBox.Text & "','" & SColleDateTextBox.Text & "','" & SReceDateTextBox.Text & "','" & SIDNoTextBox.Text & "','" & SpecimenNoTextBox.Text & "',)"

            cmd.ExecuteNonQuery()
            MsgBox("Added Successfuly")
            SpecimenNoTextBox.Clear()
            autogenerate_ID()

Line1:
        End If
    End Sub

CODE2

 Dim cmd As New OleDbCommand
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Histopathology New.accdb")
        con.Open()
        Dim cmdlogin As New OleDbCommand(("SELECT SpecimenNo FROM HPReport"), con)
        Dim dalogin As New OleDbDataAdapter
        Dim dslogin As New DataSet
        dalogin.SelectCommand = cmdlogin
        cmdlogin.ExecuteNonQuery()
        dslogin.Clear()
        dalogin.Fill(dslogin, "HPReport")

        If dslogin.Tables(0).Rows.Count > 0 Then
            Dim i As Integer = 0

            While i < dslogin.Tables(0).Rows.Count
                If SpecimenNoTextBox.Text = dslogin.Tables(0).Rows(i).Item(0).ToString Then

                    MsgBox("ID Already exist")
                    GoTo Line1
                Else

                End If
                i = i + 1
            End While


        Else
            MsgBox("No Data Exist")
        End If
Line1:

vb.net

4 Contributors
11 Replies
5K Views
4 Years Discussion Span
Latest Post 4 Years Ago Latest Post by kamal_22

Mr.M 58 Future Programmers

9 Years Ago

Check this out. http://stackoverflow.com/questions/20649548/adding-records-in-vb-net-and-checking-if-records-exist-using-elseif

Santanu.Das 125 Santanu Das

9 Years Ago

My opinion is you can solve your problems most easily if you create two seperate functons one to check Blank Text and another to check duplicate value.
If the function (1) finds the blank text then exit from save. and if the function (2) finds the duplicate value from database then exit from save.

I just here give you an example of a function to check blank text.

 Private Function BlankValues() As Boolean

        If Trim(Me.txtStuName.Text) = "" Then
            MessageBox.Show("Student name is empty.")
            Me.txtStuName.Focus()
            Return False
        End If

        If Trim(Me.txtStuRollNo.Text) = "" Then
            MessageBox.Show("Roll No. is empty.")
            Me.txtStuRollNo.Focus()
            Return False
        End If

        Return True
    End Function

And now an example to check duplicate value

 Private Function SearchDuplicate() As Boolean
        Dim result As Boolean = False

        Dim Conn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
        Dim Cmd As New System.Data.OleDb.OleDbCommand

        Try
            Conn.Open()

            Cmd.CommandType = System.Data.CommandType.Text

            Cmd.CommandText = "Select Count(*) From Student Where RollNo = ?"

            Cmd.Parameters.AddWithValue("@EnrollNo", Me.txtStuRollNo.Text)


            Cmd.Connection = Conn

            Dim rd As Integer = Cmd.ExecuteScalar()
            If rd > 0 Then
                result = True
            End If

            Cmd.Dispose()

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try

        Return result
    End Function

Now time to save new data.

Private sub SaveNewData()

    'First Check for blank value.
    'If it finds blank then Exit sub

    If Not BlankValues() Then

        Exit Sub

    End If

    'Check here for duplicate value
    'i.e. the value already exists in database
    'If it true then exit sub

    If SearchDuplicate() Then
        MessageBox.Show(String.Format("Roll No. {0} already exists.", Me.txtStuRollNo.Text))
        Me.txtStuRollNo.Focus()
        Exit Sub

    End If


    'If everything Ok!
    'Write your codes here to
    'save new data


End Sub

Hope it could help you.
Change the codes at per your requirments.

Edited 9 Years Ago by Santanu.Das

Mr.M 58 Future Programmers

9 Years Ago

Ow no you do it wrong. Let me differentiate these two tools for you so that you will see how its works.

1) Button -> has Click event which is to perform a task once the button is clicked.

2) TextBox -> has TextChange event which only take raise this event when/while typing something on it, this raise event for each stroke/character entered/typed or you can use it to track a typed word to see if its match/contains any word you want.

Now with that being said, the reason why I said you did it wrong reflect to this. The button does this for you so remove that code from TextChange event.

As I've said the textbox also allows you to monitor the word of which is what I think you want. In that case of yours to check the name if its exist while name is typed on textbox you need to use the sort of auto-complete or any other technique to tell if a name exist or not. To use auto-complete you will need to point its source to your database and keep in mind that if its does complete a word that means its exist.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Satyam_1 0 Junior Poster · Answer 1 · 2015-07-11T10:33:02+00:00

where I should put this code as per ur URL?
In btnsave event or SpecimenNoComboBox TextChange event?

As the code 1 works perfactly in btnsave event...

The only problem is that when I put the CODE2 in SpecimenNoComboBox TextChange event- when the user enters value e.g. 1234 in SpecimenNoComboBox it shows the data already exist (if 1234 is already there in data table) but when he erase 1234 totally (blank SpecimenNoComboBox)again its showing data already exist.

I want to prevent this duplicate msg when the SpecimenNocomboBox becomes empty.

Satyam_1 0 Junior Poster · Answer 2 · 2015-07-11T17:48:30+00:00

Dear, my code1 works properly for btnsave event.
I just want show msg "ID already exist" when user enter same reg.no. in SpecimenNoTextBox (at the time of typing the value in the SpecimenNoTextBox).

As per Mr.M , I modified the code as below, but when someone enter into (here) RegNoTextBox its not showing the duplicate entry msg. Its showing only when the user strikes Save button.

Please look carfully in RegNo textchange event--

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


        If RegNotxt.Text = "" Or PatientNametxt.Text = "" Then
            MsgBox("Please fill-up all fields!", MsgBoxStyle.Exclamation, "Add New Customer!")
        Else
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Histopathology New.accdb")
            con.Open()
            Dim theQuery As String = "SELECT * FROM HPReport WHERE RegNo=@RegNo AND PatientName=@PatientName"
            Dim cmd1 As OleDbCommand = New OleDbCommand(theQuery, con)
            cmd1.Parameters.AddWithValue("@FirstName", RegNotxt.Text)
            cmd1.Parameters.AddWithValue("@LastName", PatientNametxt.Text)

            Using reader As OleDbDataReader = cmd1.ExecuteReader()
                If reader.HasRows Then
                    ' User already exists
                    MsgBox("User Already Exist!", MsgBoxStyle.Exclamation, "Add New User!")
                Else
                    ' User does not exist, add them
                    Dim cmd As OleDbCommand = New OleDbCommand("Insert into HPReport ([RegNo],[PatientName]) values ('" + RegNotxt.Text + "','" + PatientNametxt.Text + "')", con)
                    cmd.ExecuteNonQuery()
                    MsgBox("Records Successfully Added!", MsgBoxStyle.Information, "Add New Customer!")
                    RegNotxt.Text = ""
                    PatientNametxt.Text = ""
                End If
            End Using

            con.Close()
        End If
    End Sub

    Private Sub RegNotxt_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegNotxt.TextChanged

        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Histopathology New.accdb")
        con.Open()
        Dim theQuery As String = "SELECT * FROM HPReport WHERE RegNo=@RegNo AND PatientName=@PatientName"
        Dim cmd1 As OleDbCommand = New OleDbCommand(theQuery, con)
        cmd1.Parameters.AddWithValue("@FirstName", RegNotxt.Text)
        cmd1.Parameters.AddWithValue("@LastName", PatientNametxt.Text)

        Using reader As OleDbDataReader = cmd1.ExecuteReader()
            If reader.HasRows Then
                ' User already exists
                MsgBox("User Already Exist!", MsgBoxStyle.Exclamation, "Add New User!")
            End If
        End Using

        con.Close()

    End Sub

Satyam_1 0 Junior Poster · Answer 3 · 2015-07-11T17:54:52+00:00

Dear Shark_1
I also modified the code as per example, but its not helping me in saving the data. Would pl modifiy the below code?
Its also not showing error msg when entring value into RegNotextbox .

Private Function BlankValues() As Boolean
        If Trim(Me.RegNotxt.Text) = "" Then
            MessageBox.Show("Student name is empty.")
            Me.RegNotxt.Focus()
            Return False
        End If
        If Trim(Me.PatientNametxt.Text) = "" Then
            MessageBox.Show("Roll No. is empty.")
            Me.PatientNametxt.Focus()
            Return False
        End If
        Return True
    End Function

    Private Function SearchDuplicate() As Boolean
        Dim result As Boolean = False
        Dim Conn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Histopathology New.accdb")
        Dim Cmd As New System.Data.OleDb.OleDbCommand
        Try
            Conn.Open()
            Cmd.CommandType = System.Data.CommandType.Text
            Cmd.CommandText = "Select Count(*) From HPReport Where PatientName = ?"
            Cmd.Parameters.AddWithValue("@EnrollNo", Me.PatientNametxt.Text)
            Cmd.Connection = Conn
            Dim rd As Integer = Cmd.ExecuteScalar()
            If rd > 0 Then
                result = True
            End If
            Cmd.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try
        Return result
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'First Check for blank value.
        'If it finds blank then Exit sub
        If Not BlankValues() Then
            Exit Sub
        End If
        'Check here for duplicate value
        'i.e. the value already exists in database
        'If it true then exit sub
        If SearchDuplicate() Then
            MessageBox.Show(String.Format("Roll No. {0} already exists.", Me.PatientNametxt.Text))
            Me.PatientNametxt.Focus()
            Exit Sub
        End If
        'If everything Ok!
        'Write your codes here to
        'save new data
    End Sub

Satyam_1 0 Junior Poster · Answer 4 · 2015-07-11T18:38:46+00:00

Sorry for the above code Shark 1.

Please find below code...

However its saving data , also says duplicate data exist...

But all i want (MsgBox-"Duplicate entry") in TextBox event...apart from btnSave event

 Private Function BlankValues() As Boolean
        If Trim(Me.RegNotxt.Text) = "" Then
            MessageBox.Show("Student name is empty.")
            Me.RegNotxt.Focus()
            Return False
        End If
        If Trim(Me.PatientNametxt.Text) = "" Then
            MessageBox.Show("Roll No. is empty.")
            Me.PatientNametxt.Focus()
            Return False
        End If
        Return True
    End Function
    Private Function SearchDuplicate() As Boolean
        Dim result As Boolean = False
        Dim Conn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Histopathology New.accdb")
        Dim Cmd As New System.Data.OleDb.OleDbCommand
        Try
            Conn.Open()
            Cmd.CommandType = System.Data.CommandType.Text
            Cmd.CommandText = "Select Count(*) From HPReport Where PatientName = ?"
            Cmd.Parameters.AddWithValue("@EnrollNo", Me.PatientNametxt.Text)
            Cmd.Connection = Conn
            Dim rd As Integer = Cmd.ExecuteScalar()
            If rd > 0 Then
                result = True
            End If
            Cmd.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try
        Return result
    End Function
    Private Sub SaveNewData()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'First Check for blank value.
        'If it finds blank then Exit sub
        If Not BlankValues() Then
            Exit Sub
        End If
        'Check here for duplicate value
        'i.e. the value already exists in database
        'If it true then exit sub
        If SearchDuplicate() Then
            MessageBox.Show(String.Format("Roll No. {0} already exists.", Me.PatientNametxt.Text))
            Me.PatientNametxt.Focus()
            Exit Sub
        End If
        'If everything Ok!
        'Write your codes here to
        'save new data

        Dim cmd As New OleDbCommand
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Histopathology New.accdb")
        con.Open()
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "insert into HPReport(RegNo,PatientName) values ('" & RegNotxt.Text & "','" & PatientNametxt.Text & "')"

        cmd.ExecuteNonQuery()
        MsgBox("Added Successfuly")
    End Sub

..

Santanu.Das 125 Santanu Das · Answer 5 · 2015-07-12T05:59:19+00:00

Checking duplicate value on every keystroke when you are entering data to a text box is not a perfect thinking. It will disturb the user when he will try to enter data. From my opinion you can perform the duplicate value checking in TextbOX LostFocus Event instead of TextChanged event.

Mr.M 58 Future Programmers · Answer 6 · 2015-07-13T05:41:22+00:00

Yea or on ENTER keydown event which is not under textbox events but can be incorporated

Satyam_1 0 Junior Poster · Answer 7 · 2015-07-13T17:37:34+00:00

The code works perfactly in LostFocus event,

The code also working in TextChange event...

The only problem was that my database was containing null data in some of the rows..

So when the SpecimenComboBox becomes empty , the msg ("ID already exist") was visible.

Thanks guys....

kamal_22 0 Newbie Poster · Answer 8 · 2019-11-22T14:41:15+00:00

hi all
how can i do like this for text file
many thanks