troubleshooting Question

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

Avatar of adamchicago
adamchicago asked on
Visual Basic Classic.NET ProgrammingSQL
4 Comments1 Solution949 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros