Export (0) Print
Expand All

Effortlessly Sort Columns in the DataGrid Control

Visual Studio 6.0
This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

October 2001
by 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 Remington

When 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 recordset's Sort property.

Sorting out the mess

To 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 Sort property accordingly. Before we do so, however, let's take a look at the Sort property in question.

A recordset's Sort property

As 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 Sort property. 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,
MsgBox rst.Sort
would produce the message box shown in Figure A.

Figure A: A recordset's Sort property contains a string that indicates the sort field and type.
[ Figure A ]

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 field

As you may know, the DataGrid provides the names of its headers in each column's Caption property. So, given the DataGrid shown in Figure B, the code
Debug.Print DataGrid1.Columns(0).Caption
would print pub_id in the Immediate window.

Figure B: The DataGrid stores each column's header in the Caption property.
[ Figure B ]

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 Unload() event.

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()
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)
Set rst = Nothing
Set conn = Nothing
End Sub

Adding the sort code

Next, add the sort code shown in Listing B. As you can see, this procedure is quite simple. It uses the event's ColIndex value to retrieve the selected column's caption. It then stores this value in the sortField variable. Next, it tests the recordset's Sort property. 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 Sort property 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"
	sortString = sortField & " Asc"
End If
rst.Sort = sortString
End Sub

Figure C: Our code sorted the data based on the price field.
[ Figure C ]

The sort of technique you can use

Even 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 Sort property, 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.

© 2014 Microsoft