teogos
asked on
COPY FROM EXCEL TO DATAGRIDVIEW THEN SAVE TO SQL
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(strSpli tter, StringSplitOptions.RemoveE mptyEntrie s)
Dim iRows As Integer = strRows.Length
Dim strCells() As String = strRows(0).Split(strSplitt erC, StringSplitOptions.None)
Dim iCells As Integer = strCells.Length
DataGridView3.RowCount = iRows
DataGridView3.ColumnCount = iCells
For iColCounter As Integer = 0 To iCells - 1
DataGridView3.Columns(iCol Counter).N ame = strCells(iColCounter)
Next
DataGridView3.Rows(0).Sele cted = True
For iRowCounter As Integer = 1 To iRows - 1
strCells = strRows(iRowCounter).Split (strSplitt erC, StringSplitOptions.None)
For iCellCounter As Integer = 0 To iCells - 1
DataGridView3.Rows(iRowCou nter - 1).Cells(iCellCounter).Val ue = 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.DestinationTableNam e = "[xxxxxx]." & TableName & ""
Sqlbcp.WriteToServer(dt)
End Using
End If
since there is not Datasource for DATAGRIDVIEW3 because is not databound
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(strSpli
Dim iRows As Integer = strRows.Length
Dim strCells() As String = strRows(0).Split(strSplitt
Dim iCells As Integer = strCells.Length
DataGridView3.RowCount = iRows
DataGridView3.ColumnCount = iCells
For iColCounter As Integer = 0 To iCells - 1
DataGridView3.Columns(iCol
Next
DataGridView3.Rows(0).Sele
For iRowCounter As Integer = 1 To iRows - 1
strCells = strRows(iRowCounter).Split
For iCellCounter As Integer = 0 To iCells - 1
DataGridView3.Rows(iRowCou
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.DestinationTableNam
Sqlbcp.WriteToServer(dt)
End Using
End If
since there is not Datasource for DATAGRIDVIEW3 because is not databound
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.