DataGridView with Detail Edit Form - VS 2005

• Download source files - 425.3 KB

Introduction

This article is a tutorial on how to create a WinForms selectable datagridview with a separate Form for editing detail row data using a strongly typed DataSet data layer. We will use Visual Studio 2005 and .NET 2.0.

Background

Creating an editable datagrid is a very straightforward way to edit a datagrid's values, but sometimes a separate editable Form/UserControl is desired (increased flexibility, more detail, etc.). This example will load the editable fields and lookups with the grid on Form load. This will keep us from having to synchronize and manage separate instances of similar data and will reduce round trips to the database. When dealing with lots of records, you would want to implement a where filter in your query. We will include a Save button on the Edit form. The Save button could just as easily be implemented on the main form. The edit form could also be changed to a UserControl to display on the main form instead of a popup.

Walkthrough

1. Add a DataSource (Strongly typed dataset)

• Create a new Windows Forms Project and create two forms, Orders and Order.
• Open Orders in Design View.
• Click on Add New Datasource in the Data Sources Window.

  

• Choose Database as the Datasource Type.
• Create a New Connection.
• Select Microsoft SQL Server (SqlClient) as the Data Source.
• Enter the location and login credentials for the Northwind Database.
• Click Next till you get to Choose Your Database Objects.
• Select OrderID, CustomerID, and EmployeeID from the Orders table, CustomerID, CompanyName, and ContactName from the Customers table, and EmployeeID, LastName,FirstName and Title from the Employees table as below:

  

  

• Click Finish.

  Note

  This automatically creates the NorthwindDataSet strongly typed dataset with related tables/tableAdapters.

  

• Change the CustomerID and EmployeeID columns to ComboBoxes in the Data Sources Window.

  

2. Create the DataGridView

• Drag the Orders table from the Data Sources Window onto the Orders form.

  

• This will create a NorthWindDataSet instance, a BindingSource, and a BindingNavigator.
• Delete the BindingNavigator.

  

• Click the DataGridView's "Smart Tag" to open the DataGridView Tasks Window.

  

• Disable Editing, Deleting, and Adding. Then dock in parent container.
• Click Edit Columns...Change the CustomerID and EmployeeID columns to DataGridViewComboBoxColumn ColumnTypes.

  

• Bind the Datasource, DisplayMember, and ValueMember properties for the Customer and Employee columns.

  

  

  

  

  Note

  Selecting the DataSources from the Project Data Sources node automatically creates the BindingSources and TableAdapters for the Form.

• Change the DisplayStyle to Nothing for both columns since these will be read-only.

3. Create the Edit Form

• Open the Order form in design view.
• Change the NorthwindDataSet.Orders table in the DataSources Window from DataGridView to Details.

  

• Drag the detail table from the DataSources Window onto the Order form. This will create the 3 controls on the form as well as the DataSet, BindingSource, TableAdapter, and BindingNavigator.
• Delete the BindingNavigator.
• Drop a Button control on the form and rename it ButtonSave.
• Click each ComboBoxes' "Smart Tag" to edit the DataSource, DisplayMember, SelectedValue, and ValueMember properties as we did earlier with the GridViewComboBoxColumns.
  You must check the Use data bound items option to edit these values.

  

  

• This will create BindingSource and TableAdapter components on the form for the Employees and Customers tables.
• Delete the corresponding TableAdapters (Not the OrdersTableAdapter) since we will be filling these tables from the Orders form.
• Set the DataBindings.Text property to None since we're now binding to the SelectedValue property.

  

4. Instantiate the Edit Form

• Create a Sub New() in the Edit Form.

  

• Double-click the Save button in design mode to create a click event handler.
• Use the following code (fix any naming differences):

  Private Sub Orders_FormClosing(ByVal sender As Object, _
          ByVal e As System.Windows.Forms.FormClosingEventArgs) _
          Handles Me.FormClosing
      'Reject any changes that were not saved.
      Me.NorthwindDataSet.Orders(Me.OrdersBindingSource.Position)._
                              RejectChanges()
  End Sub
  
  Private Sub ButtonSave_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles ButtonSave.Click
      'Update the database and close.
      Me.Validate()
      Me.OrdersBindingSource.EndEdit()
      If Not Me.OrdersTableAdapter.Update(Me.NorthwindDataset.Orders) > 0 _
      Then
          MsgBox("No records were updated.")
      End If
      Me.Close()
  End Sub
  
  Public Sub New(ByVal nwDataSet As NorthwindDataSet, _
                      ByVal position As Integer)
      ' This call is required by the Windows Form Designer.
      InitializeComponent()
      ' Add any initialization after the InitializeComponent() call.
      ' We will specify the DataSet from the DataGrid form here
      Me.NorthwindDataset = nwDataSet
      Me.OrdersBindingSource.DataSource = Me.NorthwindDataSet
      Me.CustomersBindingSource.DataSource = Me.NorthwindDataSet
      Me.EmployeesBindingSource.DataSource = Me.NorthwindDataSet
      Me.OrdersBindingSource.Position = position
  End Sub
  
  Private Sub Order_Load(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles MyBase.Load
  
      'Do not refill. You could optionally refresh this record or
      'access additional database columns by creating another query
      'with a primary key parameter in the table adapter.
      'Me.OrdersTableAdapter.Fill(Me.nwDataSet.Orders)
  End Sub

  Note

  In Sub New, we will pass in the dataset created and filled with the grid. We then point the local NorthwindDataSet variable to the existing dataset. This does not change the properties already set in InitializeComponent for the DataSet, so we must reconfigure the other elements on the form to point to the existing DataSet. Fortunately, the BindingSources provide abstraction. We need only reset the 3 BindingSources' DataSource properties. We then set the BindingSource position so the form knows which record to edit.

• On Save click, we will save the changes and exit.
• Finally on form close, we undo any changes that were not saved, because the dataset is shared with the grid.

5. Code the DataGridView Double-click event

• Open the Orders form in code view.
• Create a RowHeaderMouseDoubleClick event handler.

  

• Create a FormClosing event handler.
• Use the following code (fix any naming differences):

  Private Sub Orders_FormClosing(ByVal sender As Object, _
      ByVal e As System.Windows.Forms.FormClosingEventArgs) _
      Handles Me.FormClosing
      Me.OrdersDataGridView.Dispose()
  End Sub
  
  Private Sub Orders_Load(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles MyBase.Load
      'TODO: This line of code loads data into the
      ''NorthwindDataSet.Employees' table.
      'You can move, or remove it, as needed.
      Me.EmployeesTableAdapter.Fill(Me.NorthwindDataSet.Employees)
      'TODO: This line of code loads data into the
      ''NorthwindDataSet.Customers' table.
      'You can move, or remove it, as needed.
      Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)
      'TODO: This line of code loads data into the
      ''NorthwindDataSet.Orders' table. 
      'You can move, or remove it, as needed.
      Me.OrdersTableAdapter.Fill(Me.NorthwindDataSet.Orders)
  End Sub
  
  Private Sub OrdersDataGridView_RowHeaderMouseDoubleClick_
      (ByVal sender As Object, ByVal e _
      As System.Windows.Forms.DataGridViewCellMouseEventArgs) _
      Handles OrdersDataGridView.RowHeaderMouseDoubleClick
      If Me.OrdersDataGridView.SelectedRows.Count > 0 Then
          Dim editForm As New Order(Me.NorthwindDataSet, _
          Me.NorthwindDataSet.Orders.Rows.IndexOf_
          (CType(CType(Me.OrdersDataGridView.SelectedRows(0)._
          DataBoundItem, DataRowView).Row, NorthwindDataSet.OrdersRow)))
          editForm.Show()
      End If
  End Sub

Note

The DataSet is filled on form load. The tableadapter fill statements were created by configuring the BindingSource. We have to dispose of the grid before the form closes to avoid possible DataErrors from the GridViewComboBoxColumns.

The Double-click event instantiates the detail edit form, passing in the DataSet and the position. The position is found by tracking the selected row back to its row instance in the order table, then finding the index of that row in the dataset.