how to create table in sql server from either a datagridview or excelfile

[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-
comffice: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""
ssattern=""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""><ssata 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

 

[Mr. Arnold]

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!

The best you could hope for is to make a generic/dynamic table to hold
column name and column data.

columnname1 = "HelpColumn"
columndata1 = "Help Me"
columnname2 = "Address1"
columndata2 = "999 Home"

You map data to the table's generic column-name and column-data fields on
the table, and you map back to the fields and data required by the program.

You could have a 100 field table, as an example.