By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,694 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,694 IT Pros & Developers. It's quick & easy.

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

P: n/a
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(DataGridVi ewRowHeadersWidthSizeMode.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:office: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:Pattern=""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:Data ss:Type=""String"">{0}</ss:Data></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:Data ss:Type=""String"">{0}</ss:Data></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
Jun 27 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"TG" <jt*****@yahoo.comwrote in message
news:8b**********************************@w7g2000h sa.googlegroups.com...
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.


Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.