Move selected checked row to table2

[saxick]

hello there
am finding it difficult to accomplished this task

i am to use a button click to move a checked row(s) to another table

the checkbox column is dynamic

the tables have the same number of columns
i want it in winform (i have try searching and i cant figure out how to use me.controls.find on winform all am getting is "findcontrol" which seems not to work on winform)
lastly i wish to use entity framework to accomplished this task but any better solution is welcomed

thank you

 

[jmcilhinney]

So, are you saying that you want to query a database table and display the results in a form, then allow the user to select certain records and then insert those records into a different database table? Whether you use a DataTable and a data adapter or the Entity Framework, I would suggest that you first need to be able to query the source table and display the data in a DataGridView. Once you can do that, you would add an unbound column to the grid for the check boxes. Once you've got that, you can start processing the checked rows. Exactly how you process them depends on what data access technology you're using.

In the case of ADO.NET, one option would be to Clone the DataTable, loop through the grid rows and, for each checked row, get the DataBoundItem (which will be a DataRow) and add a new row to the new DataTable with the same data. When that's done, you have a DataTable full of new rows so you can use a data adapter (the same data adapter that retrieved the data even) to save those new rows to the target database table.

If you're using EF then the process would be similar but the DataBoundItem would be an entity and you would need to create and add an entity of the target type to your context.

I suggest that you do as much of that as you can and post back if and when you strike a specific issue. I'm not about to write all the code to do the whole thing for you.

i have try searching and i cant figure out how to use me.controls.find on winform all am getting is "findcontrol" which seems not to work on winform

I can't see how that has any relevance at all. Maybe you were using individual controls and you wanted to access each CheckBox but that would be a mistake. You should absolutely use a DataGridView.

 

[saxick]

So, are you saying that you want to query a database table and display the results in a form, then allow the user to select certain records and then insert those records into a different database table? Whether you use a DataTable and a data adapter or the Entity Framework, I would suggest that you first need to be able to query the source table and display the data in a DataGridView. Once you can do that, you would add an unbound column to the grid for the check boxes. Once you've got that, you can start processing the checked rows. Exactly how you process them depends on what data access technology you're using.

In the case of ADO.NET, one option would be to Clone the DataTable, loop through the grid rows and, for each checked row, get the DataBoundItem (which will be a DataRow) and add a new row to the new DataTable with the same data. When that's done, you have a DataTable full of new rows so you can use a data adapter (the same data adapter that retrieved the data even) to save those new rows to the target database table.

If you're using EF then the process would be similar but the DataBoundItem would be an entity and you would need to create and add an entity of the target type to your context.

I suggest that you do as much of that as you can and post back if and when you strike a specific issue. I'm not about to write all the code to do the whole thing for you.

I can't see how that has any relevance at all. Maybe you were using individual controls and you wanted to access each CheckBox but that would be a mistake. You should absolutely use a DataGridView.

here is what i did and it was not working perhaps my logic was wrong

Private Sub fmMem_Load(sender As Object, e As EventArgs) Handles MyBase.Load         me.BindGrid()
		Dim checkBoxColumn As New DataGridViewCheckBoxColumn()
        checkBoxColumn.HeaderText = ""
        checkBoxColumn.Width = 30
        checkBoxColumn.Name = "check"
        gvMembsers.Columns.Insert(0, checkBoxColumn)
        gvMembsers.AllowUserToAddRows = False
End Sub
  
Private Sub BindGrid()
    Dim test As TestEntities = New TestEntities()
    gvMembsers.DataSource = From m In test.members Select m
    
End Sub
  
Private Sub btnmove_Click(sender As Object, e As EventArgs)Handles btnmove.Click
    Dim test As TestEntities = New TestEntities()
     
   Dim isSelected As Boolean = Convert.ToBoolean(row.Cells("check").Value)
    
    Dim memOldRecord = (From o In test.members Where isSelected = True Select o).ToList()
        If isSelected Then
     
            For j As Integer = 0 To memOldRecord.Count() - 1
                Using test1 As TestEntities = New TestEntities()
                    Dim ol As Oldmember = New Oldmember With {
                        .name = memOldRecord(j).name,
                        .age = memOldRecord(j).age,
                        .dob = memOldRecord(j).dob,
                        .location = memOldRecord(j).location
                        }
                    test1.AddToOldmembers(ol)
                test1.SaveChanges()
                End Using
            Next
        Me.BindGrid()
        End if
  
    Using test2 As TestEntities = New TestEntities()
        Dim mem = (From c In test2.members WhereisSelected = True Select c).ToList()
        For i As Integer = 0 To mem.Count() - 1
            Dim member As member = mem(i)
            test2.DeleteObject(member)
            test2.SaveChanges()
        Next
        gvOldmembers.DataSource = mem
        gvOldmembers.DataBind()
        Me.BindGrid()
    End Using
End Sub

 

[jmcilhinney]

Here's an untested example that should work using ADO.NET:

Private sourceTable As DataTable
Private adapter As SqlDataAdapter

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    AddCheckBoxColumnToGrid()
    ConfigureDataAdapter()
    RetrieveAndBindData()
End Sub

Private Sub AddCheckBoxColumnToGrid()
    DataGridView1.Columns.Add(New DataGridViewCheckBoxColumn With {.HeaderText = "Select"})
End Sub

Private Sub ConfigureDataAdapter()
    Dim connection As New SqlConnection("connection string here")

    adapter = New SqlDataAdapter("SELECT ID, Name FROM SourceTable", connection)

    Dim insertCommand As New SqlCommand("INSERT INTO DestinationTable (Name) VALUES (@Name)", connection)

    insertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50)
    adapter.InsertCommand = insertCommand
End Sub

Private Sub RetrieveAndBindData()
    sourceTable = New DataTable
    adapter.Fill(sourceTable)
    DataGridView1.DataSource = sourceTable
End Sub

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

Private Sub SaveSelectedRows()
    Dim destinationTable = sourceTable.Clone()

    For Each gridRow As DataGridViewRow In DataGridView1.Rows
        If CBool(gridRow.Cells(0).Value) = True Then
            Dim sourceRow = DirectCast(gridRow.DataBoundItem, DataRowView).Row

            destinationTable.Rows.Add(sourceRow.ItemArray)
        End If
    Next

    adapter.Update(destinationTable)
End Sub

The principle is basically the same using EF:

1. Get the source entity list.
2. Bind the list to a DataGridView containing an unbound check box column.
3. Loop through the grid rows and find those that are checked in that unbound column.
4. Get the source entity bound to a checked row, copy its data into an entity of the destination type and add that to the context.
5. Save the changes.