Click here to Skip to main content
14,330,747 members
Rate this:
Please Sign up or sign in to vote.
My problem is that when uploading bulk records to SQL Server database through VB.Net it creates a lot of EMPTY records after Inserting all records inside the xls file

how to eliminate this issue. taking into consideration that Excel.xls file has no Empty records .

What I have tried:

Private Sub browseXLfile_Click(sender As Object, e As EventArgs) Handles browseXLfile.Click
    btnCustRefresh.PerformClick()

    Dim SystemUserName As String = Environment.UserName
    Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Users WHERE Username = @SystemUserName AND Role = 'Super-User' ", cnnOLEDB)

    If cnnOLEDB.State = ConnectionState.Closed Then
        cnnOLEDB.Open()
    End If

    'cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@SystemUserName", SystemUserName)
    Dim sdr As SqlDataReader = cmd.ExecuteReader()

    If (sdr.Read() = True) Then
        Dim ofd As New OpenFileDialog
        If ofd.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then Exit Sub
        Dim nme As String = ofd.FileName
        Dim safename As String = ofd.SafeFileName
        safename = safename.Substring(0, safename.LastIndexOf("."))
        Import(nme, DataGridViewCustomer, safename)
    Else
        MessageBox.Show("You have no access. You are not listed in the Admins list ", Me.Text)
    End If
    sdr.Close()
End Sub

Public Shared Function Import(ByVal FileName As String, ByVal DataGridViewCustomer As DataGridView, ByVal safefilename As String) As Boolean
    Try
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Customers", safefilename)
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridViewCustomer.DataSource = DtSet.Tables(0)
        MyConnection.Close()

        Dim expr As String = "SELECT * FROM [Sheet1$]"
        Dim SQLconn As New SqlConnection()
        'Dim ConnString As String = "Data Source=EB-5CG7476R7V\SQLEXPRESS;Initial Catalog=SupplierSQL_DB;Integrated Security=True"
        Dim ConnString As String = "Server=tcp:server-name,1433;Initial Catalog=SupplierDB;Persist Security Info=False;User ID=SERVERUSER;Password=SERVERPASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, MyConnection)
        Dim objDR As OleDbDataReader
        SQLconn.ConnectionString = ConnString

        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString)
            bulkCopy.DestinationTableName = safefilename
            Try
                MyConnection.Open()
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                SQLconn.Close()

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

        Return True
    Catch ex As Exception
        Return False
    End Try
End Function
Posted
Updated 7-Oct-19 21:54pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
taking into consideration that Excel.xls file has no Empty records

Are you sure?
Most Excel files have more empty records than non-empty: all that lovely whitespace at teh bottom!
Since you don't specify any limits on what rows are returned:
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

Dim expr As String = "SELECT * FROM [Sheet1$]"
You need to use the debugger and see exactly what is returned - because if Excel gives you loads of blank rows at the bottom, then SQL doesn't "know" they aren't "real data" and will insert them happily.
Computers don't "invent" data, even "blank data" - it comes from somewhere, and my guess would be from your sheet.
   
Comments
BassamKassem 8-Oct-19 6:44am
   
Thanks bro you are genius , here is a fix based on your recommendations

Dim expr As String = "SELECT * FROM [Sheet1$] WHERE ID > '0'"
OriginalGriff 8-Oct-19 6:47am
   
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100