Solved

inserting a combobox in a datagrid

Posted on 2004-09-15
3
293 Views
Last Modified: 2010-04-23
Does anyone know how to populate a  combo box  from a database in a datagrid using windows forms and vb code
0
Comment
Question by:genesisproject
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
planocz earned 500 total points
Comment Utility
Here is a Mirco$oft sample...


'FORM 1

Option Strict Off
Option Explicit On

Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb

Namespace DataGridTextBoxCombo
 
    '/ <summary>
    '/ Summary description for Form1.
    '/ </summary>
    Public Class Form1
        Inherits System.Windows.Forms.Form
        Public dataGrid1 As System.Windows.Forms.DataGrid
        Private myDataSet As DataSet
        Private dataAdapter As OleDbDataAdapter
        Private dataGrid2 As System.Windows.Forms.DataGrid
        Private dataGrid3 As System.Windows.Forms.DataGrid
        '/ <summary>
        '/ Required designer variable.
        '/ </summary>
        Private components As System.ComponentModel.Container = Nothing
        Public Sub New()
            '
            ' Required for Windows Form Designer support
            '
            InitializeComponent()

            MakeDataSetAndBindGrid()
        End Sub 'New
        Private Sub MakeDataSetAndBindGrid()
            ' Set the connection and sql strings
            ' assumes your mdb file is in your root
            Dim connString As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
            Dim sqlString As String = "SELECT * FROM orders"

            dataAdapter = Nothing
            myDataSet = Nothing

            Try
                ' Connection object
                Dim connection As New OleDbConnection(connString)

                ' Create data adapter object
                dataAdapter = New OleDbDataAdapter(sqlString, connection)

                ' Create a dataset object and fill with data using data adapter's Fill method
                myDataSet = New DataSet
                dataAdapter.Fill(myDataSet, "orders")


                'now load the datatable for the bound combobox...
                'notice that the combobox & grid are NOT  bound to the same
                'table (if bound to the same table, you must have different binding contexts)
                sqlString = "SELECT customerID, contactName FROM customers"
                dataAdapter = New OleDbDataAdapter(sqlString, connection)
                dataAdapter.Fill(myDataSet, "customerList")

                connection.Close()
            Catch ex As Exception
                MessageBox.Show(("Problem with DB access-   connection: " + connString + "            query: " + sqlString + ex.ToString()))
                Me.Close()
                Return
            End Try

            ' Create a table style that will hold the new column style
            ' that we set and also tie it to our customer's table from our DB
            Dim tableStyle As New DataGridTableStyle
            tableStyle.MappingName = "orders"

            Dim dt As DataTable = myDataSet.Tables("orders")

            ' make the dataGrid use our new tablestyle and bind it to our table
            Dim i As Integer

            While i < dt.Columns.Count
                If i <> 1 Then
                    Dim TextCol As New DataGridTextBoxColumn
                    TextCol.MappingName = dt.Columns(i).ColumnName
                    TextCol.HeaderText = dt.Columns(i).ColumnName
                    tableStyle.GridColumnStyles.Add(TextCol)
                    'use this for width control
                    'tableStyle.GridColumnStyles(0).Width = 200
                Else
                    Dim ComboTextCol As New DataGridComboBoxColumn
                    ComboTextCol.MappingName = "customerID" 'must be from the grid table...
                    ComboTextCol.HeaderText = "ContactCombo"
                    ComboTextCol.Width = 120
                    'DataView dv = new DataView(myDataSet.Tables["customerList"], "", "customerID", DataViewRowState.CurrentRows);
                    ComboTextCol.ColumnComboBox.DataSource = myDataSet.Tables("customerList").DefaultView 'dv;
                    ComboTextCol.ColumnComboBox.DisplayMember = "contactName"
                    ComboTextCol.ColumnComboBox.ValueMember = "customerID"


                    tableStyle.PreferredRowHeight = ComboTextCol.ColumnComboBox.Height + 2

                    tableStyle.GridColumnStyles.Add(ComboTextCol)
                End If
                i += 1
            End While

            dataGrid1.TableStyles.Clear()
            dataGrid1.TableStyles.Add(tableStyle)
            dataGrid1.DataSource = dt

            'in datagrid2, you can see the raw table without the combobox.
            ' As you change the combo displaymember in grid1,
            ' the valuemember in grid2 should change as you move off the row...
            dataGrid2.DataSource = dt

            'let datagrid3 display the combobox table
            dataGrid3.PreferredColumnWidth = 97
            dataGrid3.DataSource = myDataSet.Tables("customerList").DefaultView
            dataGrid3.Enabled = False ' for display only - no clicking...

            'make it no append
            myDataSet.Tables("customerList").DefaultView.AllowDelete = False
            myDataSet.Tables("customerList").DefaultView.AllowNew = False
            myDataSet.Tables("customerList").DefaultView.AllowEdit = False
        End Sub 'MakeDataSetAndBindGrid
        '/ <summary>
        '/ Clean up any resources being used.
        '/ </summary>
        Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub 'Dispose
        Private Sub InitializeComponent()
            Me.dataGrid1 = New System.Windows.Forms.DataGrid
            Me.dataGrid2 = New System.Windows.Forms.DataGrid
            Me.dataGrid3 = New System.Windows.Forms.DataGrid
            CType(Me.dataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
            CType(Me.dataGrid2, System.ComponentModel.ISupportInitialize).BeginInit()
            CType(Me.dataGrid3, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            ' dataGrid1
            '
            Me.dataGrid1.Anchor = System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom Or System.Windows.Forms.AnchorStyles.Left Or System.Windows.Forms.AnchorStyles.Right
            Me.dataGrid1.CaptionText = "DataGrid1 with ComboBox in column 1 showing DisplayMember"
            Me.dataGrid1.DataMember = ""
            Me.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
            Me.dataGrid1.Location = New System.Drawing.Point(16, 24)
            Me.dataGrid1.Name = "dataGrid1"
            Me.dataGrid1.Size = New System.Drawing.Size(624, 200)
            Me.dataGrid1.TabIndex = 0
            '
            ' dataGrid2
            '
            Me.dataGrid2.Anchor = System.Windows.Forms.AnchorStyles.Bottom Or System.Windows.Forms.AnchorStyles.Right
            Me.dataGrid2.CaptionText = "DataGrid 2 shows ValueMember in column 1"
            Me.dataGrid2.DataMember = ""
            Me.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText
            Me.dataGrid2.Location = New System.Drawing.Point(16, 248)
            Me.dataGrid2.Name = "dataGrid2"
            Me.dataGrid2.Size = New System.Drawing.Size(368, 112)
            Me.dataGrid2.TabIndex = 1
            '
            ' dataGrid3
            '
            Me.dataGrid3.Anchor = System.Windows.Forms.AnchorStyles.Bottom Or System.Windows.Forms.AnchorStyles.Right
            Me.dataGrid3.CaptionText = "DataGrid3 combobox values"
            Me.dataGrid3.DataMember = ""
            Me.dataGrid3.HeaderForeColor = System.Drawing.SystemColors.ControlText
            Me.dataGrid3.Location = New System.Drawing.Point(392, 248)
            Me.dataGrid3.Name = "dataGrid3"
            Me.dataGrid3.Size = New System.Drawing.Size(248, 112)
            Me.dataGrid3.TabIndex = 2
            '
            ' Form1
            '
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(656, 373)
            Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.dataGrid3, Me.dataGrid2, Me.dataGrid1})
            Me.Name = "Form1"
            Me.Text = "Form1"
            CType(Me.dataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
            CType(Me.dataGrid2, System.ComponentModel.ISupportInitialize).EndInit()
            CType(Me.dataGrid3, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)
        End Sub 'InitializeComponent
        <STAThread()> _
          Public Shared Sub Main()
            Application.Run(New Form1)
        End Sub
    End Class
End Namespace

'CLASS

Option Strict Off
Option Explicit On

Imports Microsoft.VisualBasic
Imports System
Imports System.ComponentModel
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Windows.Forms

Namespace DataGridTextBoxCombo
    Public Class NoKeyUpCombo
        Inherits ComboBox
        Private WM_KEYUP As Integer = &H101
        Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message)
            If m.Msg = WM_KEYUP Then
                'ignore keyup to avoid problem with tabbing & dropdownlist;
                Return
            End If
            MyBase.WndProc(m)
        End Sub
    End Class
End Namespace

'CLASS

Option Strict Off
Option Explicit On

Imports Microsoft.VisualBasic
Imports System
Imports System.ComponentModel
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Windows.Forms

Namespace DataGridTextBoxCombo
    ' Step 1. Derive a custom column style from DataGridTextBoxColumn
    '      a) add a ComboBox member
    '  b) track when the combobox has focus in Enter and Leave events
    '  c) override Edit to allow the ComboBox to replace the TextBox
    '  d) override Commit to save the changed data
    Public Class DataGridComboBoxColumn
        Inherits DataGridTextBoxColumn
        Public ColumnComboBox As NoKeyUpCombo
        Private _source As System.Windows.Forms.CurrencyManager
        Private _rowNum As Integer
        Private _isEditing As Boolean
        Public Shared _RowCount As Integer


        Public Sub New()
            _source = Nothing
            _isEditing = False
            _RowCount = -1

            ColumnComboBox = New NoKeyUpCombo()
            ColumnComboBox.DropDownStyle = ComboBoxStyle.DropDownList

            AddHandler ColumnComboBox.Leave, AddressOf LeaveComboBox
            AddHandler ColumnComboBox.SelectionChangeCommitted, AddressOf ComboStartEditing
        End Sub 'New

        Private Sub HandleScroll(ByVal sender As Object, ByVal e As EventArgs)
            If ColumnComboBox.Visible Then
                ColumnComboBox.Hide()
            End If
        End Sub 'HandleScroll

        Private Sub ComboStartEditing(ByVal sender As Object, ByVal e As EventArgs)
            _isEditing = True
            MyBase.ColumnStartedEditing(sender)
        End Sub 'ComboMadeCurrent


        Private Sub LeaveComboBox(ByVal sender As Object, ByVal e As EventArgs)
            If _isEditing Then
                SetColumnValueAtRow(_source, _rowNum, ColumnComboBox.Text)
                _isEditing = False
                Invalidate()

            End If
            ColumnComboBox.Hide()
            AddHandler Me.DataGridTableStyle.DataGrid.Scroll, New EventHandler(AddressOf HandleScroll)
        End Sub 'LeaveComboBox


        Protected Overloads Overrides Sub Edit(ByVal [source] As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer, ByVal bounds As System.Drawing.Rectangle, ByVal [readOnly] As Boolean, ByVal instantText As String, ByVal cellIsVisible As Boolean)
           
            MyBase.Edit([source], rowNum, bounds, [readOnly], instantText, cellIsVisible)

            _rowNum = rowNum
            _source = [source]

            ColumnComboBox.Parent = Me.TextBox.Parent
            ColumnComboBox.Location = Me.TextBox.Location
            ColumnComboBox.Size = New Size(Me.TextBox.Size.Width, ColumnComboBox.Size.Height)
            ColumnComboBox.SelectedIndex = ColumnComboBox.FindStringExact(Me.TextBox.Text)
            ColumnComboBox.Text = Me.TextBox.Text
            Me.TextBox.Visible = False
            ColumnComboBox.Visible = True
            AddHandler Me.DataGridTableStyle.DataGrid.Scroll, AddressOf HandleScroll

            ColumnComboBox.BringToFront()
            ColumnComboBox.Focus()
        End Sub 'Edit


        Protected Overrides Function Commit(ByVal dataSource As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer) As Boolean

            If _isEditing Then
                _isEditing = False
                SetColumnValueAtRow(dataSource, rowNum, ColumnComboBox.Text)
            End If
            Return True
        End Function 'Commit


        Protected Overrides Sub ConcedeFocus()
            Console.WriteLine("ConcedeFocus")
            MyBase.ConcedeFocus()
        End Sub 'ConcedeFocus

        Protected Overrides Function GetColumnValueAtRow(ByVal [source] As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer) As Object

            Dim s As Object = MyBase.GetColumnValueAtRow([source], rowNum)
            Dim dv As DataView = CType(Me.ColumnComboBox.DataSource, DataView)
            Dim rowCount As Integer = dv.Count
            Dim i As Integer = 0
            Dim s1 As Object

            'if things are slow, you could order your dataview
            '& use binary search instead of this linear one
            While i < rowCount
                s1 = dv(i)(Me.ColumnComboBox.ValueMember)
                If (Not s1 Is DBNull.Value) AndAlso _
                    (Not s Is DBNull.Value) AndAlso _
                            s = s1 Then
                    Exit While
                End If
                i = i + 1
            End While

            If i < rowCount Then
                Return dv(i)(Me.ColumnComboBox.DisplayMember)
            End If
            Return DBNull.Value
        End Function 'GetColumnValueAtRow


        Protected Overrides Sub SetColumnValueAtRow(ByVal [source] As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer, ByVal value As Object)
            Dim s As Object = value

            Dim dv As DataView = CType(Me.ColumnComboBox.DataSource, DataView)
            Dim rowCount As Integer = dv.Count
            Dim i As Integer = 0
            Dim s1 As Object

            'if things are slow, you could order your dataview
            '& use binary search instead of this linear one
            While i < rowCount
                s1 = dv(i)(Me.ColumnComboBox.DisplayMember)
                If (Not s1 Is DBNull.Value) AndAlso _
                            s = s1 Then
                    Exit While
                End If
                i = i + 1
            End While
            If i < rowCount Then
                s = dv(i)(Me.ColumnComboBox.ValueMember)
            Else
                s = DBNull.Value
            End If
            MyBase.SetColumnValueAtRow([source], rowNum, s)
        End Sub 'SetColumnValueAtRow


    End Class 'DataGridComboBoxColumn


End Namespace


0
 
LVL 25

Expert Comment

by:RonaldBiemans
Comment Utility
or look here

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q480q

there is a databound example in both c# and VB.net
0
 
LVL 25

Expert Comment

by:RonaldBiemans
Comment Utility
Bye the way, the databound sample is at the bottom of the article. It's a complete working project.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

13 Experts available now in Live!

Get 1:1 Help Now