Copy Data from One DataGridView to Another in Vb.Net

February 04th, 2013
Single row or multiple rows can be transferred from one DataGridView to another with help of few lines of code. Why would somebody do that? We have come across a situation where we had to upgrade details about existing records with new details without changing the original one.

So we came up with a simple idea. Data will be populated in a DataGridView and if a particular rows data has to be modified, the row will be double clicked and data will be transferred to another DataGridView for modification.

This demo is based on the above mentioned idea.

1) Fetch data from the database and populate it in a DataGridView (Parent Grid).

2) Double click a “Row” or “Cell” to copy data from Parent Grid to Child Grid and show it.

DataGridView - Copy Row

Start “Microsoft Visual Studio” and select a “New Project” from “File” (Left Top Menu). From the “New Project” templates, select “Windows Forms Application” and click OK.

1) A new project appears with a “Blank Form”. Click the “Toolbox button” and from the list of tools “Double Click” to add a “Panel” control on the form. (Panel1)

2) Drag a “DataGridView” control and drop it on the Panel. Name the DataGridView as “DGVParent”. Set the grid’s “Drop” property as “Fill”.

3) Drag and drop another “Panel” (Panel2) from the tool box and drop it on the “Parent Grid”. Set this panel’s “Visible” property as “false”.

4) Finally, drag another DataGridView control and drop it on the “Second Panel”.

Note: The “Panels” we have added on the form, actually works as a container. You can use the “Group Box” control either.

The second panel will remain hidden, till the user “Double Clicks” one of the parent grid’s rows. This will show the “Panel2” with the “Child Grid” below the row which has been clicked.

Code (Form1.vb)
Option Explicit On

Imports System.Data.SqlClient

Public Class Form1
    ' ON FORM LOAD, POPULATE DATA IN TO THE PARENT DATAGRIDVIEW.
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If setConn() Then
            PopulateData()
        End If
    End Sub

    ' POPULATE AND SHOW EMPLOYEE DETAILS.
    Private Sub PopulateData()
        Dim sdrEmp As SqlDataReader = GetDataReader("SELECT EmpID, EmpName Employee, " & _
            "PresentAddress Address, Email FROM dbo.EmployeeDetails")
        If sdrEmp.HasRows Then
            Dim dt As New DataTable
            dt.Load(sdrEmp)

            With DGVParent
                DGVParent.DataSource = dt                   ' BIND DATABASE WITH THE GRIDVIEW.
                .Columns.Item("EmpID").Visible = False      ' HIDE EMPLOYEE ID COLUMN.

                ' PUT SOME COLOR.
                .GridColor = Color.FromArgb(211, 222, 229) : .BackgroundColor = Color.Wheat

                .RowsDefaultCellStyle.BackColor = Color.AliceBlue
                .RowsDefaultCellStyle.SelectionBackColor = Color.CornflowerBlue
                .RowsDefaultCellStyle.SelectionForeColor = Color.White
            End With
        End If
    End Sub

    ' COPY DATA FROM PARENT GRID TO CHILD GRID.

    Private Sub DGVParent_CellDoubleClick(ByVal sender As Object, _
            ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
            Handles DGVParent.CellDoubleClick
            
        Cursor.Current = Cursors.WaitCursor
        With DGVParent
            If Not IsDBNull(.Item(1, .CurrentRow.Index).Value()) Then

                Dim sEmpDetailsToUpdate As String = .Item(1, .CurrentRow.Index).Value()
                lblModify.Text = "Showing details of " & sEmpDetailsToUpdate & " in another DataGridView"

                If .Item(1, .CurrentRow.Index).Value() <> "" Then

                    Dim x, y, w, h As Integer
                    Dim rect As Rectangle
                    
                    ' SET COORDINATES.
                    With DGVParent
                        rect = .GetCellDisplayRectangle(.CurrentCell.ColumnIndex, .CurrentRow.Index, False)
                        x = 20 : y = rect.Y + .Top + rect.Height

                        w = .Width - 37
                    End With

                    With Panel2
                        h = .Height
                        .SetBounds(x, y, w, h)
                        .Visible = True         ' SHOW THE PANEL WITH THE CHILD GRID JUST BELOW THE SELECTED ROW.
                    End With

                    Application.DoEvents()
                    With DGVParent
                        DGVChild.Rows.Clear() : DGVChild.Columns.Clear()   ' CLEAR DETAILS OF CHILD GRID.

                        ' GET COLUMNS FROM THE PARENT GRID AND ADD IT TO THE CHILD GRID.
                        For Each DGV_Parents_Column As DataGridViewColumn In .Columns
                            DGVChild.Columns.Add(DirectCast(DGV_Parents_Column.Clone(), DataGridViewColumn))
                        Next

                        Dim row As New DataGridViewRow()
                        For iCnt As Integer = 0 To .Rows.Count - 1
                            If Trim(.Rows(iCnt).Cells(1).Value) = Trim(sEmpDetailsToUpdate) Then
                                row = DirectCast(.Rows(iCnt).Clone(), DataGridViewRow)

                                Dim iColIndex As Integer = 0
                                For Each cell As DataGridViewCell In .Rows(iCnt).Cells
                                    row.Cells(iColIndex).Value = cell.Value
                                    iColIndex += 1
                                Next
                                DGVChild.Rows.Add(row)

                                ' NAME MATCHES, SO BAIL OUT.
                                Exit For
                            End If
                        Next

                        DGVChild.Focus()            ' SET FOCUS ON THE CHILD.
                    End With
                End If
            End If
        End With
        Cursor.Current = Cursors.Default
    End Sub

    Private Shared myConn As SqlConnection
    Private Shared sqComm As SqlCommand                 ' FOR SQL EXECUTION.

    Private Function setConn() As Boolean
        ' SET DATABASE CONNECTION.
        Try
            myConn = New SqlConnection("Data Source=DNA;Persist Security Info=False;" & _
                    "Integrated Security=SSPI;Initial Catalog=DNA_CLASSIFIED;" & _
                    "User Id=sa;Password=;Connect Timeout=30;")
            myConn.Open()

            sqComm = New SqlCommand
            sqComm.Connection = myConn
        Catch ex As Exception
            Return False
        End Try
        Return True
    End Function

    ' SQLDATAREADER, TO FETCH DATA FROM THE DATABASE.
    Private Function GetDataReader(Optional ByVal sQuery As String = "") As SqlDataReader
        sqComm.CommandText = sQuery
        sqComm.ExecuteNonQuery()
        GetDataReader = sqComm.ExecuteReader
        sqComm.Dispose()
    End Function

    Protected Overrides Function ProcessCmdKey(ByRef msg As System.Windows.Forms.Message, _
            ByVal keyData As System.Windows.Forms.Keys) As Boolean
        If keyData = Keys.Escape Then
            ' HIDE THE SECOND PANEL. (THE PANEL WITH THE CHILD GRID)
            If Panel2.Visible = True Then Panel2.Visible = False : Return True
        End If
    End Function
End Class
comments powered by Disqus

Join our Google Plus Community and be a part of a discussion!