Click here to Skip to main content
14,695,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello i'm having trouble here with my code:
Problem is that when i try use import option my datagridview is blank (silver) no rows showing. But if i disable the code when form loads to load in datagridview information and click to import then it shows it.

My problem is this:
in my datagridview i have custom button maded inside for delete rows, and my export saves also this one in the excel, i do not want to do this without this row to save it.

Imports System.Linq
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat
Imports Microsoft.Office.Interop
Imports System.IO
Imports System.Xml.XPath
Imports System.Data
Imports System.Xml
Public Class Testing

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim conn As OleDbConnection
        Dim dtr As OleDbDataReader
        Dim dta As OleDbDataAdapter
        Dim cmd As OleDbCommand
        Dim dts As DataSet
        Dim excel As String
        Dim OpenFileDialog As New OpenFileDialog

        OpenFileDialog1.FileName = ""
        OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
        OpenFileDialog1.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"

        If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
            DataGridView1.Columns.Clear()

            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim FileName As String = OpenFileDialog1.FileName

            excel = fi.FullName
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
            dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
            dts = New DataSet
            dta.Fill(dts, "[Sheet1$]")
            DataGridView1.DataSource = dts
            DataGridView1.DataMember = "[Sheet1$]"
            conn.Close()
        End If
    End Sub

    Private Sub Testing_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        RadioButton1.Checked = True
        Using con As New OleDbConnection(ServerStatus)
            Using cmd As New OleDbCommand("SELECT * FROM Connectors order by ID", con)
                cmd.Connection = con
                cmd.CommandType = CommandType.Text
                Using sda As New OleDbDataAdapter(cmd)
                    Using dta As New DataTable()
                        sda.Fill(dta)
                        DataGridView1.DataSource = Nothing
                        'Set AutoGenerateColumns False
                        DataGridView1.AutoGenerateColumns = False
                        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                        'DataDisplay.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
                        'DataDisplay.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                        'DataDisplay.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                        DataGridView1.AllowUserToResizeColumns = False
                        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
                        DataGridView1.AllowUserToResizeRows = False
                        'DataDisplay.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
                        'DataDisplay.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                        DataGridView1.AutoResizeColumns()


                        'Set Columns Count
                        DataGridView1.ColumnCount = 6

                        'Add Columns
                        DataGridView1.Columns(0).Name = "ID"
                        DataGridView1.Columns(0).HeaderText = "ID"
                        DataGridView1.Columns(0).DataPropertyName = "ID"

                        DataGridView1.Columns(1).Name = "cName"
                        DataGridView1.Columns(1).HeaderText = "Name"
                        DataGridView1.Columns(1).DataPropertyName = "cName"

                        DataGridView1.Columns(2).Name = "cYazaki"
                        DataGridView1.Columns(2).HeaderText = "Yazaki"
                        DataGridView1.Columns(2).DataPropertyName = "cYazaki"

                        DataGridView1.Columns(3).Name = "cSupplier"
                        DataGridView1.Columns(3).HeaderText = "Supplier"
                        DataGridView1.Columns(3).DataPropertyName = "cSupplier"

                        DataGridView1.Columns(4).Name = "cStore"
                        DataGridView1.Columns(4).HeaderText = "Store"
                        DataGridView1.Columns(4).DataPropertyName = "cStore"

                        DataGridView1.Columns(5).Name = "cCount"
                        DataGridView1.Columns(5).HeaderText = "Count"
                        DataGridView1.Columns(5).DataPropertyName = "cCount"

                        'Add the Button Column.
                        Dim buttonColumn As DataGridViewButtonColumn = New DataGridViewButtonColumn()
                        buttonColumn.Name = "cDelete"
                        buttonColumn.HeaderText = "Delete"
                        buttonColumn.Text = "Delete"
                        buttonColumn.FlatStyle = FlatStyle.Flat
                        buttonColumn.CellTemplate.Style.BackColor = System.Drawing.Color.White
                        buttonColumn.UseColumnTextForButtonValue = True
                        DataGridView1.Columns.Insert(6, buttonColumn)
                        'End

                        DataGridView1.DataSource = dta

                    End Using
                End Using
            End Using
        End Using

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            Button2.Text = "Please Wait..."
            Button2.Enabled = False

            SaveFileDialog1.Filter = "Excel Document (*.xlsx)|*.xlsx"
            If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                Dim xlApp As Microsoft.Office.Interop.Excel.Application
                Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
                Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
                Dim misValue As Object = System.Reflection.Missing.Value
                Dim i As Integer
                Dim j As Integer

                xlApp = New Microsoft.Office.Interop.Excel.Application
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = xlWorkBook.Sheets("sheet1")

                For i = 0 To DataGridView1.RowCount - 2
                    For j = 0 To DataGridView1.ColumnCount - 1
                        For k As Integer = 1 To DataGridView1.Columns.Count
                            xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                            xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                        Next
                    Next
                Next

                xlWorkSheet.SaveAs(SaveFileDialog1.FileName)
                xlWorkBook.Close()
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)

                MsgBox("Successfully saved" & vbCrLf & "File are saved at : " & SaveFileDialog1.FileName, MsgBoxStyle.Information, "Information")

                Button2.Text = "Export To MS Excel"
                Button2.Enabled = True
            End If
        Catch ex As Exception
            MessageBox.Show("Failed to save !!!", "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End Try
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub SaveFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles SaveFileDialog1.FileOk

    End Sub
End Class


How can i manage to fix this options?
When click Export->save the file but without the button (Delete)
When click Import->to refresh the datagridview with the information from excel file (that is without the Delete, because datagridview will create it custom button)

If someone can assist me here.

What I have tried:

If i remove the Form load, then import works.
Posted

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900