T
TG
Hi!
I have an application in which I have some checkboxes and depending
which ones are checked those columns will show in the datagridview
from sql server or no.
After that I have 2 buttons:
1) export to excel button exports the visible columns from the
datagridview to excel (this works fine)
2) create temp table: this is what I am stuck with and don't know how
to make it work. I need to export the VISIBLE COLUMNS from the
datagridview into a newly created table in a database in sql server. I
want to create the table on the fly here with the visible columns from
the datagridview.
What is the best way to achieve item 2)???
Thanks a lot for your help!
Tammy
Below is the code for my application:
Imports System
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Windows.Forms
Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Declare Function ShellEx Lib "shell32.dll" Alias
"ShellExecuteA" ( _
ByVal hWnd As Integer, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Integer) As
Integer
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
'just list local servers, set to false if you want to see all
servers
'Dim dataTable = SmoApplication.EnumAvailableSqlServers("dr-ny-
sql002")
Dim dataTable = SmoApplication.EnumAvailableSqlServers(False)
lstServers.ValueMember = "Name"
lstServers.DataSource = dataTable
End Sub
Private Sub lstServers_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lstServers.SelectedIndexChanged
lstDatabases.Items.Clear()
If lstServers.SelectedIndex <> -1 Then
Dim serverName As String =
lstServers.SelectedValue.ToString()
Dim server As Server = New Server(serverName)
Try
For Each database As Database In server.Databases
lstDatabases.Items.Add(database.Name)
Next
Catch ex As Exception
Dim exception As String = ex.Message
End Try
End If
End Sub
Private Sub lstDatabases_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles lstDatabases.Click
lstFileSets.Items.Clear()
Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()
Dim dr As SqlClient.SqlDataReader
With cn
.ConnectionString = "Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI"
.Open()
End With
With cm
.CommandText = "usp_DR_Spam_BB_Search_filesets"
.CommandType = CommandType.StoredProcedure
.Connection = cn
.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
End With
dr = cm.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read
lstFileSets.Items.Add(dr.Item(0))
End While
dr.Close()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button3.Click
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim cn As New SqlConnection("Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI")
Dim cmd As New SqlCommand("usp_DR_Spam_BB_Search_get_recs",
cn)
cmd.CommandTimeout = 0
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
cmd.Parameters.AddWithValue("@FileSet",
lstFileSets.SelectedItem)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim ds As New DataSet()
Dim dt As New DataTable("Table1")
ds.Tables.Add(dt)
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
DataGridView1.Font = New Font("SansSerif", 8.25,
FontStyle.Regular)
DataGridView1.DataSource = ds.Tables(0)
If DataGridView1.Rows.Count > 0 Then
If CheckBox1.CheckState = True Then
DataGridView1.Columns("Last Name").Visible = True
ElseIf CheckBox1.CheckState = False Then
DataGridView1.Columns("Last Name").Visible = False
End If
If CheckBox2.CheckState = True Then
DataGridView1.Columns("First Name").Visible = True
ElseIf CheckBox2.CheckState = False Then
DataGridView1.Columns("First Name").Visible = False
End If
If CheckBox3.CheckState = True Then
DataGridView1.Columns("Domain").Visible = True
ElseIf CheckBox3.CheckState = False Then
DataGridView1.Columns("Domain").Visible = False
End If
If CheckBox4.CheckState = True Then
DataGridView1.Columns("Email").Visible = True
ElseIf CheckBox4.CheckState = False Then
DataGridView1.Columns("Email").Visible = False
End If
If CheckBox5.CheckState = True Then
DataGridView1.Columns("Subject").Visible = True
ElseIf CheckBox5.CheckState = False Then
DataGridView1.Columns("Subject").Visible = False
End If
Else
MessageBox.Show("There are no records using the fileset
selected, please try with a different fileset")
End If
Dim rowNumber As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
row.HeaderCell.Value = rowNumber.ToString
rowNumber = rowNumber + 1
Next
DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
Cursor.Current = System.Windows.Forms.Cursors.Default
'cmd.Connection.Close()
End Sub
Private Sub exportExcel(ByVal grdView As DataGridView, ByVal
fileName As String, _
ByVal fileExtension As String, ByVal filePath As String)
' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName &
fileExtension
' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-
com
ffice:spreadsheet"">")
' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center""
ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0""
ss
attern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center""
ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Column
ss:Width=""{0}""/>", col.Width))
End If
Next
fs.WriteLine(" <ss:Row>")
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""1""><ss
ata ss:Type=""String"">{0}</ssata></ss:Cell>",
col.HeaderText))
End If
Next
fs.WriteLine(" </ss:Row>")
' Check for an empty row at the end due to Adding allowed on
the DataGridView
Dim subtractBy As Integer
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else
subtractBy = 1
' Write contents for each cell
For Each row As DataGridViewRow In grdView.Rows
fs.WriteLine(String.Format(" <ss:Row
ss:Height=""{0}"">", row.Height))
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""2""><ssata ss:Type=""String"">{0}</ssata></ss:Cell>",
row.Cells(col.Name).Value.ToString))
End If
Next
fs.WriteLine(" </ss:Row>")
Next
' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
' Open the file in Microsoft Excel
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", myFile, "", "", 10)
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button4.Click
' Call the export sub
exportExcel(DataGridView1, "exportedData", ".xlsx",
My.Computer.FileSystem.SpecialDirectories.Desktop)
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button5.Click
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button6.Click
Me.Close()
End Sub
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button7.Click
lstServers.ClearSelected()
lstDatabases.Items.Clear()
lstFileSets.Items.Clear()
DataGridView1.DataSource = Nothing
End Sub
End Class
I have an application in which I have some checkboxes and depending
which ones are checked those columns will show in the datagridview
from sql server or no.
After that I have 2 buttons:
1) export to excel button exports the visible columns from the
datagridview to excel (this works fine)
2) create temp table: this is what I am stuck with and don't know how
to make it work. I need to export the VISIBLE COLUMNS from the
datagridview into a newly created table in a database in sql server. I
want to create the table on the fly here with the visible columns from
the datagridview.
What is the best way to achieve item 2)???
Thanks a lot for your help!
Tammy
Below is the code for my application:
Imports System
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Windows.Forms
Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Declare Function ShellEx Lib "shell32.dll" Alias
"ShellExecuteA" ( _
ByVal hWnd As Integer, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Integer) As
Integer
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
'just list local servers, set to false if you want to see all
servers
'Dim dataTable = SmoApplication.EnumAvailableSqlServers("dr-ny-
sql002")
Dim dataTable = SmoApplication.EnumAvailableSqlServers(False)
lstServers.ValueMember = "Name"
lstServers.DataSource = dataTable
End Sub
Private Sub lstServers_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lstServers.SelectedIndexChanged
lstDatabases.Items.Clear()
If lstServers.SelectedIndex <> -1 Then
Dim serverName As String =
lstServers.SelectedValue.ToString()
Dim server As Server = New Server(serverName)
Try
For Each database As Database In server.Databases
lstDatabases.Items.Add(database.Name)
Next
Catch ex As Exception
Dim exception As String = ex.Message
End Try
End If
End Sub
Private Sub lstDatabases_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles lstDatabases.Click
lstFileSets.Items.Clear()
Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()
Dim dr As SqlClient.SqlDataReader
With cn
.ConnectionString = "Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI"
.Open()
End With
With cm
.CommandText = "usp_DR_Spam_BB_Search_filesets"
.CommandType = CommandType.StoredProcedure
.Connection = cn
.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
End With
dr = cm.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read
lstFileSets.Items.Add(dr.Item(0))
End While
dr.Close()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button3.Click
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim cn As New SqlConnection("Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI")
Dim cmd As New SqlCommand("usp_DR_Spam_BB_Search_get_recs",
cn)
cmd.CommandTimeout = 0
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
cmd.Parameters.AddWithValue("@FileSet",
lstFileSets.SelectedItem)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim ds As New DataSet()
Dim dt As New DataTable("Table1")
ds.Tables.Add(dt)
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
DataGridView1.Font = New Font("SansSerif", 8.25,
FontStyle.Regular)
DataGridView1.DataSource = ds.Tables(0)
If DataGridView1.Rows.Count > 0 Then
If CheckBox1.CheckState = True Then
DataGridView1.Columns("Last Name").Visible = True
ElseIf CheckBox1.CheckState = False Then
DataGridView1.Columns("Last Name").Visible = False
End If
If CheckBox2.CheckState = True Then
DataGridView1.Columns("First Name").Visible = True
ElseIf CheckBox2.CheckState = False Then
DataGridView1.Columns("First Name").Visible = False
End If
If CheckBox3.CheckState = True Then
DataGridView1.Columns("Domain").Visible = True
ElseIf CheckBox3.CheckState = False Then
DataGridView1.Columns("Domain").Visible = False
End If
If CheckBox4.CheckState = True Then
DataGridView1.Columns("Email").Visible = True
ElseIf CheckBox4.CheckState = False Then
DataGridView1.Columns("Email").Visible = False
End If
If CheckBox5.CheckState = True Then
DataGridView1.Columns("Subject").Visible = True
ElseIf CheckBox5.CheckState = False Then
DataGridView1.Columns("Subject").Visible = False
End If
Else
MessageBox.Show("There are no records using the fileset
selected, please try with a different fileset")
End If
Dim rowNumber As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
row.HeaderCell.Value = rowNumber.ToString
rowNumber = rowNumber + 1
Next
DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
Cursor.Current = System.Windows.Forms.Cursors.Default
'cmd.Connection.Close()
End Sub
Private Sub exportExcel(ByVal grdView As DataGridView, ByVal
fileName As String, _
ByVal fileExtension As String, ByVal filePath As String)
' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName &
fileExtension
' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-
com
ffice:spreadsheet"">")' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center""
ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0""
ss
attern=""Solid""/>")fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center""
ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Column
ss:Width=""{0}""/>", col.Width))
End If
Next
fs.WriteLine(" <ss:Row>")
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""1""><ss
ata ss:Type=""String"">{0}</ssata></ss:Cell>",col.HeaderText))
End If
Next
fs.WriteLine(" </ss:Row>")
' Check for an empty row at the end due to Adding allowed on
the DataGridView
Dim subtractBy As Integer
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else
subtractBy = 1
' Write contents for each cell
For Each row As DataGridViewRow In grdView.Rows
fs.WriteLine(String.Format(" <ss:Row
ss:Height=""{0}"">", row.Height))
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""2""><ss
ata ss:Type=""String"">{0}</ssata></ss:Cell>",row.Cells(col.Name).Value.ToString))
End If
Next
fs.WriteLine(" </ss:Row>")
Next
' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
' Open the file in Microsoft Excel
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", myFile, "", "", 10)
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button4.Click
' Call the export sub
exportExcel(DataGridView1, "exportedData", ".xlsx",
My.Computer.FileSystem.SpecialDirectories.Desktop)
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button5.Click
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button6.Click
Me.Close()
End Sub
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button7.Click
lstServers.ClearSelected()
lstDatabases.Items.Clear()
lstFileSets.Items.Clear()
DataGridView1.DataSource = Nothing
End Sub
End Class