OleDbDataReader and bulkcopy.WriteToServer do not write to table

Question

My script does not insert any data in the database table and it does not throw any error either.

The server, database and table names are correct. The workbook is selected using filedialog (hence correct filepath) and the worksheet name seems correct too. I have data in column A. My table is made of 2 fields:

ID (Identity, autocrement)
CustomerName

The While Loop performs 2 iterations but I have 4 records on the worksheet.

Any idea why the data does not insert in the table?

    'Open the dialog box to select the file to upload
    Dim fd As OpenFileDialog = New OpenFileDialog()
    Dim strFileName As String

        fd.InitialDirectory = "C:\"
        fd.Filter = "Excel Files|*.xlsx"
        fd.FilterIndex = 2
        fd.RestoreDirectory = True


    'declare variables - edit these based on your particular situation 
    Dim ssqltable As String = "tbl1"

    Dim myexceldataquery As String = "select CustomerName from [A$]"

    'create our connection strings 
    Dim sexcelconnectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=Excel 12.0;"


    Dim ssqlconnectionstring As String = "Data Source=AA1\SQL001_DEV001;Initial Catalog=DB_Test;Integrated Security=True"

    'execute a query to erase any previous data from our destination table 
    Dim sclearsql As String = Convert.ToString("delete from ") & ssqltable

    Dim sqlconn As New SqlClient.SqlConnection(Connections.MyMainSQLServer)

    Dim sqlcmd As New SqlCommand(sclearsql, sqlconn)

        sqlconn.Open()
        sqlcmd.ExecuteNonQuery()
        sqlconn.Close()

    'series of commands to bulk copy data from the excel file into our sql table 
    Dim oledbconn As New OleDbConnection(sexcelconnectionstring)
    Dim oledbcmd As New OleDbCommand(myexceldataquery, oledbconn)

        oledbconn.Open()

    Dim dr As OleDbDataReader = oledbcmd.ExecuteReader()
    Dim bulkcopy As New SqlBulkCopy(ssqlconnectionstring)

        bulkcopy.DestinationTableName = ssqltable

        While dr.Read()
            bulkcopy.WriteToServer(dr)
        End While

        dr.Close()
        oledbconn.Close()

Please write a better question title, you are not using an OleDbDataReader to write (which would not even be possible), so it is incorrect to make that suggestion in the title. — Peter B, Jul 26 '18 at 13:42
For starters, you should call `bulkcopy.WriteToServer(dr)` without invoking `dr.Read()` (let alone in a loop). `SqlBulkCopy` will process the reader on its own. — Jeroen Mostert, Jul 26 '18 at 13:44
Agreed with Jeroen, see https://msdn.microsoft.com/en-us/library/434atets(v=vs.110).aspx for good explanation and examples. — Peter B, Jul 26 '18 at 13:45

score 1 · Accepted Answer · answered Jul 26 '18 at 14:35

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcelSpreadsheet.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
ExcelConnection.Open()

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=MMSQL1;Initial Catalog=DbName; User Id=UserName; Password=password;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()


Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"

Try
  objDR = objCmdSelect.ExecuteReader
  bulCopy.WriteToServer(objDR)
  objDR.Close()
  SQLConn.Close()

 Catch ex As Exception
  MsgBox(ex.ToString)
 End Try
End Using

OleDbDataReader and bulkcopy.WriteToServer do not write to table

1 Answers1