How to validate Duplicate entries VB.net

Question

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:

VB

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
        Next
        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
        cn.Close()
    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;"
        cn.Open()
        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)
        dt.Clear()
        da.Fill(dt)
        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
        Try
            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
        Else

        End If

        Try
            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 & "' )"
                .ExecuteNonQuery()
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
            Return
        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
        Try
            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 & "' "
                .ExecuteNonQuery()

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

    End Sub

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

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

    End Sub

End Class

Posted 6-May-15 2:11am

Shaik Izaz

Updated 6-May-15 2:31am

CHill60

v3

Add a Solution

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)

Dave Kreskowiak · Answer 1 · 2015-05-06T02:48:00

Solution 1

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.

Posted 6-May-15 2:48am

Dave Kreskowiak

Comments

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)
connection.Open()
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.