Binding DataGridView ComboBox Column

Level: Beginner Knowledge Required:

• Data Binding
• DataGridView

Description: We use DataGridView control lots of times while developing a Data Driven Application. We usually bind the DataGridView control with some BindingSource. Also we can bind the DataGridView's ComboBox Column to some other BindingSource. That is DataGridView has different DataSource and it's ComboBox column has a different. Consider the following scenario, We have following Typed DataSet,

OrderDataSet

First we will create a simple User Interface, As you can see in the above figure, the DataGridView control is bind with OrderDetail Table. Note that the Product_ID column is displaying ID which is NOT a friendly approach. Instead Product Name should be displayed here. We can use the DataGridView's ComboBox Column here, in which we will populate all the products. The main point to be focused here is that, we will be binding the DataGridView control with the same OrderDetail DataTable, in which there is no Product_Name column. But on front-end the Product_ID column will be set to ComboBox Column in which we will populate the Products' Name. Since we need to populate the ComboBox with Products' Name. Therefore we will add another Typed DataSet and BindingSource to our Form, We have bind our ProductBindingSource with ProductDataSet which contains Product DataTable. We will fill this table in Form_Load event Handler.

IMPORTANT: This table needs to be filled before we fill the OrderDataSet OR at-least before the OrderDetail DataGridView is displayed

Now we need to setup the Product_ID column. First set the ColumnType of Product_ID column to DataGridViewComboBoxColumn and then set the properties as,

• DataPropertyName = Product_ID
• DataSource = ProductBindingSource
• DisplayMember = Product_Name
• ValueMember = Product_ID

DataPropertyName: tells that Product_ID column of OrderDetail DataTable should be updated DataSource: is the source from where the list of Products will be taken, in this case the ProductBindingSource which is actually bind with Product DataTable DisplayMember: Column of Product DataTable which should be used to display in DataGridView control ValueMember: Column of Product DataTable which should be used to set the Value in DataGridView which ultemately will send the value in OrderDetail DataTable Download: BindingDataGridViewComboBox.zip

Bound TextBox Control Validation Issue

Level: Beginner

Knowledge Required:
Data Binding

Description:
Sometimes it happens that we bound TextBox with BindingSource for a Numeric Column, if we try to put a non-numeric value or a very large numeric value in that TextBox, the application stuck, i.e. cannot get out from TextBox, cannot close the Form. This is because internal validation is running.

Whenever we try to get out from TextBox control or try to close the Form, validation happens and if value (provided in TextBox) is NOT a legal value for that particular column then validation fails, which causes the focus to remain in the TextBox control.

To solve this issue just set the CausesValidation property of TextBox control to False. This will stop the automatic Validation.

How To Increase Performance of Loading Large Data from Database (Part-2)

Level: Advanced

Knowledge Required:
Part-2:

• Typed DataSet
• TableAdapter
• DataAdapter
• Partial Class
• Data Binding

Description:
In my previous post, I showed you how to Implement Paging in SQL Server 2005 (Database Level). In this post I will discuss How To Implement Paging Using DataAdapter (Application Level). This is a simple technique,

SQLDataAdapter gives us an option to load the Data from Particular Row and to Limit the Loading of Data by providing the Maximum Number of Records to be Loaded. The Fill Method of SQLDataAdapter has 5 Declarations, one of them is: SQLDataAdapter.Fill(StartRecord, MaxNumberOfRecordsToLoad, ParamArray DataTables()) We will use the above Fill Method to start loading Data from Particular Record Number and Limit the SQLDataAdapter to load only 20 or 30 Records, as per our Page Size.

To implement it professionaly, we will create another Fill Method in our TableAdapter Class (using Partial Class) as,

Partial Class LargeDataTableAdapter
    Public Function FillByDataAdapterPaging(ByRef LargeDataDataTable As LargeDataSet.LargeDataDataTable, ByVal PageNumber As Integer, ByVal PageSize As Integer) As Integer
        Dim StartRow As Integer
        StartRow = ((PageNumber - 1) * PageSize)
        Me.Adapter.SelectCommand = Me.CommandCollection(0)
        If (Me.ClearBeforeFill = True) Then
            LargeDataDataTable.Clear()
        End If
        Dim returnValue As Integer = Me.Adapter.Fill(StartRow, PageSize, LargeDataDataTable)
        Return returnValue
    End Function
End Class

Source Code:
LargeDataWithDataAdapterPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

Filtering the Data Using BindingSource

Level: Beginner
Knowledge Required: To understand the following solution you must have knowledge of:

• Data Binding

Description:
We usually use BindingSource to bind the Data with Controls. BindingSource is a powerful tool which not only used for binding but can also be used to quickly Filter and Sort the Data.
We use Filter property of BindingSource to filter the data,

KeywordBindingSource.Filter = "Keyword='Computer'"

In the above statement, word "Keyword" is the Column Name. Therefore the typical syntax for Filtering the Data is

ColumnName (comparison operator) Value

For String and Date Columns we close the Value in Single Quotes and also we can use LIKE operator with String Columns to partially search the value. We can group together multiple Column Filtering by using AND and/or OR operators.

KeywordBindingSource.Filter = "Keyword LIKE 'C%' AND IsSelected=True"

This statement will display those rows in which Keyword starts with "C" and only selected ones.

Other Filtering examples:

MyBindingSource.Filter = "Qty >= 50 AND Order_ID <= 20"
MyBindingSource.Filter = "OrderDate = #1-Jan-2008 3:23:00 PM#"

Please note that in above statement we used "#" sign for Date column therefore Date column can be closed with either Single Quotes or hash (#) sign.

Also consider this:

MyBindingSource.Filter = "CompName='Ahmed''s Bakery'"

In the above example notice the 2 Single Quotes after Ahmed. The actual String Value is Ahmed's Bakery but whenever in string, a Single Quote comes we must replace it with 2 Single Quotes otherwise an exception will be thrown by BindingSource, that's how BindingSource recognizes that String is closed in Single Quotes and any 2 Single Quotes within String is actually part of that String.

Here I have created a simple project which displays a list of Keywords and user has choice to select the Keywords. I have given an option to filter out the Keywords by typing the initial letters and can also filter down the Selected Keywords only by click on the "Display Selected Keywords only" check box. Here is the picture of the Form1




I have created a single method to set filtering:

Private Sub SetFilter()
    Dim sFilter As String = ""
    ' if Display Only Selected Keywords is checked
    If Me.chkIsSelected.Checked Then
        sFilter = "IsSelected=True";
    End If
    ' if some text is given to filter Keywords
    If Me.txtKeywordFilter.Text <> "" Then
        If sFilter <> "" Then sFilter &= " AND "
        sFilter &= "Keyword LIKE '" & Me.txtKeywordFilter.Text.Replace("'", "''") & "%'"
    End If
    ' If NO Filter is found to be created
    If sFilter = "" Then
        ' remove current filter
        Me.bsKeyword.RemoveFilter()
    Else
        ' set that filter
        Me.bsKeyword.Filter = sFilter
    End If
End Sub

This method is called when chkIsSelected CheckBox's Checked State is changed or whenever txtKeywordFilter TextBox's Text property is changed (See Project for detail).

Download the full source code from here

KeywordSelector.rar

How to Display Row Icon in DataGridView

Level: Intermediate
Knowledge Required:

• DataGridView Control
• Data Bindings
• DataGridView Columns

Description:

We usually use DataGridView control to display contents of a DataTable. For a good impression and User Friendly Interface, we display an Icon with each Row in the DataGridView. For Example we can have a DataTable to store Tasks. Each task can have Status = Done / NOT Done. So we want that when we display these tasks in the DataGridView control there should be an Icon representing the Status of Task. As shown in the following figure.

DataGridView Control with Row Status Icon

To get the above result we will add an Unbound Column in the DataGridView control of Type DataGridViewImageColumn and set the VirtualMode property of DataGridView control to True. This will cause the DataGridView to fire the CellValueNeeded event. This Event triggers whenever the Cell is going to be rendered. Note that only the Unbound Cell will cause this event to be triggered. The bound columns will render themselve automatically.

For this purpose we first have created a Typed DataSet i.e. TaskDataSet containing a DataTable Task as,

Task DataTable

In the above table Task_Status is an Int32 field. We have decided that when this field is 0 (zero) then it means task is NOT Done yet and if this field contains 1 then it means task is Done.

Next we will create a Form and will put the following things:

• TaskDataSet
• BindingSource
• DataGridView Control

Then we will bind the DataGridView to the BindingSource.

After this we will setup our DataGridView control by removing the Columns: Task_ID and Task_Status then we will add an Unbound Column TaskStatusIconColumn of type DataGridViewImageColumn. This column should be the first column of DataGridView set its properties as,

Properties of TaskStatusIconColumn:

• DefaultCellStyle:
• BackColor = White
• ForeColor = Black
• SelectionBackColor = White
• SelectedForeColor = Black
• Resizable = False
• Width = 32

Now set the properties of Task_Description column which should have the Name TaskDescriptionDataGridViewTextBoxColumn.

Properties of TaskDescriptionDataGridViewTextBoxColumn:

• AutoSizeMode = Fill

Properties of DataGridView Control:

• RowHeadersVisible = False
• SelectionMode = FullRowSelect
• VirtualMode = True

VirtualMode is an important property here which must be True otherwise the CellValueNeeded event will NOT be triggered. And finally in the CellValueNeeded Event Handler we will use the following code:

Private Sub TaskDataGridView_CellValueNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) Handles TaskDataGridView.CellValueNeeded
    If e.RowIndex >= 0 AndAlso e.ColumnIndex = Me.TaskStatusIconColumn.Index Then
        Dim drvTask As DataRowView
        Dim rowTask As TaskDataSet.TaskRow
        drvTask = Me.TaskDataGridView.Rows(e.RowIndex).DataBoundItem
        rowTask = CType(drvTask.Row, TaskDataSet.TaskRow)
        Select Case rowTask.Task_Status
            Case 0 ' NOT Done
                e.Value = My.Resources.Resources.Blank16
            Case 1 ' Done
                e.Value = My.Resources.Resources.OK
        End Select
    End If
End Sub

In above code I have used a technique to get the DataRow of DataTable from DataGridView's Row. I have discussed this technique in my previous post How to Get the Table Row from DataGridView Row

Note that I have created 2 PNG images (Blank16 and OK) and have added them in my Resources. Blank16 is a Blank PNG if we dont use this, then DataGridView will render its default image i.e. a Red Cross

You can download the source from here:

DataGridViewRowStatusIcon.rar

Using Data Across Multiple Windows Forms with OK and Cancel Feature

Issue: Cannot add OK and Cancel button on other forms if DataSet Contains multiple child tables, other forms change the data directly from main DataSet
Level: Advanced
Knowledge Required:

• Typed DataSet
• Data Bindings

Description:

Before you continue with this article I recommend to see the Beth Massi’s article because it will clear the basic idea. My article covers a little advanced topic.

As we all have used a Typed DataSet (with multiple related tables) on a single form for Data Entry Process. Which is easy to create using Visual Studio 2005.

Sometimes we require to break this data entry process into multiple Forms but the Typed DataSet should remain same. For example consider the following DataSet,

Company DataSet

Now we can create a single form containing controls that can manipulate all three tables. But the form will look like a mess. So we can split the data into multiple forms as I have created 2 forms

Company Form

Address Form

As you can see the Address form will open when User clicks the New Address or Edit Address Buttons. The logic will be very simple for sharing the Data i.e. we will pass the CompanyDataSet in the Public Constructor (New Method) of Address Form and the Address Form will use that DataSet for editing. But if we directly use the Passed DataSet in the Address Form then changes in this form will cause directly changes in the DataSet since its reference is passed. For Example:

In Address Form we replace the binding source’s DataSurce in the New Method as,

Public Sub New(ByVal Address_ID As Integer, ByRef CompDS As CompanyDataSet)
    Me.InitializeComponent()
    Me.AddressBindingSource.DataSource = CompDS
    Me.AddressBindingSource.Filter = "Address_ID=" & Address_ID
End Sub

Now changes in this form will directly change the Main Company DataSet and the buttons we have placed at the bottom of this form i.e. OK, Cancel and Apply will have no use. So what we do here is a simple technique:

We will make a copy of the DataSet and make changes in that copy and on pressing the OK or Apply buttons we will copy the changes on the main DataSet. Technically speaking: 1) Create Private Save Changes method in Parent form which accepts a Parameter CompanyDataSet, this method will discard its own CompanyDataSet and copy all the things from this given DataSet 2) Create a Public Delegate in Child Form for saving the changes in DataSet 3) Pass this save changes method (created on Parent Form) to the Child Form so the Child Form call this save method by passing its own CompanyDataSet

Child Form (Address Form):
We will use the following code form Child Form

' this form will execute this method to save changes
Public Delegate Sub SaveChangesDelegate(ByRef OtherDataSet As CompanyDataSet)
' declaration of Delegate
Private _SubSaveChanges As SaveChangesDelegate

Public Sub New(ByVal Address_ID As Integer, ByRef CompDS As CompanyDataSet, ByRef SubSaveChanges As SaveChangesDelegate)
    Me.InitializeComponent()
    ' coying data from main dataset
    Me.CompanyDataSet.Merge(CompDS)
    Me._SubSaveChanges = SubSaveChanges
End Sub

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
    ' call the save changes method
    Me._SubSaveChanges(Me.CompanyDataSet)
    Me.Close()
End Sub

Private Sub btnApply_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnApply.Click
    ' just save changes do not close form
    Me._SubSaveChanges(Me.CompanyDataSet)
End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    Me.Close()
End Sub

Parent Form:
Following is the code for Parent Form

Private Sub btnEditAddress_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditAddress.Click
    Dim drvCurrentAddress As DataRowView
    ' get the current selected address
    drvCurrentAddress = Me.AddressBindingSource.Current
    ' if NO Address is selected
    If drvCurrentAddress Is Nothing Then
        MsgBox("Please select an Address first", MsgBoxStyle.Exclamation, "Edit Address")
    Else ' else (address is selected)
        ' display that address
        Dim frmNew As frmAddress
        frmNew = New frmAddress(drvCurrentAddress("Address_ID"), Me.CompanyDataSet, AddressOf SaveChanges)
        frmNew.ShowDialog()
    End If
End Sub

Public Sub SaveChanges(ByRef OtherDataSet As CompanyDataSet)
    Me.CompanyDataSet.Clear()
    Me.CompanyDataSet.Merge(OtherDataSet)
End Sub

Updated Log:
24-May-2008:
DataSet.Merge() method only merges the rows i.e. new rows added and old rows updated but this method does NOT remove the Rows. E.g. DS1.Merge(DS2) will NOT Delete the rows from DS1 (rows that are DELETED from DS2). Therefore the above article was NOT working for the Deleted Rows.
26-May-2008:
Changing the reference of DataSet affects the BindingSource. Since BindingSource is bound to the previous DataSet and executing Me.DataSet = OtherDataSet causes Me.DataSet to change its reference to OtherDataSet and our BindingSource still bound with the previous Reference. To overcome this issue I have changed the Logic the code in above SaveChanges() method, i.e. first clear the DataSet and then merge with the OtherDataSet.

How To Get the Table Row from DataGridView Row

Level: Beginner
Knowledge Required: To understand the following solution you must have knowledge of:

• Typed DataSets
• Data Tables
• DataGridView Control
• Data Binding
• Windows Forms

Description:
We use DataGridView Control to display the contents of a DataTable. For this purpose we first bind the DataTable with DataGridView Control using a BindingSource. Sometimes it is required that we need to get the DataTable's Row from DataGridView Row.

For example, we have an event in DataGridView i.e. CellDoubleClick event. This event is triggered whenever the DataGridView Cell is double clicked. In this event we are supplied with an Event Argument of type DataGridViewCellEventArgs which contains 2 Members RowIndex and ColumnIndex.

Suppose we want that whenever the Cell is double clicked in the DataGridView, we extract out that particular row of our DataTable and then perform some action.

To fully understand this consider a Student Table having Student ID, Student Name and lots of other different fields. We have bound this Table with our DataGridView control and in DataGridView control we are only displaying the Student Name. Whenever the Student Name is double clicked we want that another Window should open i.e. Student Detail Window that contains all the Fields of Table. For this purpose we will use the following code:

Private Sub StudentDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles StudentDataGridView.CellDoubleClick

    Dim drvStudent As DataRowView
    Dim rowStudent As StudentDataSet.StudentRow
    Dim frmNew As StudentDetailForm

    drvStudent = StudentDataGridView.Rows(e.RowIndex).DataBoundItem
    rowStudent = drvStudent.Row

    frmNew = New StudentDetailForm(rowStudent.Student_ID)
    frmNew.ShowDialog()

End Sub

As you can see following code gets the DataRowView which is actually bound with DataGridView Row

drvStudent = StudentDataGridView.Rows(e.RowIndex).DataBoundItem

Then we get the actual DataTable Row as,

drvStudent.Row