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
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()
FillDataGridView("select * from[edit$]")
End Sub
End Class