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

        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
        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") = "-----"
            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
        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)
        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.

2 Years
Discussion Span
Last Post by Jing_1
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.