Question Move Data from one datagridview to another datagridview

[tashiduks]

Hi Everyone,

Currently i am working with Datagrid. I want to move the data from one datagrid to another. I can populate the data in datagrid using combo box. Now i am stuck with how to move the data between datagrid and save the data using save button.
I following code till now which is working correctly:

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Public Class gridview_search
    Dim sqlFunc As New SQLConnectionNET.SQLConnection
    Dim sINIFile As String = Application.StartupPath + "\DBConnection.INI"
    Dim SQLCOnn As OleDbConnection = New OleDbConnection
    Dim ds As DataSet = New DataSet
    Private Sub gridview_search_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try
            SQLCOnn.Close()
            SQLCOnn.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
            SQLCOnn.Open()
            ds.Clear()
            SQLCOnn.Close()
            'Retrieve value of Combo box from DB
            LoadCountry()
            LoadCountry2()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                                        & "Error : " & ex.Message & vbCrLf _
                                        & "Source : " & Err.Source & vbCrLf _
                                        , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Private Sub LoadCountry()

        SQLCOnn.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
        SQLCOnn.Open()

        Try
            Dim query As String = "SELECT * FROM dbo.Cbr_CountryList WHERE CnlIsDefault = 1"
            Dim myCommand As New OleDbCommand(query, SQLCOnn)
            myCommand.CommandType = CommandType.Text
            Dim dataAdapter As New OleDbDataAdapter(myCommand)
            Dim dataTable As New DataTable()
            dataAdapter.Fill(dataTable)
            ComboBox2.DataSource = dataTable
            ComboBox2.DisplayMember = "cnlCountryName"
            ComboBox2.ValueMember = "cnlCountryID"
            SQLCOnn.Close()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                                        & "Error : " & ex.Message & vbCrLf _
                                        & "Source : " & Err.Source & vbCrLf _
                                        , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            SQLCOnn.Close()
        End Try
    End Sub
    Private Sub LoadCountry2()

        SQLCOnn.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
        SQLCOnn.Open()

        Try
            Dim query As String = "SELECT * FROM dbo.Cbr_CountryList WHERE CnlIsDefault = 1"
            Dim myCommand As New OleDbCommand(query, SQLCOnn)
            myCommand.CommandType = CommandType.Text
            Dim dataAdapter As New OleDbDataAdapter(myCommand)
            Dim dataTable As New DataTable()
            dataAdapter.Fill(dataTable)
            ComboBox3.DataSource = dataTable
            ComboBox3.DisplayMember = "cnlCountryName"
            ComboBox3.ValueMember = "cnlCountryID"
            SQLCOnn.Close()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                                        & "Error : " & ex.Message & vbCrLf _
                                        & "Source : " & Err.Source & vbCrLf _
                                        , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            SQLCOnn.Close()
        End Try
    End Sub
    Private Sub LoadDistrict(selectedValue As String)
        If SQLCOnn.State = ConnectionState.Closed Then
            SQLCOnn.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
            SQLCOnn.Open()
        End If

        Try

            Dim query As String = "select * from dbo.Cbr_DistrictList where CdlCountryId = '" & selectedValue & "'"
            Dim myCommand As New OleDbCommand(query, SQLCOnn)
            myCommand.CommandType = CommandType.Text
            Dim dataAdapter As New OleDbDataAdapter(myCommand)
            Dim dataTable As New DataTable()
            dataAdapter.Fill(dataTable)
            ComboBox1.DataSource = dataTable
            ComboBox1.DisplayMember = "CdlDistrictName"
            ComboBox1.ValueMember = "CdlDistrictID"
            SQLCOnn.Close()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                                        & "Error : " & ex.Message & vbCrLf _
                                        & "Source : " & Err.Source & vbCrLf _
                                        , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            SQLCOnn.Close()
        End Try
    End Sub
    Private Sub LoadDistrict2(selectedValue As String)
        If SQLCOnn.State = ConnectionState.Closed Then
            SQLCOnn.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
            SQLCOnn.Open()
        End If

        Try

            Dim query As String = "select * from dbo.Cbr_DistrictList where CdlCountryId = '" & selectedValue & "'"
            Dim myCommand As New OleDbCommand(query, SQLCOnn)
            myCommand.CommandType = CommandType.Text
            Dim dataAdapter As New OleDbDataAdapter(myCommand)
            Dim dataTable As New DataTable()
            dataAdapter.Fill(dataTable)
            ComboBox4.DataSource = dataTable
            ComboBox4.DisplayMember = "CdlDistrictName"
            ComboBox4.ValueMember = "CdlDistrictID"
            SQLCOnn.Close()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                                        & "Error : " & ex.Message & vbCrLf _
                                        & "Source : " & Err.Source & vbCrLf _
                                        , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            SQLCOnn.Close()
        End Try
    End Sub
    Private Sub LoadSubDistrict(selectedValue As String)
        If SQLCOnn.State = ConnectionState.Closed Then
            SQLCOnn.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
            SQLCOnn.Open()
        End If
        Try
            Dim adp As OleDbDataAdapter = New OleDbDataAdapter _
        ("select * from dbo.Cbr_SubDistrictList where SdlDistrictID = '" & selectedValue & "'", SQLCOnn)
            Dim ds As DataSet = New DataSet()
            adp.Fill(ds)
            gvList.DataSource = ds.Tables(0)
            SQLCOnn.Close()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                                         & "Error : " & ex.Message & vbCrLf _
                                         & "Source : " & Err.Source & vbCrLf _
                                         , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            SQLCOnn.Close()
        End Try
    End Sub
    Private Sub LoadSubDistrict2(selectedValue As String)
        If SQLCOnn.State = ConnectionState.Closed Then
            SQLCOnn.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
            SQLCOnn.Open()
        End If
        Try
            Dim adp As OleDbDataAdapter = New OleDbDataAdapter _
        ("select * from dbo.Cbr_SubDistrictList where SdlDistrictID = '" & selectedValue & "'", SQLCOnn)
            Dim ds As DataSet = New DataSet()
            adp.Fill(ds)
            gvList2.DataSource = ds.Tables(0)
            SQLCOnn.Close()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                                         & "Error : " & ex.Message & vbCrLf _
                                         & "Source : " & Err.Source & vbCrLf _
                                         , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            SQLCOnn.Close()
        End Try
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim selectedValue As String
        selectedValue = ComboBox1.SelectedValue.ToString()
        LoadSubDistrict(selectedValue)
    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        Dim selectedValue As String
        selectedValue = ComboBox2.SelectedValue.ToString()
        LoadDistrict(selectedValue)
    End Sub

    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ComboBox3.SelectedIndexChanged
        Dim selectedValue As String
        selectedValue = ComboBox3.SelectedValue.ToString()
        LoadDistrict2(selectedValue)
    End Sub

    Private Sub ComboBox4_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ComboBox4.SelectedIndexChanged
        Dim selectedValue As String
        selectedValue = ComboBox4.SelectedValue.ToString()
        LoadSubDistrict2(selectedValue)
    End Sub
End Class

Till now its fine. Now what i want in my project is, "i want to move the data from one datagrid to another datagrid after loading the data to datagrid with single button click". Can anyone tell me how to achieve this? I have attached the screenshot of my windows form.

I am using VB.NET 2010 and SQL SERVER 2008.

Thanks

 

[jmcilhinney]

The grid's are bound to DataTables so the grid's themselves are pretty much irrelevant where the moving of the data is concerned. You would get the underlying DataRow from the first DataTable and then pass it to a call to ImportRow on the second DataTable. That will copy it and you can then Remove it from the first DataTable if you want to move rather than copy.

To get the DataRow in the first place, you can use the DataBoundItem property of a grid row to get a DataRowView and then the Row property of that to get the DataRow.

 

[tashiduks]

Thanks for the response. I would appreciate if you can show me the code who to do it would be really nice. Sorry for the trouble.

 

[jmcilhinney]

Thanks for the response. I would appreciate if you can show me the code who to do it would be really nice. Sorry for the trouble.

I'm sure it would be, because that would mean that there was no effort required on your part. Try following the instructions provided. If you can do it then it's done and you didn't need to ask for any more help to begin with. If you can't then show us what you did and we can help you fix it. The best way to learn is to do... so do.

 

[tashiduks]

Hi,

I have managed to get the project work without database using below code:

Public Class movegridview
   
    Private ds As New DataSet

    Private Sub movegridview_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        'make some data
        Dim dt As New DataTable("flintstones")
        ds.Tables.Add(dt) 'you indicated that you are using a dataset, so i will also use one
        Dim r As DataRow
        With dt
            'add a column for some data: you don't need to do this as you already have a table of data
            .Columns.Add("data", GetType(String))
            r = .NewRow : r(0) = "fred" : .Rows.Add(r)
            r = .NewRow : r(0) = "wilma" : .Rows.Add(r)
            r = .NewRow : r(0) = "dino" : .Rows.Add(r)
            r = .NewRow : r(0) = "barney" : .Rows.Add(r)
            r = .NewRow : r(0) = "betty" : .Rows.Add(r)
            r = .NewRow : r(0) = "hoppy" : .Rows.Add(r)
        End With

        ' you can add a column to an existing table
        ds.Tables(0).Columns.Add("Moved", GetType(Boolean))
        ' Initially, the added column cells will have a Null value.
        'We will treat this as false

        ' Create a DataView to act as the datasource for each datgridview

        Dim fromview As New DataView(ds.Tables(0))
        ' Select only False "Moved" rows
        fromview.RowFilter = "[Moved]=False or [Moved] is Null"
        fromview.Sort = "[Data] Asc"
        DataGridView1.DataSource = fromview
        DataGridView1.Columns("Moved").Visible = False
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGridView1.ReadOnly = True

        Dim ToView As New DataView(ds.Tables(0))
        ' Select only True "Moved" rows
        ToView.RowFilter = "[Moved]=True"
        ToView.Sort = "[Data] Asc"
        DataGridView2.DataSource = ToView
        DataGridView2.Columns("Moved").Visible = False
        DataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGridView2.ReadOnly = True
    End Sub

    Private Sub btnleft_Click(sender As Object, e As System.EventArgs) Handles btnleft.Click
        For Each r As DataGridViewRow In DataGridView2.SelectedRows
            'Modify the underlying datarow
            CType(r.DataBoundItem, DataRowView).Row.Item("Moved") = False
        Next
    End Sub

    Private Sub btnright_Click(sender As Object, e As System.EventArgs) Handles btnright.Click
        For Each r As DataGridViewRow In DataGridView1.SelectedRows
            'Modify the underlying datarow
            CType(r.DataBoundItem, DataRowView).Row.Item("Moved") = True
        Next
    End Sub
End Class

NOW CAN ANYONE TELL ME HOW TO ACHIEVE THE SAME THING WITH SQL DATABASE. I REALLY NEED THAT ABOVE CODE TO WORK WITH SQL DATABASE.

Thanks

 

[jmcilhinney]

Firstly, please don't shout. I said I would help if you tried and showed what you'd done and so I shall. You're not making it more likely by shouting but you may make it less.

You don't actually need to make it work with a database because the database has nothing to do with the grid. You already have the grid's working with DataTables and none of that will change. A DataTable is a DataTable no matter how you populate it so how you use it doesn't change. All you have to do is use a data adapter to Fill the DataTable to start with and Update the database when you're done. If you don't know how to do that, check this out:

Retrieving and Saving Data in Databases

 

[tashiduks]

Hi,

I have working code below which moves the row from one gridview to another gridview. I dont know whether the code which i have used below is proper way of doing it.

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Public Class movegridviewSQL
    Dim sqlFunc As New SQLConnectionNET.SQLConnection
    Dim sINIFile As String = Application.StartupPath + "\BDFCLHR.INI"
    Dim SQLCOnn As OleDbConnection = New OleDbConnection
    Private ds As New DataSet

    Private Sub movegridviewSQL_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        SQLCOnn.Close()
        With SQLCOnn
            .ConnectionString = sqlFunc.GetConnectionString(sINIFile)
            .Open()
        End With
        Dim da As New OleDbDataAdapter("select * from dbo.Cbr_SubDistrictList", SQLCOnn)
        ' Dim bldOperations As New OleDbCommandBuilder(da)

        Dim dt As New DataTable("dbo.Cbr_SubDistrictList")
        Dim ds As New DataSet("ds")
        Dim bs As New BindingSource

        'Setting up a datagrid with the binding source in 'MainForm_Load event
        da.Fill(dt)
        bs.DataSource = dt
        DataGridView1.DataSource = bs
        ds.Tables.Add(dt)
        'Button code for adding a record 'Gives error that I am not adding the same type as the'underlying list
        Dim newRec As DataRow = dt.NewRow
        With dt
            .Rows.Add(newRec)
        End With
        ' you can add a column to an existing table
        ds.Tables(0).Columns.Add("Moved", GetType(Boolean))
        ' Initially, the added column cells will have a Null value.
        'We will treat this as false

        ' Create a DataView to act as the datasource for each datgridview

        Dim fromview As New DataView(ds.Tables(0))
        ' Select only False "Moved" rows
        fromview.RowFilter = "[Moved]=False or [Moved] is Null"
        'fromview.Sort = "[Data] Asc"
        DataGridView1.DataSource = fromview
        DataGridView1.Columns("Moved").Visible = False
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGridView1.ReadOnly = True

        Dim ToView As New DataView(ds.Tables(0))
        ' Select only True "Moved" rows
        ToView.RowFilter = "[Moved]=True"
        'ToView.Sort = "[Data] Asc"
        DataGridView2.DataSource = ToView
        DataGridView2.Columns("Moved").Visible = False
        DataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGridView2.ReadOnly = True
    End Sub

    Private Sub btnleft_Click(sender As Object, e As System.EventArgs) Handles btnleft.Click
        Try
            For Each r As DataGridViewRow In DataGridView2.SelectedRows
                'Modify the underlying datarow
                CType(r.DataBoundItem, DataRowView).Row.Item("Moved") = False
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub btnright_Click(sender As Object, e As System.EventArgs) Handles btnright.Click
        Try
            For Each r As DataGridViewRow In DataGridView1.SelectedRows
                'Modify the underlying datarow
                CType(r.DataBoundItem, DataRowView).Row.Item("Moved") = True
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

I would appreciate if some one can check and see whether the code which i have used above is in proper or not. I would like seek a help to put it the above code in proper manner.

THanks

 

[tashiduks]

The code which i have provided to move records from one datagrid to another is working fine. Thanks for the helpers who have helped me.

Now i am continuing with further coding "To Save the records of destination datagrid to SQL database". I have used the following code but it seems to be throwing an exception error message. The message says:

Oledbexception was handled. Must declare the scalar variable "@SdlCountryID"

The code which i have for BUTTON Click events are as follows:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

Try
Dim command As New OleDbCommand

SQLCOnn.Close()
With SQLCOnn
.ConnectionString = sqlFunc.GetConnectionString(sINIFile)
.Open()
End With
command.Connection = SQLCOnn
For i As Integer = 0 To DataGridView2.Rows.Count - 1
command.CommandText = "INSERT INTO dbo.Cbr_SubDistrictList1 ([SdlCountryId],[SdlDistrictID],[SdlSubDistrictID],[SdlSubDistrictName]" & _
",[SetDate],[UserName],[DataStatusFlag]) VALUES (@SdlCountryId, @SdlDistrictID, @SdlSubDistrictID, @SdlSubDistrictName,@SetDate,@UserName,@DataStatusFlag)"
command.Parameters.AddWithValue("@SdlCountryId", DataGridView2.Rows(i).Cells(0).Value)
command.Parameters.AddWithValue("@SdlDistrictID", DataGridView2.Rows(i).Cells(1).Value)
command.Parameters.AddWithValue("@SdlSubDistrictID", DataGridView2.Rows(i).Cells(2).Value)
command.Parameters.AddWithValue("@SdlSubDistrictName", DataGridView2.Rows(i).Cells(3).Value)
command.Parameters.AddWithValue("@SetDate", DataGridView2.Rows(i).Cells(4).Value)
command.Parameters.AddWithValue("@UserName", DataGridView2.Rows(i).Cells(5).Value)
command.Parameters.AddWithValue("@DataStatusFlag", DataGridView2.Rows(i).Cells(6).Value)

command.ExecuteNonQuery()
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

Can anyone tell me why its throwing the exception message? Where did i went wrong?

Thanks