SqlBulkCopy doesn't copy all the rows from Excel sheet

sqlbulkcopy sql-server vb.net

Question

I developed a Windows app to import Excel file and write the data into a SQL Server database table. The application works well but it starts writing from the line 27 or 30 and sometimes from line 29 of the Excel sheet. I need all the rows to be written to the database table from line 1 to line 4500.

My code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim fdlg As OpenFileDialog = New OpenFileDialog
    fdlg.Title = "Open File Dialog"
    fdlg.InitialDirectory = "C:\"
    fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*"
    fdlg.FilterIndex = 2
    fdlg.RestoreDirectory = True
    If fdlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
        ExcelFileName = fdlg.FileName
    End If

    'Excel 2007
    Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFileName + ";Extended Properties=""Excel 12.0 Xml;HDR=No;""")
    Try
        ExcelConnection.Open()
    Catch ex As Exception
    End Try

    Dim expr As String = "SELECT * FROM [Sheet1$]"

    Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
    Dim objDR As OleDbDataReader

    Dim SQLconn As New SqlConnection()
    Dim ConnString As String = "Data Source=My_PC_Name;Initial Catalog=myDatabase;Integrated Security=True"
    SQLconn.ConnectionString = ConnString
    SQLconn.Open()

    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)

        bulkCopy.DestinationTableName = "ItemDetails"

        Try
            objDR = objCmdSelect.ExecuteReader
            If objDR.HasRows = True Then
                bulkCopy.WriteToServer(objDR)
                MessageBox.Show("You Successfuly import the excel file")
                objDR.Close()
                SQLconn.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Using
End Sub
1
0
7/12/2015 10:18:11 AM

Popular Answer

SQL is per definition not ordered. So to guarantee the order you will have to add a helper column (say column A) with your row_id. YOu can then use that later to select your data ordered.

0
7/12/2015 6:15:07 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow