Solved

DatagridViewComboboxCell - how to populate combo with values

Posted on 2011-09-21
2
918 Views
Last Modified: 2012-06-21
Hi,
Below is VB code I use to represent data in datagridview which contains 2 datagridviewcombobox columns...
Generally, it works very good for me except that I have an issue I don't know how to solve...
Namely, combobox list in "TABLE_DESC" column is filled with all tables from database which is OK...
Also, combobox list in "RULE_DEF" is filled with all column names from database...this is what's NOT OK for me...
I would like somehow to fill each combobox in "RULE_DEF" column depending on defined table/value in "TABLE_DESC" column for that particular row...
Here is example:

Table_desc                                                    Rule_def

Table 1 (this is defined value)                      Combobox is populated with values representing column names from Table 1
Table 2 (this is defined value)                      Combobox is populated with values representing column names from Table 2
Table 3 (this is defined value)                      Combobox is populated with values representing column names from Table 3
etc...

So basically, I think that should be defined in RetrieveAlternativeTitles2() where I should introduce table name from "TABLE_DESC" column in order to pass value in SQL Statement and retrieve column names (if I'm thinking correctlly)....Unfortunatelly, I don't know how...

I would appreciate your help a lot in order to solve the problem....

Thank you very much
Regards
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Collections.Generic
Imports System.Drawing

Public Class Employees
    Inherits System.Windows.Forms.Form
    Private WithEvents _adapter As New SqlDataAdapter()
    Private WithEvents _commandbuilder As New SqlCommandBuilder(_adapter)
    Private WithEvents _table As New DataTable()
    Private WithEvents DataGridView1 As New DataGridView
    Private WithEvents DataGridView2 As New DataGridView

    <STAThreadAttribute()> _
    Public Shared Sub Main()
        Try
            Application.EnableVisualStyles()
            Application.Run(New Employees())
        Catch e As Exception
            MessageBox.Show(e.Message & e.StackTrace)
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load

        Try
            SetUpForm()
            SetUpDataGridView1()
            SetUpDataGridView2()
        Catch ex As SqlException
            MessageBox.Show("The connection string <" _
                & connectionString _
                & "> failed to connect.  Modify it to connect to " _
                & "a Forplan database accessible to your system.", _
                "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Application.Exit()
        End Try
    End Sub

    Private Sub SetUpForm()
        Size = New Size(950, 600)
        Dim flowLayout As New FlowLayoutPanel()
        flowLayout.FlowDirection = FlowDirection.TopDown
        flowLayout.Dock = DockStyle.Fill
        Controls.Add(flowLayout)
        Text = "General rule definition"

        flowLayout.Controls.Add(DataGridView1)
        flowLayout.Controls.Add(DataGridView2)
    End Sub

    Private Sub SetUpDataGridView2()
        DataGridView2.Dock = DockStyle.Bottom
        DataGridView2.TopLeftHeaderCell.Value = "Filter definition"
        DataGridView2.RowHeadersWidthSizeMode = _
        DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
        DataGridView2.BackgroundColor = Color.White
        DataGridView2.AllowUserToAddRows = False

    End Sub

    Private Sub SetUpDataGridView1()
        ' Virtual mode is turned on so that the
        ' unbound DataGridViewCheckBoxColumn will
        ' keep its state when the bound columns are
        ' sorted.
        DataGridView1.VirtualMode = True
        DataGridView1.BackgroundColor = Color.White

        DataGridView1.AutoSize = True
        DataGridView1.DataSource = _
            Populate_primary("SELECT * FROM FE_GENERAL_RULE_DEF WHERE SEQUENCE_EXE = 1 order by id")
        DataGridView1.TopLeftHeaderCell.Value = "Rules"
        DataGridView1.RowHeadersWidthSizeMode = _
            DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
        DataGridView1.ColumnHeadersHeightSizeMode = _
            DataGridViewColumnHeadersHeightSizeMode.AutoSize
        DataGridView1.AutoSizeColumnsMode = _
            DataGridViewAutoSizeColumnsMode.AllCells
        DataGridView1.AllowUserToAddRows = True
        DataGridView1.AllowUserToDeleteRows = True
       

        ' The below autogenerated column is removed so 
        ' a DataGridViewComboboxColumn could be used instead.
        DataGridView1.Columns.Remove( _
            ColumnName.TABLE_DESC.ToString())
        DataGridView1.Columns.Remove( _
            ColumnName.RULE_DEF.ToString())

        AddComboBoxColumns()
        AddButtonColumn()
    End Sub

    Private Sub AddComboBoxColumns()
        Dim comboboxColumn As DataGridViewComboBoxColumn
        Dim comboboxColumn2 As DataGridViewComboBoxColumn
        comboboxColumn = CreateComboBoxColumn()
        SetAlternateChoicesUsingDataSource(comboboxColumn)
        comboboxColumn.HeaderText = _
            "TABLE_DESC "
        DataGridView1.Columns.Insert(6, comboboxColumn)

        comboboxColumn2 = CreateComboBoxColumn2()
        SetAlternateChoicesUsingDataSource2(comboboxColumn2)
        comboboxColumn2.HeaderText = _
            "RULE_DEF"
        DataGridView1.Columns.Insert(7, comboboxColumn2)

    End Sub

    Private Shared Sub SetAlternateChoicesUsingItems( _
        ByVal comboboxColumn As DataGridViewComboBoxColumn)

        comboboxColumn.Items.AddRange("Mr.", "Ms.", "Mrs.", "Dr.")

    End Sub
    Private Function CreateComboBoxColumn() _
        As DataGridViewComboBoxColumn
        Dim column As New DataGridViewComboBoxColumn()


        With column
            .DataPropertyName = ColumnName.TABLE_DESC.ToString()
            .HeaderText = ColumnName.TABLE_DESC.ToString()
            .DropDownWidth = 160
            .Width = 90
            .MaxDropDownItems = 3
        End With
        Return column

    End Function
    Private Function CreateComboBoxColumn2() _
        As DataGridViewComboBoxColumn
        Dim column2 As New DataGridViewComboBoxColumn()


        With column2
            .DataPropertyName = ColumnName.RULE_DEF.ToString()
            .HeaderText = ColumnName.RULE_DEF.ToString()
            .DropDownWidth = 160
            .Width = 90
            .MaxDropDownItems = 3
        End With
        Return column2

    End Function
    Private Sub SetAlternateChoicesUsingDataSource( _
        ByVal comboboxColumn As DataGridViewComboBoxColumn)
        With comboboxColumn
            .DataSource = RetrieveAlternativeTitles()
            .ValueMember = ColumnName.TABLE_DESC.ToString()
            .DisplayMember = .ValueMember
        End With

    End Sub
    Private Sub SetAlternateChoicesUsingDataSource2( _
    ByVal comboboxColumn2 As DataGridViewComboBoxColumn)

        With comboboxColumn2
            .DataSource = RetrieveAlternativeTitles2()
            .ValueMember = ColumnName.RULE_DEF.ToString()
            .DisplayMember = .ValueMember
        End With

    End Sub
    Private Function RetrieveAlternativeTitles() As DataTable

        Return Populate( _
         "SELECT name AS TABLE_DESC FROM sys.tables UNION SELECT '' ") '"SELECT distinct TABLE_DESC FROM FE_GENERAL_RULE_DEF") ' 

    End Function
    Private Function RetrieveAlternativeTitles2() As DataTable
        Return Populate( _
         "select COLUMN_NAME as RULE_DEF from information_schema.columns  UNION SELECT 'CURRENT_PERIOD' UNION SELECT '' UNION SELECT '*' ") '"SELECT distinct TABLE_DESC FROM FE_GENERAL_RULE_DEF") ' 

    End Function
    Private connectionString As String = _
            "Integrated Security=SSPI;Persist Security Info=False;" _
            & "Initial Catalog=Forplan;Data Source=localhost"

    Private Function Populate(ByVal sqlCommand As String) As DataTable
        Dim northwindConnection As New SqlConnection(connectionString)
        northwindConnection.Open()

        Dim command As New SqlCommand(sqlCommand, _
            northwindConnection)
        Dim adapter As New SqlDataAdapter()

        Dim table As New DataTable()
        adapter.SelectCommand = command

        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        adapter.Fill(table)
        Return table
    End Function

    Private Function Populate_primary(ByVal sqlCommand As String) As DataTable
        Dim northwindConnection As New SqlConnection(connectionString)
        northwindConnection.Open()

        Dim command As New SqlCommand(sqlCommand, _
            northwindConnection)
      
        _adapter.SelectCommand = command

        _table.Locale = System.Globalization.CultureInfo.InvariantCulture
        _adapter.Fill(_table)
        _commandbuilder.GetUpdateCommand()
        _commandbuilder.GetInsertCommand()
        _commandbuilder.GetDeleteCommand()
        Return _table
    End Function

    Enum ColumnName

        KEYID
        ID
        SYS_ID_NUM
        SEQUENCE_EXE
        CONSOLIDATION_CD
        TABLE_PRIORITY
        TABLE_DESC
        RULE_DEF
        SchemaTable

    End Enum

    Private Sub AddButtonColumn()
        Dim buttons As New DataGridViewButtonColumn()
        With buttons
            .HeaderText = "Filter setup"
            .Text = "Filter setup"
            .UseColumnTextForButtonValue = True
            .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            .FlatStyle = FlatStyle.Standard
            .CellTemplate.Style.BackColor = Color.Honeydew
            .DisplayIndex = 0
        End With

        DataGridView1.Columns.Add(buttons)

    End Sub


    Private Sub PopulateFilters( _
        ByVal buttonClick As DataGridViewCellEventArgs)

        Dim seq_exe As String = _
            DataGridView1.Rows(buttonClick.RowIndex). _
            Cells(ColumnName.SEQUENCE_EXE.ToString()).Value().ToString()
        DataGridView2.DataSource = Populate( _
            "SELECT * FROM FE_GENERAL_FILTER_DEFINITION WHERE SEQUENCE_EXE = " & seq_exe)
    End Sub

#Region "SQL Error handling"
    Private Sub DataGridView1_DataError(ByVal sender As Object, _
    ByVal e As DataGridViewDataErrorEventArgs) _
    Handles DataGridView1.DataError

        MessageBox.Show("Error happened " _
            & e.Context.ToString())

        If (e.Context = DataGridViewDataErrorContexts.Commit) _
            Then
            MessageBox.Show("Commit error")
        End If
        If (e.Context = DataGridViewDataErrorContexts _
            .CurrentCellChange) Then
            MessageBox.Show("Cell change")
        End If
        If (e.Context = DataGridViewDataErrorContexts.Parsing) _
            Then
            MessageBox.Show("parsing error")
        End If
        If (e.Context = _
            DataGridViewDataErrorContexts.LeaveControl) Then
            MessageBox.Show("leave control error")
        End If

        If (TypeOf (e.Exception) Is ConstraintException) Then
            Dim view As DataGridView = CType(sender, DataGridView)
            view.Rows(e.RowIndex).ErrorText = "an error"
            view.Rows(e.RowIndex).Cells(e.ColumnIndex) _
                .ErrorText = "an error"

            e.ThrowException = False
        End If
    End Sub
#End Region

    Private Sub DataGridView1_CellContentClick(ByVal sender As Object, _
        ByVal e As DataGridViewCellEventArgs) _
        Handles DataGridView1.CellContentClick

        If IsANonHeaderLinkCell(e) Then
            MoveToLinked(e)
        ElseIf IsANonHeaderButtonCell(e) Then
            PopulateFilters(e)
        End If
    End Sub

    Private Sub MoveToLinked(ByVal e As DataGridViewCellEventArgs)
        Dim employeeId As String
        Dim value As Object = DataGridView1.Rows(e.RowIndex). _
            Cells(e.ColumnIndex).Value
        If value.GetType Is GetType(DBNull) Then Return

        employeeId = CType(value, String)
        Dim boss As DataGridViewCell = _
            RetrieveSuperiorsLastNameCell(employeeId)
        If boss IsNot Nothing Then
            DataGridView1.CurrentCell = boss
        End If
    End Sub

    Private Function IsANonHeaderLinkCell(ByVal cellEvent As  _
        DataGridViewCellEventArgs) As Boolean

        If TypeOf DataGridView1.Columns(cellEvent.ColumnIndex) _
            Is DataGridViewLinkColumn _
            AndAlso Not cellEvent.RowIndex = -1 Then _
            Return True Else Return False

    End Function

    Private Function IsANonHeaderButtonCell(ByVal cellEvent As  _
        DataGridViewCellEventArgs) As Boolean

        If TypeOf DataGridView1.Columns(cellEvent.ColumnIndex) _
            Is DataGridViewButtonColumn _
            AndAlso Not cellEvent.RowIndex = -1 Then _
            Return True Else Return (False)

    End Function

    Private Function RetrieveSuperiorsLastNameCell( _
        ByVal employeeId As String) As DataGridViewCell

        For Each row As DataGridViewRow In DataGridView1.Rows
            If row.IsNewRow Then Return Nothing
            If row.Cells(ColumnName.SEQUENCE_EXE.ToString()). _
                Value.ToString().Equals(employeeId) Then
                Return row.Cells(ColumnName.TABLE_DESC.ToString())
            End If
        Next
        Return Nothing
    End Function

#Region "checkbox state"
    Dim inOffice As New Dictionary(Of String, Boolean)
    Private Sub DataGridView1_CellValuePushed(ByVal sender As Object, _
     ByVal e As DataGridViewCellValueEventArgs) _
        Handles DataGridView1.CellValuePushed

        If IsCheckBoxColumn(e.ColumnIndex) Then
            Dim employeeId As String = GetKey(e)
            If Not inOffice.ContainsKey(employeeId) Then
                inOffice.Add(employeeId, CType(e.Value, Boolean))
            Else
                inOffice.Item(employeeId) = CType(e.Value, Boolean)
            End If
        End If
    End Sub

    Private Function GetKey(ByVal cell As DataGridViewCellValueEventArgs) As String
        Return DataGridView1.Rows(cell.RowIndex).Cells( _
            ColumnName.KEYID.ToString()).Value().ToString()
    End Function

    Private Sub DataGridView1_CellValueNeeded(ByVal sender As Object, _
     ByVal e As DataGridViewCellValueEventArgs) _
        Handles DataGridView1.CellValueNeeded

        If IsCheckBoxColumn(e.ColumnIndex) Then
            Dim employeeId As String = GetKey(e)
            If Not inOffice.ContainsKey(employeeId) Then
                Dim defaultValue As Boolean = False
                inOffice.Add(employeeId, defaultValue)
            End If

            e.Value = inOffice.Item(employeeId)
        End If
    End Sub

    Private Function IsCheckBoxColumn(ByVal columnIndex As Integer) As Boolean

        Dim outOfOfficeColumn As DataGridViewColumn = _
            DataGridView1.Columns(ColumnName.TABLE_DESC.ToString())
        Return (DataGridView1.Columns(columnIndex) Is outOfOfficeColumn)

    End Function
#End Region

    
 
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        _adapter.Update(_table)
        MsgBox("Updated")
    End Sub

End Class

Open in new window

0
Comment
Question by:alsam
2 Comments
 
LVL 13

Accepted Solution

by:
Corey2 earned 500 total points
ID: 36584872
You can override the list based on the selection in a different column here is a simple example
Public Class Form1

    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.DataGridView1 = New System.Windows.Forms.DataGridView
        Me.Column1 = New System.Windows.Forms.DataGridViewComboBoxColumn
        Me.Column2 = New System.Windows.Forms.DataGridViewComboBoxColumn
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGridView1
        '
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.Column1, Me.Column2})
        Me.DataGridView1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.DataGridView1.Location = New System.Drawing.Point(0, 0)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.Size = New System.Drawing.Size(284, 262)
        Me.DataGridView1.TabIndex = 0
        '
        'Column1
        '
        Me.Column1.HeaderText = "Column1"
        Me.Column1.Name = "Column1"
        '
        'Column2
        '
        Me.Column2.HeaderText = "Column2"
        Me.Column2.Name = "Column2"
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(284, 262)
        Me.Controls.Add(Me.DataGridView1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub
    Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView
    Friend WithEvents Column1 As System.Windows.Forms.DataGridViewComboBoxColumn
    Friend WithEvents Column2 As System.Windows.Forms.DataGridViewComboBoxColumn


    Dim Tables() As String = New String() {"List1", "List2"}
    Dim l1() As String = New String() {"L1Column1", "L1Column2", "L1Column3"}
    Dim l2() As String = New String() {"L2Column1", "L2Column2", "L2Column3"}


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Column1.DataSource = Tables
        Dim superList As New ArrayList

        For Each i In l1
            superList.Add(i)
        Next
        For Each i In l2
            superList.Add(i)
        Next
        Column2.DataSource = superList

    End Sub

    Private Sub DataGridView1_CellErrorTextChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellErrorTextChanged

    End Sub

    Private Sub DataGridView1_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
        e.Cancel = True
    End Sub



    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        If DataGridView1.SelectedCells(0).ColumnIndex = 1 Then
            Dim rowIndex As Integer = DataGridView1.SelectedCells(0).RowIndex

            Dim TableName As String = DataGridView1.Item(0, rowIndex).Value
            Select Case TableName
                Case "List1"
                    CType(e.Control, Object).DataSource = l1
                Case "List2"
                    CType(e.Control, Object).DataSource = l2
            End Select
        End If


    End Sub
End Class

Open in new window

0
 
LVL 1

Author Closing Comment

by:alsam
ID: 36585840
Thank you very much...with small adjustments it works like charm...thank you once more...
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now