none
SQLBulkcopy inserting twice RRS feed

  • Question

  • Hi,

    Having trouble with sqlbulkcopy. It is inserting records twice. Any help will greatly be appreciated. Please see below my code.

     

    ProtectedSubbtnSend_Click(ByValsender AsObject, ByVale AsSystem.EventArgs) HandlesbtnSend.Click

           

    If(fileuploadExcel.HasFile) Then


               

    ' Get the name of the file to upload.


               

    DimfileName AsString= fileuploadExcel.FileName

               

    DimuserID AsString


                userID =

    Membership.GetUser().UserName

               

    DimConnString AsString= ConfigurationManager.ConnectionStrings("crs5_oltpConnectionString").ToString()

               

    'file upload path


     

     

     

     

     

               

    Dimpath AsString= Server.MapPath("~/App_Data/") + fileuploadExcel.FileName

                fileuploadExcel.SaveAs(path)

               

    'Create connection string to Excel work book


               

    Dima AsString= fileuploadExcel.PostedFile.FileName

               

    DimexcelConnectionString AsString= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ path + ";Extended Properties=Excel 8.0"


               

    'Create Connection to Excel work book


               

    DimexcelConnection AsNewOleDbConnection(excelConnectionString)

               

    'Create OleDbCommand to fetch data from Excel


               

    Dimcmd AsNewOleDbCommand("Select [Consumer Account Number],[Name],[Creditor Short Name],[Payment Type],[Payment Memo Code],[Payment Method],[Payment Location],[Payment Amount],[Payment Date],[Principal ],[Interest ],[Collection Costs],[Collection Charge],[Agency Fees],[Attorney Fees],[Billing Fees],[Previous Client Lit Fee],[Client Fixed Fees],[Cancel Fees],[Convenience Fee],[Windham Court Costs],[Creditor Court Cost],[Fair Market Value],[Fees],[Client Internal Charge],[Late Fees],[Make Whole],[Other ],[Placement  Charge],[Place Fees],[Unbilled Payments],[Amount Withheld],[Comment],[Reversal Transaction ID],[record_disposition],'new' as [status],'"& userID & "' as [inputby],'"& fileName & "' as inputsource,1 as importid,'"& DateAndTime.Now.ToString() & "' as currentdate from [Sheet1$] where len([Consumer Account Number])>0", excelConnection)

                excelConnection.Open()

               

    Dimworksheet1 AsString= excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()

     

     

               

    Try


                   

    DimdReader AsOleDbDataReader


                   

    Dimtable AsDataTable= NewDataTable()

                    dReader = cmd.ExecuteReader()

                    Table.Load(dReader)

                    excelConnection.Close()

                   

    DimsqlBulk AsNewSqlBulkCopy(ConnString)

                   

    'Give your Destination table name


                    sqlBulk.DestinationTableName =

    "ETL.directpay_batch_stg"


                    sqlBulk.WriteToServer(Table)

                    lblerror.ForeColor = System.Drawing.

    Color.Green

                    lblerror.Text =

    "# of records inserted: "& table.Rows.Count

                    GridView1.DataBind()

     

               

    Catchex AsOleDbException


                   

    'MesgBox("Error in uploading file due to following exception" & vbNewLine & ex.ToString)


                    lblerror.ForeColor = System.Drawing.

    Color.Red

                   

    Ifworksheet1 <> "Sheet1$"Then


                        lblerror.Text =

    "Invalid WorkSheet Name. Please make sure worksheet name is Sheet1"


                   

    Else


                        lblerror.Text =

    "Error in uploading file due to following exception"& vbNewLine & ex.ToString

                   

    EndIf


                   

    'Response.Redirect("~/Account/Import.aspx")


               

    Finally


                    excelConnection.Close()

               

    EndTry


           

    EndIf


      

       

    EndSub

    Thursday, October 23, 2014 2:48 PM

Answers

  • Hello NasrinChowdhury,

    >>Having trouble with sqlbulkcopy. It is inserting records twice

    With your provided code, I made a similar test, however, it worked fine:

    Sub Main()
    
            If (True) Then
    
                Dim fileName As String = ""
    
                Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\BMX\Project\2014\10\VBs\P20141024\Sample20141024.xlsx;Extended Properties='Excel 8.0;HDR=Yes;'"
    
                Dim excelConnection As New OleDbConnection(excelConnectionString)
    
                Dim cmd As New OleDbCommand("Select [OrderID],[OrderName],[CustomerID] From [Order$]", excelConnection)
    
                excelConnection.Open()
    
                Dim worksheet1 As String = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
    
                Try
    
                    Dim dReader As OleDbDataReader
    
                    Dim table As DataTable = New DataTable()
    
                    dReader = cmd.ExecuteReader()
    
                    table.Load(dReader)
    
                    excelConnection.Close()
    
                    Dim sqlBulk As New SqlBulkCopy("Data Source=(localdb)\Projects;Database=DFDB;Trusted_Connection=True;")
    
                    sqlBulk.DestinationTableName = "[Order]"
    
                    sqlBulk.WriteToServer(table)
    
                Catch ex As OleDbException
    
                Finally
    
                    excelConnection.Close()
    
                End Try
    
            End If
    
        End Sub
    

    And as far as I know, the SqlBulkCopy would not insert twice or it should throw a primary key conflict exception. Do you meet this exception since you describe that it inserts twice?

    My test environment is Excel2013, VS2013, SQL Server2012 and .NET Framewrok4.5, what about yours?

    And I am wondering if you have debug the code step by step, if not, please check the saved file after this line “Dimpath AsString= Server.MapPath("~/App_Data/") + fileuploadExcel.FileName fileuploadExcel.SaveAs(path)” and also add a break point at this line “table.Load(dReader)” to check the “table” if it has duplicated records already.

    You could try with my code to check if it works for you and please provide a more readable code snippet so that we could help you better.

    Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, October 24, 2014 6:21 AM
    Moderator