locked
Remove duplicate while uploading the data into access RRS feed

  • 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

    Friday, May 11, 2012 10:44 AM

Answers

  • 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"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 14, 2012 1:18 PM

All replies

  • User-851967432 posted

    SELECT DISTINCT * FROM [Sheet1]

    Friday, May 11, 2012 11:55 AM
  • User-1771609044 posted

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

    Friday, May 11, 2012 12:03 PM
  • 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

    Friday, May 11, 2012 12:06 PM
  • 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.

    Friday, May 11, 2012 12:24 PM
  • 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);

    Saturday, May 12, 2012 9:32 PM
  • User-1771609044 posted

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

    Monday, May 14, 2012 12:38 PM
  • 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"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 14, 2012 1:18 PM
  • User-1771609044 posted

    Hi hans_v,

    Thanks For the reply  this worked for me

    Monday, May 14, 2012 5:21 PM
  • 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)"

    Monday, May 14, 2012 6:03 PM
  • User-1199946673 posted

    There's no space between WHERE and (

    Monday, May 14, 2012 6:10 PM
  • 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 :(

    Tuesday, May 15, 2012 8:11 AM
  • 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....

    Tuesday, May 15, 2012 8:16 AM
  • 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....

    Tuesday, May 15, 2012 8:16 AM
  • 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?

    Tuesday, May 15, 2012 10:14 AM