Add rows per data copied from excel to datagrid

[jared.geli]

Good day guys I need help for adding rows in datagrid where the rows to be added
are equal to the number of rows copied from excel. I can copy the data but it displays
only the first row from excel on the last row of datagrid but if I add let's just say 4
rows in datagrid 4 rows are copied from excel. What I want is to add rows equal
to the rows copied from excel to datagrid. If possible I f it can add another row
just like when you put data in a row it will automatically add one below the row you
are putting the data

 Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim s As String
        Try


            s = Clipboard.GetText()
            Dim i, ii As Integer

            Dim tArr() As String = s.Split(ControlChars.NewLine)
            Dim arT() As String
            Dim cc, iRow, iCol As Integer

            iRow = DataGridView1.SelectedCells(0).RowIndex
            iCol = DataGridView1.SelectedCells(0).ColumnIndex
            For i = 0 To tArr.Length - 1
                If tArr(i) <> "" Then
                    arT = tArr(i).Split(vbTab)
                    cc = iCol
                    For ii = 0 To arT.Length - 1
                        If cc > DataGridView1.ColumnCount - 1 Then Exit For
                        If iRow > DataGridView1.Rows.Count - 1 Then Exit Sub
                        With DataGridView1.Item(cc, iRow)
                            .Value = arT(ii).TrimStart

                        End With
                        cc = cc + 1
                    Next
                    iRow = iRow + 1
                End If

            Next

        Catch ex As Exception
            MsgBox("Please redo Copy and Click on cell")
        End Try
    End Sub

Source:http://franshbotes.blogspot.com/2011/07/paste-excel-data-into-datagrid-in-vbnet.html

[ddanbe]

There exists a Rows.Add method.
You could do it like this:
DataGridView1.Rows.Add(arT);(arT, your string array from line 17)
Look here for another example.

[jared.geli]

where can I put the DataGridView1.Rows.Add(arT)?

[ddanbe]

Well just after line 17.
Forget all the for-loop mumbo jumbo, that follows, remove it.
The Rows Add method, takes care of all the creepy details.

[jared.geli]

I'm sorry but I don't get it..........To be honest I'm confused

[ddanbe]

No problem if you are confused, we are here, trying to help you out.
This is how I see it:

Try
    s = Clipboard.GetText()
    Dim i, ii As Integer
    Dim tArr() As String = s.Split(ControlChars.NewLine)
    Dim arT() As String

    For i = 0 To tArr.Length - 1
        If tArr(i) <> "" Then
            arT = tArr(i).Split(vbTab)
            DataGridView1.Rows.Add(arT);
        End If
    Next
Catch ex As Exception
    MsgBox("Please redo Copy and Click on cell")
End Try

Hope this clears things up, if not, just ask.

[jared.geli]

there's an error it says:Rows cannot be programmatically added to the DataGridView's rows collection when the control is data-bound.

My datagridview shows the data from my db:

Here's the code how I connect my db to datagrid:

 Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;data source=F:\Office\VehicleAltered.accdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT EmpID, Driver as [Name] FROM Data4", con)
        con.Open()
        adpt = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
        DtSet = New DataSet()
        adpt.Fill(DtSet, "Data4")
        DataGridView1.DataSource = DtSet.Tables("Data4").DefaultView
        con.Close()
        con = Nothing

[ddanbe]

So, as I understand you well, you have a Datagridview connected with a database and you want to do some copy/paste from an external excel sheet to the datagridview?

[jared.geli]

yes

[ddanbe]

What happens if you do that as DtSet.Tables.Rows.Add(....) and a Refresh?

[jared.geli]

Rows is not a member of 'System.Data.DataTableCollection'

[ddanbe]

According to MSDN there is a Rows property.
But sorry if I gave some bad info, I find it a bit complicated myself and it is some time ago I used a DataTable.
You must first use the NewRow method before you can Add the row.

[jared.geli]

It's ok. How about this one

 Dim rowSplitter As Char() = {vbCr, vbLf}
        Dim columnSplitter As Char() = {vbTab}

        'get the text from clipboard

        Dim dataInClipboard As IDataObject = Clipboard.GetDataObject()
        Dim stringInClipboard As String = CStr(dataInClipboard.GetData(DataFormats.Text))

        'split it into lines
        Dim rowsInClipboard As String() = stringInClipboard.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries)

        'get the row and column of selected cell in grid
        Dim r As Integer = dgv.SelectedCells(0).RowIndex
        Dim c As Integer = dgv.SelectedCells(0).ColumnIndex

        'add rows into grid to fit clipboard lines
        If (dgv.Rows.Count < (r + rowsInClipboard.Length)) Then
            dgv.Rows.Add(r + rowsInClipboard.Length - dgv.Rows.Count)
        End If

        ' loop through the lines, split them into cells and place the values in the corresponding cell.
        Dim iRow As Integer = 0
        While iRow < rowsInClipboard.Length
            'split row into cell values
            Dim valuesInRow As String() = rowsInClipboard(iRow).Split(columnSplitter)
            'cycle through cell values
            Dim iCol As Integer = 0
            While iCol < valuesInRow.Length
                'assign cell value, only if it within columns of the grid
                If (dgv.ColumnCount - 1 >= c + iCol) Then
                    dgv.Rows(r + iRow).Cells(c + iCol).Value = valuesInRow(iCol)
                End If
                iCol += 1
            End While
            iRow += 1
        End While

I used that code first but it doesn't work.
dgv is DataGridView1

[jared.geli]

Hi ddanbe thanks for suggesting me aboy the DtSet.Tables(0).Rows.Add(). I figured out how I can do it but not exactly what I wanted. I created a keydown event in a textbox and loop the DtSet.Tables(0).Rows.Add() according to the number you inputed in the textbox. Thanks again

[richardsonka]

Up until recently, I have bound the datatable directly to the DataGridView; ,however I now impose a BindingSource into the mix as it has other benefits not relevant to this article. So, bind the data to the DataGridView.
private void btnGetData_Click(object sender, EventArgs e)