Hello,
I'm using a datagrid to display data from a table (Jobs). One of the datagrid columns is a DataGridComboBoxColumn (the standard implementation widely available on the Internet, George Shepherd), referencing data from a related table (Customers). The datagrid works well, displaying data correctly. In the "Customers" column, it displays the customer name, as you might expect. If the user clicks on a cell in the "Customers" column the combobox comes up, displaying all the customers in a properly sorted list.
The problem I'm having is that the datagrid needs to allow the user to sort by the contents of any column. I enable sorting with datagrid.AllowSorting = True, so that the user can click in any column header and sort by that column. However, if the user clicks on the column containing the DataGridComboBoxColumn, the sort is performed using the *index* into the related table, rather than the display value. So, the rows in the datagrid are listed in order of customer index rather than customer name.
What is the best way to get the datagrid sort operation, in the context of the DataGridComboBoxColumn, to use the display (foreign) value as the key for the sort I realize that DataGridComboBoxColumn is not a framework-provided class, but I'm hoping the answer is generic, not specific to this class.
This would seem to be a very common question, but I've had a lot of trouble finding the answer!
Here's how I initialize the DataGridComboBoxColumn:
DataGridComboBoxColumn Sorting on Foreign Key Index Rather than Foreign Value
Marcus Rodrigues
To answer your question you can not override how the datatable sorts itself. What I do is add a column to the datatable which contains the displayed value. I leave the column which contains the foreign key out of the datagrids tablestyle. In the combobox's selected index changed event I set the foreign key's column value.
Imports System.Data.SqlClient
Public Class Form1
Dim ds As New DataSet
Dim da, daProducts As SqlDataAdapter
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Dim cmd As New SqlCommandBuilder(da)
da.Update(ds, "orders")
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As SqlConnection
Dim strConn As String
strConn = "Server = .\sqlexpress;"
strConn &= "Database = NorthWind; Integrated Security = SSPI;"
conn = New SqlConnection(strConn)
da = New SqlDataAdapter("Select * from [order details]", conn)
daProducts = New SqlDataAdapter("Select * from products", conn)
da.Fill(ds, "orders")
daProducts.Fill(ds, "Products")
ds.Tables("Products").PrimaryKey = New DataColumn() {ds.Tables("Products").Columns("ProductID")}
DataGrid1.DataSource = ds.Tables("orders")
Dim dc As New DataColumn("ProductName")
ds.Tables("orders").Columns.Add(dc)
For Each dr As DataRow In ds.Tables("orders").Rows
Dim drProduct As DataRow = ds.Tables("Products").Rows.Find(dr("ProductID"))
With dr
.BeginEdit()
.Item("ProductName") = drProduct("ProductName")
.EndEdit()
End With
Next
Dim ts As New DataGridTableStyle
ts.MappingName = "orders"
Dim textCol As New DataGridComboBoxColumn
With textCol
.MappingName = "ProductName"
.HeaderText = "Product"
.Width = 150
.ColumnComboBox.DataSource = ds.Tables("Products")
.ColumnComboBox.DisplayMember = "ProductName"
.ColumnComboBox.ValueMember = "ProductID"
AddHandler .ColumnComboBox.SelectedIndexChanged, AddressOf SelectedIndexChanged
End With
Dim priceCol As New DataGridTextBoxColumn
With priceCol
.MappingName = "UnitPrice"
.HeaderText = "Price"
.Width = 60
End With
Dim qtyCol As New DataGridTextBoxColumn
With qtyCol
.MappingName = "Quantity"
.HeaderText = "Quantity"
.Width = 60
End With
ts.GridColumnStyles.Add(textCol)
ts.GridColumnStyles.Add(priceCol)
ts.GridColumnStyles.Add(qtyCol)
DataGrid1.TableStyles.Add(ts)
End Sub
Private Sub SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim cbo As NoKeyUpCombo
Try
cbo = CType(sender, NoKeyUpCombo)
Dim dr As DataRowView = ds.Tables("orders").DefaultView.Item(DataGrid1.CurrentRowIndex)
With dr
.BeginEdit()
.Item("ProductID") = cbo.SelectedValue
.EndEdit()
End With
Catch ex As Exception
End Try
End Sub
End Class
Public Class DataGridComboBoxColumn
Inherits DataGridTextBoxColumn
Public WithEvents ColumnComboBox As NoKeyUpCombo 'special class
Private WithEvents cmSource As CurrencyManager
Private mRowNum As Integer
Private isEditing As Boolean
Shared Sub New()
End Sub
Public Sub New()
MyBase.New()
ColumnComboBox = New NoKeyUpCombo
AddHandler ColumnComboBox.SelectionChangeCommitted, _
New EventHandler(AddressOf ComboStartEditing)
End Sub
Protected Overloads Overrides Sub Edit(ByVal source As CurrencyManager, _
ByVal rowNum As Integer, ByVal bounds As Rectangle, ByVal readOnly1 As Boolean, _
ByVal instantText As String, ByVal cellIsVisible As Boolean)
MyBase.Edit(source, rowNum, bounds, readOnly1, instantText, cellIsVisible)
mRowNum = rowNum
cmSource = source
ColumnComboBox.Parent = Me.TextBox.Parent
ColumnComboBox.Location = Me.TextBox.Location
ColumnComboBox.Size = New Size(Me.TextBox.Size.Width, ColumnComboBox.Size.Height)
ColumnComboBox.Text = Me.TextBox.Text
TextBox.Visible = False
ColumnComboBox.Visible = True
ColumnComboBox.BringToFront()
ColumnComboBox.Focus()
End Sub
Protected Overloads Overrides Function Commit(ByVal dataSource As _
CurrencyManager, ByVal rowNum As Integer) As Boolean
If isEditing Then
isEditing = False
SetColumnValueAtRow(dataSource, rowNum, ColumnComboBox.Text)
End If
Return True
End Function
Private Sub ComboStartEditing(ByVal sender As Object, ByVal e As EventArgs)
isEditing = True
MyBase.ColumnStartedEditing(DirectCast(sender, Control))
End Sub
Private Sub LeaveComboBox(ByVal sender As Object, ByVal e As EventArgs) _
Handles ColumnComboBox.Leave
If isEditing Then
SetColumnValueAtRow(cmSource, mRowNum, ColumnComboBox.Text)
isEditing = False
Invalidate()
End If
ColumnComboBox.Hide()
End Sub
End Class
Public Class NoKeyUpCombo
Inherits ComboBox
Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message)
If m.Msg <> &H101 Then
MyBase.WndProc(m)
End If
End Sub
End Class
kawing0510
Great solution! Thanks very much!
warm regards,
Tom