Copy Selected rows from Datagrid to another

[jared.geli]

Hi guys. I need help in copying selected row from DataGrid to another. What I want is if I click a row it will be copied to another DataGrid. I set my DataGrid Selection Mode to FullRowSelect. Is it possible for me to update my database using the copied rows.

My code so far:

DtSet is the Dataset I used to get the values from my Database in DataGridView1

Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "'; Extended Properties=Excel 12.0;")

        MyConnection.Open()



        For Each SelectedRow As DataRow In DtSet.Tables(0).Rows
            Dim r As String = SelectedRow("ITEM CODE")
            Dim bc = SelectedRow("Inventory on Hand")

            'I don't know what comes after this

        Next

        MyConnection.Close()

I don't have any ideas will I do it. Tried searching and I still don't get it.

[oussama_1]

let me get this straight, your are asking for 2 things
1.you want on datagridview1_mouseclick to copy selected raw to datagridview2
2.you want on datagridview2_RowsAdded to update your database with the new added raw
am i right ?

[jared.geli]

yes

[jared.geli]

Update: I can now copy a row with datagridview cellclick event along with this code:

        Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "'; Extended Properties=Excel 12.0;")

        MyConnection.Open()

        Dim Dt As DataTable = DtSet.Tables(0)
        Dim Dt2 As DataTable = Dt.Clone()

        '        Dim dgvrow As DataRow

        For Each Row As DataGridViewRow In DataGridView1.SelectedRows
            Dim dgvrow As DataRow = Dt2.NewRow
            dgvrow("ITEM CODE") = Row.Cells("ITEM CODE").Value
            dgvrow("Item Description") = Row.Cells("Item Description").Value
            dgvrow("Brand") = Row.Cells("Brand").Value
            dgvrow("Category") = Row.Cells("Category").Value
            'dgvrow("Salary") = Row.Cells("Salary").Value
            Dt2.Rows.Add(dgvrow)

        Next
        Dim dv2 As DataView = New DataView(Dt2, Nothing, "ITEM CODE", DataViewRowState.CurrentRows)
        DataGridView2.DataSource = dv2

        MyConnection.Close()

The problem now is if I click another row it replaces the one I copied recently. I want to stock all the the rows I copied from DataGridView1 to DataGridView2 like agrocery list so the user will not have a hard time in reviewing his/her orders

[oussama_1]

here's the copy raw solution
ill post the database update later

    Private Sub DataGridView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
        If DataGridView1.RowCount = 0 Or Nothing Then
        Else
            For Each cell As DataGridViewColumn In DataGridView1.Columns
                DataGridView2.Columns.Add(DirectCast(cell.Clone, DataGridViewColumn))
            Next
            DataGridView2.Rows.Add(DataGridView1.CurrentRow.Cells.Cast(Of DataGridViewCell).Select(Function(c) c.Value).ToArray)
        End If
    End Sub

[oussama_1]

for the update
change these :
tablename
columnname1 etc..

    Private Sub DataGridView1_RowsAdded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsAddedEventArgs) Handles DataGridView1.RowsAdded
        Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "'; Extended Properties=Excel 12.0;")

        Dim insertCommand As New OleDb.OleDbCommand("INSERT INTO tablename (columnname1, columnname2, columnname3) VALUES ('" & DataGridView2.Rows(DataGridView2.Rows.Count - 2).Cells(0).Value.ToString & "','" & DataGridView2.Rows(DataGridView2.Rows.Count - 2).Cells(1).Value.ToString & "','" & DataGridView2.Rows(DataGridView2.Rows.Count - 2).Cells(2).Value.ToString & "')", MyConnection)
        Try
            MyConnection.Open()
            insertCommand.ExecuteNonQuery()
        Catch ex As Exception
        Finally
            MyConnection.Close()
        End Try
    End Sub
End Class

[jared.geli]

Tnx the copy works like a charm. I'll try to search on how to update using your code. Tnx agin for the copy code

[jared.geli]

Can I replace Insert Into with Update? And how can I show the values from DataGridView2 to Report Viewer? I can show the values of DataGridView1 to Report Viewer. Can I still do that? Sorry for late reply

[oussama_1]

no you can not.
"UPDATE will only update existing records in the database, it will not add new records. To add new records you need to use an INSERT command."
for report viewer Click Here

[jared.geli]

That's what I want to update my database. Also I want to show the rows in the DataGridView2 to Report Viewer for printing. I can do that if there's a DataSet or a DataTable but I can't because I don't see any in the DataGridView2. I did try the update command but it didn't update.

Dim UpdateCommand As New OleDbCommand("UPDATE [Sheet1$] Set [Inventory on Hand] Where [ITEM CODE] = '" & DataGridView2.Rows(DataGridView2.Rows.Count - 2).Cells(6).Value.ToString & "'", MyConnection)
        Try
            MyConnection.Open()
            UpdateCommand.ExecuteNonQuery()
        Catch ex As Exception
        Finally
            MyConnection.Close()
        End Try

I made an update button

[oussama_1]

try this

Dim UpdateCommand As New OleDbCommand
        UpdateCommand.CommandText = "UPDATE [Sheet1$] Set [Inventory on Hand] Where [ITEM CODE] = '" & DataGridView2.Rows(DataGridView2.Rows.Count - 2).Cells(6).Value.ToString & "'"
        UpdateCommand.Connection = MyConnection
        MyConnection.Open()
        UpdateCommand.ExecuteNonQuery()

convert Datagridview Data to Datatable Click Here

[Deep Modi]

You should watch this Article from MSDN
LINK HERE

some more Similar links: LINK 1
LINK 2
LINK 3

[jared.geli]

@ousaama_1 It says out of range. Still trying other methods like from unbounded datagrid to datatable or dataset

@Deep_Modi Tnx for the links I'm getting (though incomplete) ideas. I'll keep studying them

[jared.geli]

Update: So far here's my code:

Dim myDS As New DataSet
        myDS = DirectCast(DataGridView2.DataSource, DataSet)

        MyConnection.Open()

        For Each dsrow As DataRow In myDS.Tables(0).Rows
            Dim r As String = dsrow("ITEM CODE")
            Dim bc = dsrow("Inventory on Hand")

            sql = "Update [Sheet1$] Set [Inventory on Hand] = '" & bc & "' Where [ITEM CODE] = '" & r & "'"
            Dim updt As OleDbCommand = New OleDbCommand(sql, MyConnection)
            updt.ExecuteNonQuery()
        Next

        MyConnection.Close()

An Exception pops up and it says:"Object reference not set to an instance of an object.". I'm guessing DataGridView2 really has no DataSource according to my observation on the code oussama_1 gave me. I'm still going to pursue the idea of making a dataset based on DataGridView2 untill I find another alternative.

[jared.geli]

Problem solved! I can now put the Rows and Columns of a Unbound Datagrid to a DataTable. I use this code:

Dim dt As New DataTable()
            For Each col As DataGridViewColumn In DataGridView2.Columns
                dt.Columns.Add(col.HeaderText)
            Next

            For Each row As DataGridViewRow In DataGridView2.Rows
                Dim dRow As DataRow = dt.NewRow()
                For Each cell As DataGridViewCell In row.Cells
                    dRow(cell.ColumnIndex) = cell.Value
                Next
                dt.Rows.Add(dRow)
            Next

BTW oussama_1 I just want for you to know on your code you gave on copying rows and columns to another datagrid there's this bug where if you add another row it will copy another set of colums ex. I have 11 columns on my first row I copied then I copy another row and it add another set of columns. I fixed it with adding this:

DataGridView2.ColumnCount = 11

before this:

DataGridView2.Rows.Add(DataGridView1.CurrentRow.Cells.Cast(Of DataGridViewCell).Select(Function(c) c.Value).ToArray)

Thanks oussama_1 and Deep Modi for helping me