Effortlessly Sort Columns in the DataGrid Control
October 2001by Mike D. Jones
Application: Microsoft Visual Basic 6.0
I've just created a VB project that uses the DataGrid to show some data from a database (Access 2000). The users have asked me to include a sort feature that would allow them to click on a specific header and have the column sort by value. Unfortunately, the DataGrid doesn't provide a method to do this, and I can't figure it out. Can you help?
Jack RemingtonWhen displaying data in a grid control like the DataGrid, you'll no doubt want to provide a sort feature like the one Mr. Remington describes. If you don't, you can be certain users will ask for it. While it's true the DataGrid doesn't provide any built-in members that automatically provide this functionality, you can easily provide it on your own with some quick coding of ADO and a
Sorting out the messTo illustrate our technique, we'll build a simple project that consists of a form and a DataGrid, which will in turn pull some records from a database. We'll then attach the code that evaluates the user's header selection, accesses the DataGrid's underlying recordset, and then modifies the
Sortproperty accordingly. Before we do so, however, let's take a look at the
Sortproperty in question.
A recordset's Sort propertyAs we mentioned, to provide the sort feature, you'll need to access the DataGrid's underlying DataSource. Assuming that it's an ADO or DAO recordset, this object will expose a
Sortproperty. As you can guess, this property contains a string that describes the current sort. Typically, it contains the name of the field upon which ADO has sorted the records—or it may be empty when there's no sort specified at all. With this in mind, suppose our DataGrid contains two fields: title and price. In the SQL statement to create the recordset, you initiated an ascending sort on the price field. At this point,
would produce the message box shown in Figure A.
A recordset's Sort property contains a string that indicates the sort field and type.
When you alter the
Sort property with a new string, ADO automatically updates the recordset to reflect the modification. In turn, because of the recordset's relation to the DataGrid control, Visual Basic will also update the grid. At this point, all we need to do is come up with a convenient way to update the
Sort property based on the user's selection in the DataGrid.
Assigning a sort fieldAs you may know, the DataGrid provides the names of its headers in each column's
Captionproperty. So, given the DataGrid shown in Figure B, the code
would print pub_id in the Immediate window.
Figure B: The DataGrid stores each column's header in the Caption property.
What's more, whenever you select a header, the DataGrid control executes the
HeadClick() event. This event passes in the index number of the column you clicked. Assuming that the DataGrid's headers match the recordset's field names, you can use this index number to pass the caption into the Sort property, as in:
rst.Sort = DataGrid1.Columns(ColIndex) _ .Caption & " Asc"
Speaking of a convenient way to sort DataGrid records …
At this point, we're ready to build the sample application. To do so, launch a Visual Basic project, and then select Project | Components from the menu bar. In the Components dialog box, select Microsoft DataGrid Control 6.0 (SP3) (OLEDB). Click OK to add the control to the project. When you do, Visual Basic inserts the control icon onto the Toolbar. Click the DataGrid button and draw the control on the default form.
Next, right-click on the form and select View Code from the shortcut menu. When Visual Basic displays the Code window, enter the initial code from Listing A. This code establishes a connection to a database (SQL Server in our case), retrieves the required records and sets the DataGrid's DataSource to the subsequent recordset. Don't forget to modify the connection string
(CONN_STRING) and SQL constants to match your own database circumstances. Finally, the code performs some cleanup in the
Listing A: Establishing a database connection
Dim conn As New ADODB.Connection Dim rst As New ADODB.Recordset Const CONN_STRING = "Provider=SQLOLEDB;" _ & "Data Source=foobar; initial catalog=pubs;" _ & "user id=sa" Const SQL = "SELECT pub_id, title, price FROM titles" Private Sub Form_Load() conn.Open CONN_STRING With rst .CursorLocation = adUseClient .Open SQL, conn, adOpenStatic, adLockBatchOptimistic Set DataGrid1.DataSource = rst End With With DataGrid1 .Columns(0).Width = 0 .Columns(1).Width = 3000 .Columns(2).Width = 1030 End With End Sub Private Sub Form_Unload(Cancel As Integer) rst.Close conn.Close Set rst = Nothing Set conn = Nothing End Sub
Adding the sort codeNext, add the sort code shown in Listing B. As you can see, this procedure is quite simple. It uses the event's
ColIndexvalue to retrieve the selected column's caption. It then stores this value in the
sortFieldvariable. Next, it tests the recordset's
Sortproperty. If ADO has already executed an ascending sort, then the code combines the sort field with the SQL keyword Desc. Otherwise, it concatenates the sort field with the Asc keyword. Finally, the procedure sets the recordset's
Sortproperty to the modified value. The DataGrid updates itself accordingly. Figure C shows what happened to the data in Figure B when we clicked the price header.
Listing B: Sorting the data
Private Sub DataGrid1_HeadClick(ByVal ColIndex _ As Integer) Dim sortField As String Dim sortString As String sortField = DataGrid1.Columns(ColIndex).Caption If InStr(rst.Sort, "Asc") Then sortString = sortField & " Desc" Else sortString = sortField & " Asc" End If rst.Sort = sortString End Sub
Our code sorted the data based on the price field.
The sort of technique you can useEven Visual Basic's built-in controls don't always offer the existing functionality you might expect. The ability to sort the data in the DataGrid is one such feature. Fortunately, by taking advantage of the underlying DataSource's
Sortproperty, you can provide this oft-requested feature, making both you and your users happy.
Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.