how to create a table in sql server from a datagridview

[jtammyg713]

Hi!

I have a datagridview with hidden columns. I want to create a table in a sql server programmatically and then only export the visible columns form the datagridview to it.

How can I achieve this?

Thanks a lot for the help to this newbie!!!

Tammy

 

[chiph]

Take a look at the Server Management Objects (SMO)
It will let you programatically create SQL Server 2005 databases, tables, etc.

Note that you will have to write some SQL. This is not the sort of thing that you can do via data binding.

Chip H.


____________________________________________________________________
www.chipholland.com

 

[jtammyg713]

I found this online but I am getting stuck at the with cn part in which i get an error :

value of type 'system.data.sqlclient.sqlconnection' cannot be converted to 'string'


Below is the code I am trying to use:


Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

'Declare Variables - Edit these based on your particular situation
Dim sSQLTable As String = "master.dbo.TEST_TAMMY"
Dim sExcelFileName As String = "C:\casey.xls"
Dim sWorkbook As String = "[Sheet1$]"

'Create our connection strings
Dim cn As New SqlClient.SqlConnection()
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & (sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""
'Dim sSqlConnectionString As String = sSqlConnectionString.ConnectionString = ("Data Source=" & lstServers.SelectedValue & ";Initial Catalog=" & lstDatabases.SelectedValue & ";Integrated Security=SSPI").ToString

With cn
.ConnectionString = "Data Source=" & lstServers.SelectedValue & ";Initial Catalog=" & lstDatabases.SelectedValue & ";Integrated Security=SSPI"
.Open()
End With

'Execute a query to erase any previous data from our destination table
Dim sClearSQL = "DELETE FROM " & sSQLTable
Dim SqlConn As SqlConnection = New SqlConnection(cn)
Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
SqlConn.Open()
SqlCmd.ExecuteNonQuery()
SqlConn.Close()

'Series of commands to bulk copy data from the excel file into our SQL table
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)

OleDbConn.Open()

Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(cn)

bulkCopy.DestinationTableName = sSQLTable
bulkCopy.WriteToServer(dr)

OleDbConn.Close()





End Sub



Whta should I change to make it work?

Thanks a lot from a newbie!!!!

Tammy

 

[Sorwen]

Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(cn)

Look at SqlBulkCopy. It sounds like it is looking for a string and you are trying to pass a connection. This doesn't do what you where asking about though. This tries to connect to an Excel workbook and send the data to an SQL table.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jtammyg713]

sorwen,

This is the solution I came up with...but I cannot get it to work.

'Declare Variables - Edit these based on your particular situation
Dim sSQLTable As String = "client_lehman_MBS.dbo.TEST_TAMMY"
Dim sExcelFileName As String = "C:\casey.xls"
Dim sWorkbook As String = "[Sheet1$]"

'Create our connection strings
Dim connection As New SqlClient.SqlConnection()
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & (sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""
'Dim sSqlConnectionString As String = sSqlConnectionString.ConnectionString = ("Data Source=" & lstServers.SelectedValue & ";Initial Catalog=" & lstDatabases.SelectedValue & ";Integrated Security=SSPI").ToString

With connection
.ConnectionString = "Data Source=" & lstServers.SelectedValue & ";Initial Catalog=" & lstDatabases.SelectedValue & ";Integrated Security=SSPI"
.Open()
End With

'Execute a query to erase any previous data from our destination table
Dim sClearSQL = "DELETE FROM " & sSQLTable
Dim SqlConn As SqlConnection = New SqlConnection(connection)
Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
SqlConn.Open()
SqlCmd.ExecuteNonQuery()
SqlConn.Close()

'Series of commands to bulk copy data from the excel file into our SQL table
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)

OleDbConn.Open()

Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(connection)

bulkCopy.DestinationTableName = sSQLTable
bulkCopy.WriteToServer(dr)

OleDbConn.Close()



Now I am getting an error at

SqlConn.Open()

saying connection has not been initialized.

Please help!!!!

Thanks a lot!

Tammy

 

[Sorwen]

 Dim SqlConn As SqlConnection = New SqlConnection(connection)

You should have gotten an error here. You have to pass the string not a connection.

It is untested, but try this:

        'Declare Variables - Edit these based on your particular situation
        Dim sSQLTable As String = "client_lehman_MBS.dbo.TEST_TAMMY"
        Dim sExcelFileName As String = "C:\casey.xls"
        Dim sWorkbook As String = "[Sheet1$]"

        'Create our connection strings
        Dim strConn As String = "Data Source=" & lstServers.SelectedValue & ";Initial Catalog=" & lstDatabases.SelectedValue & ";Integrated Security=SSPI"
        Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & (sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""


        'Execute a query to erase any previous data from our destination table
        Dim sClearSQL = "DELETE FROM " & sSQLTable
        Dim SqlConn As SqlConnection = New SqlConnection(strConn)
        Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
        SqlConn.Open()
        SqlCmd.ExecuteNonQuery()

        'Series of commands to bulk copy data from the excel file into our SQL table
        Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
        Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)

        OleDbConn.Open()

        Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
        Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(SqlConn)

        bulkCopy.DestinationTableName = sSQLTable
        bulkCopy.WriteToServer(dr)

        OleDbConn.Close()
        SqlConn.Close()

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jtammyg713]

Sorwen,

You are a genius!!!!!!

That worked!!!!!

Now I have to be able to create the table on the fly, before bulkcopying to it!!!!!

Thanks a lot!!!!!!!!!!!!!!!!!!

You made my day!!!

Tammy

 

[Sorwen]

NP. I'm gald it worked.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!