Click here to Skip to main content
15,742,011 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I don't know how to prevent Duplicate entry validation in VB.NET
Am using an excel sheet to save my data and there i want to Validate "ID"(TxtId.Text) to prevent duplicate entries.
Here is my CODE:

Imports System.Data.OleDb
Imports System
Imports System.Text
Imports System.IO
Imports System.Net
Imports System.Net.Sockets
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Threading
Imports Microsoft.VisualBasic
Imports System.Text.RegularExpressions
Imports System.Windows.Forms

Public Class Form1

    Private Function vld(ByVal ParamArray ctl() As Object) As Boolean
        For i As Integer = 0 To UBound(ctl)
            If ctl(i).text = "" Then
                ErrorProvider1.SetError(ctl(i), ctl(i).tag)
                Return False
                Exit For
            End If
        Return True
    End Function
    Dim cn As New OleDbConnection
    Dim cm As New OleDbCommand
    Dim da As OleDbDataAdapter
    Dim dt As New DataTable

    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    End Sub
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        TxtExamtime.Format = DateTimePickerFormat.Custom
        TxtExamtime.CustomFormat = "hh:MM tt"
        cn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source=C:\psave\New folder\save.xls;Extended Properties=Excel 8.0;"
        FillDataGridView("select ID, Family Name, Given Name, Gender, DOB, Exam Date, Exam Time, Street Name, House Nr, please, City from [edit$]")

    End Sub

    Private Sub FillDataGridView(ByVal Query As String)
        da = New OleDbDataAdapter(Query, cn)
        With DataGridView1
            .DataSource = dt
            .Columns(0).HeaderText = "ID"
            .Columns(1).HeaderText = "Family Name"
            .Columns(2).HeaderText = "Given Name"
            .Columns(3).HeaderText = "Gender"
            .Columns(4).HeaderText = "DOB"
            .Columns(5).HeaderText = "Exam Date"
            .Columns(6).HeaderText = "Exam Time"
            .Columns(7).HeaderText = "Street Name"
            .Columns(8).HeaderText = "House Nr"
            .Columns(9).HeaderText = "please"
            .Columns(10).HeaderText = "City"
            .Columns(10).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        End With
    End Sub

    Private Sub BtnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSearch.Click
            FillDataGridView("select * from [edit$] where ID='" & TxtId.Text & "'")
            TxtFamilyname.Text = dt.Rows(0).Item(1)
            TxtGivenname.Text = dt.Rows(0).Item(2)
            TxtGender.Text = dt.Rows(0).Item(3)
            TxtDob.Text = dt.Rows(0).Item(4)
            TxtExamdate.Text = dt.Rows(0).Item(5)
            TxtExamtime.Text = dt.Rows(0).Item(6)
            TxtStreet.Text = dt.Rows(0).Item(7)
            TxtHouse.Text = dt.Rows(0).Item(8)
            TxtPlz.Text = dt.Rows(0).Item(9)
            TxtCity.Text = dt.Rows(0).Item(10)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
        End Try
    End Sub

    Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSave.Click
        If vld(TxtId, TxtFamilyname, TxtGivenname, TxtGender, TxtDob, TxtExamdate, TxtExamtime, TxtStreet, TxtHouse, TxtPlz, TxtCity) = False Then
            Exit Sub

        End If

            With cm
                .Connection = cn
                .CommandText = "insert into [edit$]values('" & TxtId.Text & "','" & TxtFamilyname.Text & "','" & TxtGivenname.Text & "','" & TxtGender.Text & "','" & TxtDob.Text & "','" & TxtExamdate.Text & "','" & TxtExamtime.Text & "','" & TxtStreet.Text & "','" & TxtHouse.Text & "','" & TxtPlz.Text & "','" & TxtCity.Text & "' )"
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
        End Try
        MsgBox("succefully Saved!", MsgBoxStyle.Information, Text)

    End Sub

    Private Sub BtnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btnupdate.Click
            With cm
                .Connection = cn
                .CommandText = "Update [edit$] set [Family Name]= '" & TxtFamilyname.Text & "',[Given Name] = '" & TxtGivenname.Text & "',[Gender] = '" & TxtGender.Text & "',[DOB] = '" & TxtDob.Text & "',[Exam Date] = '" & TxtExamdate.Text & "',[Exam Time] = '" & TxtExamtime.Text & "',[Street Name] = '" & TxtStreet.Text & "',[House Nr] = '" & TxtHouse.Text & "',[please] = '" & TxtPlz.Text & "',[CITY] = '" & TxtCity.Text & "' where ID = '" & TxtId.Text & "' "

            End With
            FillDataGridView("select * from [edit$]")
            MsgBox("Succesfully updated!", MsgBoxStyle.Information, Text)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, Text)
        End Try

    End Sub

    Private Sub BtnClose_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnClose.Click
    End Sub

    Private Sub BtnClear_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnClear.Click
        'To see all the data in DataGridView
        FillDataGridView("select * from[edit$]")

    End Sub

End Class
Updated 6-May-15 2:31am

1 solution

Excel doesn't have any UNIQUE constraint support for data so you have to enforce the constraint yourself in your code. Real database engines, such as SQL Express, LocalDb, MySQL, or whatever, all support this constraint themselves so if you try to put duplicate data into a constrained column the query will fail and you capture this exception in your code and handle it.

If you insist on using an Excel sheet as your "database" you have no choice but to execute a SELECT on the sheet to see if the field that you're treating as unique already has that value. If you have multiple columns that you're enforcing the constraint on, you have to do multiple queries, one for each column, to check for the duplicate value.
Share this answer
Shaik Izaz 6-May-15 9:43am    
can you please explain what is this SELECT on the sheet?
Dave Kreskowiak 6-May-15 10:09am    
Why? You're already doing it in your code snippet. SELECT columnList FROM sheet.
Shaik Izaz 6-May-15 10:44am    
Am new to Vb.Net and i wrote this code for to prevent Duplicate ID and even though am able to enter same data in ID field. I don't know what to do now?

Here is my code for to prevent Duplicate entries in TxtID.Text

Public Function DoesIDExist(ByVal id As String) As Boolean

Dim connectionString As String = "provider=microsoft.jet.oledb.4.0; Data Source=C:\psave\New folder\save.xls;Extended Properties=Excel 8.0;"
Dim selectStatement As String = "SELECT COUNT (ID) As CountOfID FROM [edit$] WHERE ID=?"
Dim countOfId As Integer = 0

Using connection As New OleDbConnection(connectionString)
Using command As New OleDbCommand(selectStatement, connection)
command.Parameters.AddWithValue("?", id)
countOfId = Convert.ToInt32(command.ExecuteScalar)
End Using

End Using

Return countOfId > 0

End Function
Dave Kreskowiak 6-May-15 17:26pm    
Step through the code in the debugger, line by line, and check the values in the variables. Are the values what you expect? Are the values in the parameters you're passing into the query what you expect? Are the values in the database the same type as the parameter? This is all stuff you have to check yourself.

The debugger is your friend. If you don't know how to use it, you better learn because you're walking around blind without it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900