Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

inserting a combobox in a datagrid

Posted on 2004-09-15
3
297 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
ID: 12065760
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
ID: 12065832
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
ID: 12065866
Bye the way, the databound sample is at the bottom of the article. It's a complete working project.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

860 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