Solved

COPY FROM EXCEL TO DATAGRIDVIEW THEN SAVE TO SQL

Posted on 2013-05-15
1
497 Views
Last Modified: 2013-05-22
I want to copy from excel to a clipboard then paste into a datagridview   then save to SQL TABLE

to copy and PASTE to the clipboard I am using this code below which works fine

Dim strPasteText As String = Clipboard.GetText
        Dim strSplitter() As String = {vbCrLf}
        Dim strSplitterC() As String = {vbTab}
        Dim strRows() As String = strPasteText.Split(strSplitter, StringSplitOptions.RemoveEmptyEntries)
        Dim iRows As Integer = strRows.Length
        Dim strCells() As String = strRows(0).Split(strSplitterC, StringSplitOptions.None)
        Dim iCells As Integer = strCells.Length
       
        DataGridView3.RowCount = iRows
        DataGridView3.ColumnCount = iCells

        For iColCounter As Integer = 0 To iCells - 1
            DataGridView3.Columns(iColCounter).Name = strCells(iColCounter)
        Next

        DataGridView3.Rows(0).Selected = True

        For iRowCounter As Integer = 1 To iRows - 1
            strCells = strRows(iRowCounter).Split(strSplitterC, StringSplitOptions.None)

            For iCellCounter As Integer = 0 To iCells - 1
                DataGridView3.Rows(iRowCounter - 1).Cells(iCellCounter).Value = strCells(iCellCounter)
            Next
        Next




But the problem comes when I tried to bulk write  using
Dim dt As New DataTable
        dt = DataGridView3.DataSource
        Dim objSQLCon As New SqlConnection("Data Source=xxxxxxx;Initial Catalog=xxxxxx;Integrated Security=SSPI;User ID=xxxxr;Password=xxxxxxx")
        Dim TableName As String = "[xxxxxx].MASTERSCHEDULE"
        Dim strErrorMessage As String = ""

        Try

        Catch ex As Exception

        End Try
        If objSQLCon.State = ConnectionState.Open Then
            objSQLCon.Close()
        End If

        objSQLCon.Open()

        Try

        Catch ex As Exception

        End Try
        'To Perform Bulk Copy for copy data from DataTable to SQL Table
        If dt.Rows.Count > 0 Then
            Using Sqlbcp As SqlBulkCopy = New SqlBulkCopy(objSQLCon)
                Sqlbcp.DestinationTableName = "[xxxxxx]." & TableName & ""
                Sqlbcp.WriteToServer(dt)
            End Using
        End If

since there is not Datasource for DATAGRIDVIEW3  because is not databound
0
Comment
Question by:teogos
1 Comment
 
LVL 16

Accepted Solution

by:
Rose Babu earned 500 total points
ID: 39170997
Hi,

at the run-time only you have pasted the data to DataGridView. so at the beginning, the DataSource will be null.

and when you get the DataGridView's DataSource after you pasted the data, then also it will be empty.

to overcome this, loop the datagridview and load all the data into a datatable then use the updated datatable to bulkcopy process

Have a look on the updated code. this works well for me.

Dim dtUpdated = New DataTable
Dim drUpdated As DataRow
For Each col As DataGridViewColumn In DataGridView2.Columns
	dtUpdated.Columns.Add(col.Name)
Next

Dim j As Integer = 0

Dim r As New DataGridViewRow
' The footer new row is also adding as empty row. so i used DataGridView2.Rows.Count - 2. update it accordingly
For i = 0 To DataGridView2.Rows.Count - 2
	r = DataGridView2.Rows(i)

	drUpdated = dtUpdated.NewRow
	For Each cells As DataGridViewCell In r.Cells
		drUpdated(j) = cells.Value
		j = j + 1
	Next
	dtUpdated.Rows.Add(drUpdated)
	j = 0
Next

Dim objSQLCon As New SqlClient.SqlConnection("server=*********;Initial Catalog=*********;Integrated Security=SSPI;")
Dim TableName As String = "BulkCopyTest"
Dim strErrorMessage As String = ""

Try

	If objSQLCon.State = ConnectionState.Open Then
		objSQLCon.Close()
	End If

	objSQLCon.Open()


	'To Perform Bulk Copy for copy data from DataTable to SQL Table
	If dtUpdated.Rows.Count > 0 Then
		Using Sqlbcp As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(objSQLCon)
			Sqlbcp.DestinationTableName = TableName
			Sqlbcp.WriteToServer(dtUpdated)
		End Using
	End If

	objSQLCon.Close()
Catch ex As Exception
	objSQLCon.Close()
End Try

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop not working 29 47
Format column on datatable 7 28
How do ASP.NET and MVC work together? 4 27
Code works but it's slow 23 30
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now