Adding and saving rows to a datagrid at runtime 1

[steve1rm]

Hello,

VS 2005

I have 2 datagrids.

The 1st datagrid will contain parts that the user will select and then click a button that saids add part. This part will be added to the 2nd datagrid. Which is the best method to add these rows to the datagrid. The fields will remain the same in both datagrids. So no need to change them.

Once the required parts have been entered into the 2nd datagrid they will be saved to the database. What is the best method so save the data in the 2nd datagrid, as the user can save periodically.


Many thanks in advance,

Steve

 

[vonehle]

If you're using sql as the database, this worked great:

http://www.informit.com/articles/article.asp?p=26956&seqNum=3&rl=1

 

[tchnologist]

If you used Visual Studio to create strongly-typed DataSets, the actual implementation of what you need isn't too hard.

When you add your first DataGridView control to the grid to the control, Visual Studio (at least VS 2005) prompts you to select a data source for the grid. At that point, you have the option to create a new data source if you don't already have any added to your project. Assuming you have a table that's acting as the source for your first DataGrid and you have a second table that should receive the data from the second DataGrid, you'll want to select both those tables to be included in the DataSet. Select your source table as the DataSource for the first DataGridView.

Visual Studio will automatically create and add a strongly-typed DataSet, BindingSource, and source table TableAdapter to the workspace beneath your form. In order to update the destination table, you'll also need to add a TableAdapter object for your destination table. If you open your toolbox, you'll see a new category: <project> Components, where <project> is the name of your project. If you selected both your source and destination tables when defining the tables to include in your DataSet, you should see TableAdapter controls for both tables (in my case, the tables were called "SourceTable" and "DestTable" so my controls are called "SourceTableTableAdapter" and "DestTableTableAdapter"). Drag your destination table TableAdapter control to the workspace.

When you add the second DataGridView that the user will copy to, don't select a data binding from the builder! Since you don't want the second grid (I'm assuming) to be automatically filled with contents from the destination table in your database, you're going to bind directly to the unfilled DataSet. You can do this in code, as we're about to see.

Switch to code view for your form. If you're starting with a blank project, the Form_Load event should look something like this:

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.SourceTableTableAdapter.Fill(Me.Db1DataSet.SourceTable)
    End Sub

This fills the source table from the database. Since the first DataGridView is bound to this DataTable, the results are automatically displayed in the grid. Now, we want to link the second DataGridView to our unfilled destination table. To do this, modify the Form_Load event to look like this (your object and variable names may differ, of course):

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.SourceTableTableAdapter.Fill(Me.Db1DataSet.SourceTable)
[highlight]        dgvDest.DataSource = Me.Db1DataSet.DestTable[/highlight]
    End Sub

To implement the copy function, all we need to do is:
[ol]
[li]find the record in the source table corresponding to the currently selected row in the source data grid, and[/li]
[li]use the properties and functions generated for you by the Data Source wizard to add a new row to the destination table and retrieve the field values from the source table.[/li]
[/ol]

This actually is fairly simple to do. Here's the code for the Copy button's Button_Click event:

    ' copies the currently selected item from the source datagrid to the destination datagrid
    Private Sub btnCopy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopy.Click

        Dim partNum As String = CType(dgvSource.CurrentRow.Cells.Item(0).Value, String)
        Dim rowSrc As db1DataSet.SourceTableRow = Db1DataSet.SourceTable.FindByPartNum(partNum)
        Dim tblDest As db1DataSet.DestTableDataTable = Db1DataSet.DestTable
        tblDest.AddDestTableRow(rowSrc.PartNum, rowSrc.Name, rowSrc.Vendor, rowSrc.Qty)

    End Sub

The tables I used only had four fields: PartNum, Name, Vendor, and Qty. The Data Source wizard automatically creates a property for each column in your table. Your column names will obviously be different based on the structure of your table.

The first thing you need to do is get the value of your primary key from the selected row in your source grid. In my case, this is PartNum. Next, get the corresponding row in your source table by using the "FindBy" function created for you by the Data Source wizard.

Finally, get a reference to the destination table and add a row to it, passing in values for all the fields from your destination row.

Saving the additions only takes a single line of code since ADO.NET does most of the work for you. The Button_Click event for the Save button is simply:

    ' save all new additions to the database
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim rowsAffected As Integer = Me.DestTableTableAdapter1.Update(Me.Db1DataSet.DestTable)
    End Sub

The Data Source wizard created the SQL commands necessary to update the destination table from the DataSet. ADO.NET keeps track of what records have been added since the last update and when you call the [tt]Update()[/tt] method, the new records are inserted in your destination table.

And I think that's it! Hope this helps.