Good Evening Everyone, i am currently facing a problem. I want to append data to an excel file but before that i want to check for duplication records in the excel file before appending. For example, an employee want to check in to a company but he can only check in once. I have create the excel table with the following fields.
Department EmployeeID Name SystemDate SystemTime Status
Based on the employeeID, i want to check whether is there a duplication record in the excel file before appending a new data. My current vb.net code is like that.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim pram As OleDbParameter
Dim dr As DataRow
Dim olecon As System.Data.OleDb.OleDbConnection
Dim olecomm As OleDbCommand
Dim olecomm1 As OleDbCommand
Dim oleadpt As OleDbDataAdapter
Dim ds As DataSet
Try
olecon = New System.Data.OleDb.OleDbConnection
olecon.ConnectionString = connstring
olecomm = New OleDbCommand
olecomm.CommandText = _
"Select Department, EmployeeID, Name, SystemDate, SystemTime, Status, SystemTimeOut, StatusOut from [Daily Attendance$]"
olecomm.Connection = olecon
olecomm1 = New OleDbCommand
olecomm1.CommandText = "Insert into [Daily Attendance$] " & _
"(Department, EmployeeID, Name, SystemDate, SystemTime, Status, SystemTimeOut, StatusOut) values " & _
"(@FName, @LName, @Age, @Phone, @Time, @Status, @Time2, @Status2)"
olecomm1.Connection = olecon
pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
pram.SourceColumn = "Department"
pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
pram.SourceColumn = "EmployeeID"
pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
pram.SourceColumn = "Name"
pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
pram.SourceColumn = "SystemDate"
pram = olecomm1.Parameters.Add("@Time", OleDbType.VarChar)
pram.SourceColumn = "SystemTime"
pram = olecomm1.Parameters.Add("@Status", OleDbType.VarChar)
pram.SourceColumn = "Status"
pram = olecomm1.Parameters.Add("@Time2", OleDbType.VarChar)
pram.SourceColumn = "SystemTimeOut"
pram = olecomm1.Parameters.Add("@Status2", OleDbType.VarChar)
pram.SourceColumn = "StatusOut"
oleadpt = New OleDbDataAdapter(olecomm)
ds = New DataSet
olecon.Open()
oleadpt.Fill(ds, "Daily Attendance")
If IsNothing(ds) = False Then
dr = ds.Tables(0).NewRow
dr("Department") = Label11.Text
dr("EmployeeID") = FName.Text
dr("Name") = Label9.Text
dr("SystemDate") = Label8.Text
dr("SystemTime") = Label6.Text
dr("Status") = "Clock In"
dr("SystemTimeOut") = "-----"
dr("StatusOut") = "-----"
ds.Tables(0).Rows.Add(dr)
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = olecomm1
Dim i As Integer = oleadpt.Update(ds, "Daily Attendance")
MessageBox.Show("Employee " & FName.Text & " attendance recorded!")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
olecomm = Nothing
oleadpt = Nothing
ds = Nothing
dr = Nothing
pram = Nothing
End Try
counter = counter + 1
Label13.Text = counter
If File.Exists(fileLoc) Then
Using sw As StreamWriter = New StreamWriter(fileLoc)
sw.Write(Label13.Text)
End Using
End If
End Sub
This is the code for appending a new data into the excel file but i would like to check for duplication employeeID record before appending. Anyone can assist me? Thank you very much.