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.