VB.Net - BulkCopy Excel to SQL...doesn't work now that table is empty prior to import

Experts, please help figure out why the SQLBulkCopy ends up with a blank table...when it worked before.

This code copies data from Excel and SQLBulkCopies it into a SQL Table that is populated with 1000 rows x 1000 columns of null data and works with no problem.  However, because the null data was in there to begin with it was randomly placing the import rows throughout the length of the SQL Table so that there were many blank (null) rows in between rows of actual data.  So I decided to add code that emptied the table prior to import and now all I get is one blank row when it's done.  Please help me figure out why the data is no longer showing up in the table after SQLBulkCopy.

FYI the only difference from when it was working until now is the addition of the code towards the top that DELETEs the data FROM the table.

Thank you very much for any help!
'IMPORT DATA FROM EXCEL INTO SQL DB MODULE TABLE
        'DELETE NULL DATA PLACE HOLDERS PRIOR TO IMPORT
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
            Conn.Open()
        Else : Conn.Open()
        End If
        Dim strSQL As String = ("USE " & DBName & ";DELETE FROM " & moduletableCB.Text & ";")
        Dim myCommand As New SqlCommand(strSQL, Conn)
        myCommand.ExecuteNonQuery()
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
        End If
        'CONNECTION STRING TO EXCEL WORKBOOK
        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & importpathvar & ";Extended Properties=""Excel 8.0;HDR=NO;"""
        'Try
        'CREATE CONNECTION TO EXCEL WORKBOOK
        Using excelConnection As OleDbConnection = New OleDbConnection(ConnectionString)
            Using excelCommand As New OleDbCommand("INSERT INTO 0 as [non_null_column], * FROM [" & importworksheetvar & "$] WHERE F1 IS NOT NULL ", excelConnection)
                Using destinationConnection As SqlConnection = New SqlConnection("Server=ADAMS-LAPTOP-PC\SQLEXPRESS; integrated security = true;" & "database=" & DBName)
                    excelConnection.Open()
                    Using dr As DbDataReader = excelCommand.ExecuteReader()
                        destinationConnection.Open()
                        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.UseInternalTransaction, Nothing)
                            For i As Integer = 0 To dr.VisibleFieldCount - 1
                                bulkCopy.ColumnMappings.Add(New SqlBulkCopyColumnMapping(i, i))
                            Next
                            bulkCopy.DestinationTableName = "dbo." & moduletableCB.Text
                            'Write from the source to the destination.
                            bulkCopy.WriteToServer(dr)
                        End Using
                    End Using
                End Using
            End Using
        End Using

Open in new window

LVL 1
adamchicagoAsked:
Who is Participating?
 
mdouganCommented:
Well, you could try commenting out the Delete code... I don't see anything wrong with it, but you never know.  You really shouldn't have to delete anything, because you really shouldn't be inserting any null data in the table in the first place (!!)

Definitely, the change I mentioned above is important, so, revert to that if you have not already.  Also, verify that there are rows in the spreadsheet where column F1 is not null... in fact, just for fun, you might want to take out the WHERE part of that select statement, just to be sure that that is not where the problem is.
0
 
mdouganCommented:
I believe the problem is with this line:

            Using excelCommand As New OleDbCommand("INSERT INTO 0 as [non_null_column], * FROM [" & importworksheetvar & "$] WHERE F1 IS NOT NULL ", excelConnection)


Try changing it to something like this:

            Using excelCommand As New OleDbCommand("SELECT 0 as [non_null_column], * FROM [" & importworksheetvar & "$] WHERE F1 IS NOT NULL ", excelConnection)
0
 
adamchicagoAuthor Commented:
Thank you very much for your help...that's actually the way I had it when this problem started, but just for kicks I tried it again and it still is doing the same thing.

Before I added the code to delete the data prior to prior to the SQLBulkCopy the copy occurred and the data was in the table no problem...this only started occurring after deleting prior to copy...ugh!

Thanks again for your help!!!
0
 
adamchicagoAuthor Commented:
Thank you very much for your help...sorry for the delay, the holidays kept from any programming for a while.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.