Link to home
Create AccountLog in
Avatar of teogos
teogosFlag for United States of America

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(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
ASKER CERTIFIED SOLUTION
Avatar of Rose Babu
Rose Babu
Flag of India image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer