How-To: Make DataGrid work with Databound Combos

Dec 22, 2011 at 4:16 PM

Hello.

Your grid demo looks really promising and I hope it saves my company $1500 not having to buy DevExpress.

But we have to get databound combo boxes working in the datagrid. It's a deal breaker if we can't do it.

You have a databound example in your demo, but you didn't add combo boxes. I would imagine this is about the first thing a developer tries to do with a data grid in any but the most simplistic application.

Could you please provide a simple example for this very frequently requested feature? I see various requests for help in this area and so far I see everyone is having to discover for themselves from scratch how to do it but in your post to Richard Males you say it is simple. I couldn't get his code to work but I'm not intested in debugging his code, but rather seeing a minimalist working example from you.

You can use an imaginary database if you want; I'll get the concept. I can fix syntax errors if you just want to type it in here and not test it. I know you are busy and also trying to make a living but whatever crumb you can throw will be greatly appreciated. 1 minute of your time saves 1 day of mine and I'm sure hundreds of others undoubtedly looking for this basic function. I will report whatever worked for me.

Thanks!

Jan 3, 2012 at 7:15 PM
Edited Jan 3, 2012 at 7:27 PM

This is a working example. It works for me. It even works on the add new rows,

 

Imports SourceGrid.Cells.Controllers

Public Class DataGrid
  Implements IDisposable

#Region "Constants"
#End Region

#Region "Enumerations"
#End Region

#Region "Declares"
#End Region

#Region "Delegates"
#End Region

#Region "Structures"
#End Region

#Region "Events"
  Public Event DataChanged(Sql As String, DataTable As DataTable)
#End Region

#Region "Constructors"
  Sub New()

    ' This call is required by the designer.
    InitializeComponent()

    ' Add any initialization after the InitializeComponent() call.
    Initialize()

  End Sub
#End Region

#Region "Properties"
  Public Event AlternateRowColorChanged(Value As System.Drawing.Color)
  Private _AlternateRowColor As System.Drawing.Color
  Public Property AlternateRowColor As System.Drawing.Color
    Get
      Return _AlternateRowColor
    End Get
    Set(Value As System.Drawing.Color)
      If _AlternateRowColor = Value Then Exit Property
      _AlternateRowColor = Value
      For Each col As SourceGrid.DataGridColumn In DataGrid1.Columns
        Dim condition As SourceGrid.Conditions.ICondition = SourceGrid.Conditions.ConditionBuilder.AlternateView(col.DataCell.View, AlternateRowColor, SystemColors.ControlText)
        col.Conditions.Add(condition)
      Next
      RaiseEvent AlternateRowColorChanged(Value)
    End Set
  End Property

  Private _ColumnDefault() As Object
  Public Property ColumnDefault(Name As String) As String
    Get
      Return _ColumnDefault(Me.DataTable.Columns.IndexOf(Name))
    End Get
    Set(Value As String)
      _ColumnDefault(Me.DataTable.Columns.IndexOf(Name)) = Value
    End Set
  End Property

  Public Property ColumnVisible(Name As String) As Boolean
    Get
      Return DataGrid1.Columns(Me.DataTable.Columns.IndexOf(Name) + 1).Visible
    End Get
    Set(Value As Boolean)
      DataGrid1.Columns(Me.DataTable.Columns.IndexOf(Name) + 1).Visible = Value
    End Set
  End Property

  Private WithEvents _CustomEvents As CustomEvents
  Public ReadOnly Property CustomEvents As CustomEvents
    Get
      If _CustomEvents Is Nothing Then
        _CustomEvents = New CustomEvents
      End If
      Return _CustomEvents
    End Get
  End Property

  Public Event DataTableChanged(Value As DataTable)
  Private WithEvents _DataTable As DataTable
  Public Property DataTable As DataTable
    Get
      Return _DataTable
    End Get
    Set(Value As DataTable)
      If _DataTable Is Value Then Exit Property
      _DataTable = Value
      DataGrid1.Columns.Clear()
      If DataTable IsNot Nothing Then
        ReDim _ColumnDefault(DataTable.Columns.Count - 1)
        Dim BoundDataView As New DevAge.ComponentModel.BoundDataView(DataTable.DefaultView)
        DataGrid1.DataSource = BoundDataView
        BoundDataView.AllowNew = True
        BoundDataView.AllowEdit = True
        BoundDataView.AllowDelete = True
        DataGrid1.Columns(1).HeaderCell.View.ForeColor = Color.RoyalBlue
        Dim gridColumn As SourceGrid.DataGridColumn = DataGrid1.Columns.Add(Nothing, "", New SourceGrid.Cells.Link)
        DirectCast(gridColumn.DataCell, SourceGrid.Cells.Link).Image = My.Resources.delete
        gridColumn.DataCell.AddController(New LinkClickDelete())
        'gridColumn.DataCell.View = viewLink
        AutoSizeColumns()
        'AlternateRowColor = Color.FromArgb(250, 250, 250)
        StyleHeaders()
      End If
      RaiseEvent DataTableChanged(Value)
    End Set
  End Property

  Public ReadOnly Property FirstVisibleColumn As Integer
    Get
      For Index As Integer = 1 To DataGrid1.Columns.Count - 1
        If DataGrid1.Columns(Index).Visible Then
          Return Index
        End If
      Next
    End Get
  End Property


  Public Event SqlChanged(Value As String)
  Private _Sql As String
  Public Property Sql As String
    Get
      Return _Sql
    End Get
    Set(Value As String)
      If _Sql = Value Then Exit Property
      _Sql = Value
      RaiseEvent SqlChanged(Value)
    End Set
  End Property

#End Region

#Region "Methods"
  ''' <summary>
  ''' 
  ''' </summary>
  ''' <param name="ColumnIndex"></param>
  ''' <param name="DataTable">
  ''' Must contain Value and Text Column
  ''' </param>
  ''' <remarks></remarks>
  Sub AddComboColumn(Name As String, DataTable As DataTable)

    Dim ComboBox As New SourceGrid.Cells.Editors.ComboBox(GetType(Integer))

    Dim ValueCount As Integer = DataTable.Rows.Count

    Dim LookupValues As Nullable(Of Integer)() = New Nullable(Of Integer)(ValueCount - 1) {}
    Dim StringValues As String() = New String(ValueCount - 1) {}

    Dim Index As Integer = 0
    For Each DataRow As DataRow In DataTable.Rows
      LookupValues(Index) = CInt(DataRow(0))
      StringValues(Index) = DataRow(1)
      Index += 1
    Next

    ComboBox.StandardValues = LookupValues
    ComboBox.StandardValuesExclusive = True
    ComboBox.Control.FormattingEnabled = True
    ComboBox.Control.AutoCompleteMode = AutoCompleteMode.SuggestAppend
    ComboBox.Control.AutoCompleteSource = AutoCompleteSource.ListItems

    Dim mapping As New DevAge.ComponentModel.Validator.ValueMapping()
    mapping.ValueList = LookupValues
    mapping.DisplayStringList = StringValues
    mapping.BindValidator(ComboBox)

    ' assign editor to appropriate column
    DataGrid1.Columns(Me.DataTable.Columns.IndexOf(Name) + 1).DataCell.Editor = ComboBox

    AutoSizeColumns()
  End Sub

  Sub AutoSizeColumns()
    For Each Column As SourceGrid.DataGridColumn In DataGrid1.Columns
      Column.AutoSizeMode = SourceGrid.AutoSizeMode.EnableAutoSize
      DataGrid1.Columns.AutoSizeColumn(Column.Index)
    Next
  End Sub

  Sub Clear()
    DataTable = Nothing
    Sql = Nothing
  End Sub

  Sub Delete()
    DataGrid1.DeleteSelectedRows()
  End Sub

  Sub HideColumn(Name As String)
    ColumnVisible(Name) = False
  End Sub

  Private Sub Initialize()

    Clear()

    DataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle

    DataGrid1.SelectionMode = SourceGrid.GridSelectionMode.Cell

    Dim captionModel As New SourceGrid.Cells.Views.Cell()
    captionModel.BackColor = DataGrid1.BackColor

    DataGrid1.FixedColumns = 1
    DataGrid1.FixedRows = 1

    DataGrid1.Font = New Font(DataGrid1.Font.FontFamily, 12)

    'AddHandler CustomEvents.KeyDown, AddressOf DataGrid_KeyDown
    DataGrid1.Controller.AddController(CustomEvents)

    'Dim viewLink As New SourceGrid.Cells.Views.Link()
    'viewLink.BackColor = Color.Purple

    'viewLink.ImageAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
    'viewLink.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter


  End Sub

  Sub StyleHeaders()
    Dim BorderLine As New DevAge.Drawing.BorderLine(SystemColors.ControlDark)
    Dim border As New DevAge.Drawing.RectangleBorder(BorderLine, BorderLine, Nothing, BorderLine)

    Dim ColumnHeader As New DevAge.Drawing.VisualElements.ColumnHeader()
    ColumnHeader.Border = border
    ColumnHeader.BackColor = SystemColors.Control
    ColumnHeader.BackgroundColorStyle = DevAge.Drawing.BackgroundColorStyle.Linear
    Dim ColumnHeaderView As New SourceGrid.Cells.Views.ColumnHeader()
    'headerView.Font = New Font(DataGrid2.Font, FontStyle.Bold)
    ColumnHeaderView.Background = ColumnHeader
    ColumnHeaderView.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
    ColumnHeaderView.ForeColor = SystemColors.ControlText

    'datagrid2[0, 0] = new SourceGrid.Cells.ColumnHeader("String");
    'grid1[0, 0].View = headerView;
    Dim CellContext As SourceGrid.CellContext
    For Index As Integer = 0 To DataGrid1.Columns.Count - 1
      CellContext = New SourceGrid.CellContext(DataGrid1, New SourceGrid.Position(0, Index))
      'CellContext.Value = "bob"
      CellContext.Cell.View = ColumnHeaderView
    Next

    'Dim RowHeader As New DevAge.Drawing.VisualElements.RowHeader
    'RowHeader.Border = border
    'RowHeader.BackColor = SystemColors.Control
    'RowHeader.BackgroundColorStyle = DevAge.Drawing.BackgroundColorStyle.Linear

    'Dim RowHeaderView As New SourceGrid.Cells.Views.RowHeader()
    ''headerView.Font = New Font(DataGrid2.Font, FontStyle.Bold)
    'RowHeaderView.Background = RowHeader
    'RowHeaderView.BackColor = Color.Magenta
    'RowHeaderView.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
    'RowHeaderView.ForeColor = SystemColors.ControlText

    'For Index As Integer = 0 To DataGrid2.DataGrid1.Rows.Count - 1
    '  CellContext = New SourceGrid.CellContext(Me.DataGrid2.DataGrid1, New SourceGrid.Position(Index, 0))
    '  'CellContext.Value = "bob"
    '  CellContext.Cell.View = RowHeaderView
    'Next

  End Sub

#End Region

#Region "Event Handlers"
  Private Sub DataGrid_KeyDown(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles _CustomEvents.KeyDown
    Dim ActivePosition As SourceGrid.Position = DataGrid1.Selection.ActivePosition
    Dim CellContext As New SourceGrid.CellContext(DataGrid1, New SourceGrid.Position(ActivePosition.Row, ActivePosition.Column))
    Select Case e.KeyCode
      Case Keys.Enter, Keys.Return
        DataGrid1.Selection.Focus(New SourceGrid.Position(ActivePosition.Row + 1, ActivePosition.Column), True)
        e.Handled = True
      Case Keys.Home
        DataGrid1.Selection.Focus(New SourceGrid.Position(IIf(e.Control, 1, ActivePosition.Row), FirstVisibleColumn), True)
        e.Handled = True
      Case Keys.End
        DataGrid1.Selection.Focus(New SourceGrid.Position(IIf(e.Control, DataGrid1.Rows.Count - 2, ActivePosition.Row), DataGrid1.Columns.Count - 2), True)
        e.Handled = True
      Case Keys.Down
        If e.Control Then
          DataGrid1.Selection.Focus(New SourceGrid.Position(DataGrid1.Rows.Count - 2, ActivePosition.Column), True)
          e.Handled = True
        End If
      Case Keys.Up
        If e.Control Then
          DataGrid1.Selection.Focus(New SourceGrid.Position(1, ActivePosition.Column), True)
          e.Handled = True
        End If
      Case Keys.Right
        If e.Control Then
          DataGrid1.Selection.Focus(New SourceGrid.Position(ActivePosition.Row, DataGrid1.Columns.Count - 2), True)
          e.Handled = True
        End If
      Case Keys.Left
        If e.Control Then
          DataGrid1.Selection.Focus(New SourceGrid.Position(ActivePosition.Row, FirstVisibleColumn), True)
          e.Handled = True
        End If

    End Select
  End Sub

  'Private Sub DataGrid_Disposed(sender As Object, e As System.EventArgs) Handles Me.Disposed
  '  RemoveHandler CustomEvents.KeyDown, AddressOf DataGrid_KeyDown
  'End Sub

  'Sub DataGrid_KeyDown(eventSender As Object, e As KeyEventArgs)
  'End Sub

  Private Sub DataTable_RowChanged(sender As Object, e As System.Data.DataRowChangeEventArgs) Handles _DataTable.RowChanged
    RaiseEvent DataChanged(Sql, DataTable)
  End Sub

  Private Sub DataTable_RowDeleted(sender As Object, e As System.Data.DataRowChangeEventArgs) Handles _DataTable.RowDeleted
    RaiseEvent DataChanged(Sql, DataTable)
  End Sub

  Private Sub DataTable_TableNewRow(sender As Object, e As System.Data.DataTableNewRowEventArgs) Handles _DataTable.TableNewRow
    'provide default values
    For Each Value As String In _ColumnDefault
      If Value IsNot Nothing Then
        e.Row(Array.IndexOf(_ColumnDefault, Value)) = Value
      End If
    Next
  End Sub

#End Region

#Region "Shared Properties"
#End Region

#Region "Shared Methods"
#End Region

#Region "Operators"
#End Region

End Class


Public Class LinkClickDelete
  Inherits SourceGrid.Cells.Controllers.ControllerBase
  Public Overrides Sub OnClick(sender As SourceGrid.CellContext, e As EventArgs)
    MyBase.OnClick(sender, e)

    Dim grid As SourceGrid.DataGrid = DirectCast(sender.Grid, SourceGrid.DataGrid)
    grid.DeleteSelectedRows()
  End Sub
End Class

but, to make this work, I had to fix a "bug"(?) in the source code to handle nulls, as it was throwing an error on a new row when using combo boxes, in the ValueMapping.cs class:

		private void p_Validator_ConvertingValueToDisplayString(object sender, ConvertingObjectEventArgs e)
		{
			if (m_DisplayStringList != null)
			{
				if (m_ValueList == null)
					throw new ApplicationException("ValueList cannot be null");

                if (e.Value == null)
                {
                    e.Value = String.Empty;
                    e.ConvertingStatus = ConvertingStatus.Completed;
                }
                else
                {
                    int l_Index = m_ValueList.IndexOf(e.Value);
                    if (l_Index >= 0)
                    {
                        e.Value = m_DisplayStringList[l_Index];
                        e.ConvertingStatus = ConvertingStatus.Completed;
                    }
                    else
                    {
                        if (m_bThrowErrorIfNotFound)
                            e.ConvertingStatus = ConvertingStatus.Error;
                    }
                }
			}
		}