Remove duplicate while uploading the data into access

Question

User-1771609044 posted

Hi,

I would like to know how do i remove the duplicates while uploading the data into access database given below is the code that i am currently using.

If FileUpload1.HasFile Then
            Dim fileExt As String
            fileExt = System.IO.Path.GetExtension(FileUpload1.FileName)
            'validating the file before being uploaded
            If (fileExt = ".xls") Then

                FileUpload1.SaveAs("C:\Inetpub\wwwroot\SD\" & FileUpload1.FileName)
                Try
                    FileUpload1.SaveAs("C:\Inetpub\wwwroot\SD\" & FileUpload1.FileName)
                    Label1.Text = "File name: " & FileUpload1.PostedFile.FileName & "<br>" & FileUpload1.PostedFile.ContentLength & " kb<br>" & "File Size: " & " <br> File Uploaded Successfully :)"
                    Dim filename As String
                    filename = FileUpload1.FileName.ToString()
                    My.Computer.FileSystem.RenameFile("C:\Inetpub\wwwroot\SD\" & filename, "Staff.xls")
                    Dim Access As String = "C:\Inetpub\wwwroot\SD\App_Data\tracker.accdb"
                    Dim Excel As String = "C:\Inetpub\wwwroot\SD\Staff.xls"
                    Dim connect As String = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Excel & ";Extended Properties=Excel 8.0;"
                    Using conn As New OleDbConnection(connect)
                        Using cmd As New OleDbCommand()
                            cmd.Connection = conn
                            cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[Level1dailytickets] SELECT * FROM [Sheet1$]"
                            conn.Open()
                            Label1.Text = cmd.ExecuteNonQuery().ToString() + "Rows Affected"
                            Response.Redirect("adminpage.aspx")
                        End Using
                        conn.Close()
                    End Using
                Catch ex As Exception
                    Label1.Text = "ERROR: " + ex.Message.ToString() + ":("
                    My.Computer.FileSystem.DeleteFile("C:\Inetpub\wwwroot\SD\Staff.xls")
                End Try
                My.Computer.FileSystem.DeleteFile("C:\Inetpub\wwwroot\SD\Staff.xls")
            Else
                Label1.Text = "Only excel files  are allowed! You have not specified a file!!!  :("

            End If

        End If

Answer 1

User-851967432 posted

SELECT DISTINCT * FROM [Sheet1]

Answer 2

User-1771609044 posted

thanks for the reply....but That did not help i had tried that before....

Answer 3

User-1771609044 posted

and while i am uploading the data i want to find out if the same data is available in the access database if yes then it has to remove it or provide an output stating that the data exists

Answer 4

User-851967432 posted

Oh I see,

Well there's only 1 way to know, and that's to query the Access database prior to the insert.

Answer 5

User3866881 posted

Hello rakesh21189：）

As far as I see，I think you should：

1）Upload the database file onto the Server，but first please use OleDbDataAdapter with the help of OleDbCommandBuilder to build automatically CRUD methods。

2）Then use the same way to create another memory-based DataTable from the Excel file。

3）Then use foreach to compare the two DataTables，when finds duplicated，please use Remove() and then in the end，you should use DataAdapter.Update(DataTable);

Answer 6

User-1771609044 posted

I get the method could you please explain this with an example..

Answer 7

User-1199946673 posted

You can do this by simply changing your CommandText! Assuming there's 1 field called "field1" that uniquely identifies the record:

 cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[Level1dailytickets] SELECT xls.* FROM [Sheet1$] AS xls LEFT JOIN [MS Access;Database=" & Access & "].[Level1dailytickets] AS mdb ON xls.Field1 = mdb.Field1 WHERE mdb.Field1 IS NULL"

If there are more fields that identify the records

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[Level1dailytickets] SELECT xls.* FROM [Sheet1$] AS xls LEFT JOIN [MS Access;Database=" & Access & "].[Level1dailytickets] AS mdb ON xls.Field1 = mdb.Field1 AND xls.Field2 = mdb.Field2 WHERE mdb.Field1 IS NULL"

Answer 8

User-1771609044 posted

Hi hans_v,

Thanks For the reply  this worked for me

Answer 9

User-1771609044 posted

It worked a few minutes but now i am getting a type mismatch error

given below is the code

"INSERT INTO [MS Access;Database=" & Access & "].Imacdailyticket SELECT xls.* FROM ([Sheet1$] xls LEFT OUTER JOIN [MS Access;Database=" & Access & "].Imacdailyticket mdb ON xls.Task = mdb.Task) WHERE(mdb.Task Is NULL)"

Answer 10

User-1199946673 posted

There's no space between WHERE and (

Answer 11

User-1771609044 posted

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].Imacdailyticket SELECT xls.* FROM ([Sheet1$] xls LEFT OUTER JOIN [MS Access;Database=" & Access & "].Imacdailyticket mdb ON xls.Task = mdb.Task) WHERE (mdb.Task Is NULL)"

Same issue :(

Answer 12

User-1771609044 posted

This works fine

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].Level1dailytickets SELECT xls.* FROM ([Sheet1$] xls LEFT OUTER JOIN [MS Access;Database=" & Access & "].Level1dailytickets mdb ON xls.TicketNumber = mdb.TicketNumber) WHERE(mdb.TicketNumber Is NULL)"

but i implement the same code on

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].Imacdailyticket SELECT xls.* FROM ([Sheet1$] xls LEFT OUTER JOIN [MS Access;Database=" & Access & "].Imacdailyticket mdb ON xls.Task = mdb.Task) WHERE (mdb.Task Is NULL)"

does not work....

Answer 13

User-1771609044 posted

This works fine

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].Level1dailytickets SELECT xls.* FROM ([Sheet1$] xls LEFT OUTER JOIN [MS Access;Database=" & Access & "].Level1dailytickets mdb ON xls.TicketNumber = mdb.TicketNumber) WHERE(mdb.TicketNumber Is NULL)"

but i implement the same code on

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].Imacdailyticket SELECT xls.* FROM ([Sheet1$] xls LEFT OUTER JOIN [MS Access;Database=" & Access & "].Imacdailyticket mdb ON xls.Task = mdb.Task) WHERE (mdb.Task Is NULL)"

does not work....

Answer 14

User-1199946673 posted

but i implement the same code on

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].Imacdailyticket SELECT xls.* FROM ([Sheet1$] xls LEFT OUTER JOIN [MS Access;Database=" & Access & "].Imacdailyticket mdb ON xls.Task = mdb.Task) WHERE (mdb.Task Is NULL)"

does not work....

Meaning? Do you get an error?