vb.net Datagridview 下拉框应用

    Public Class Form1


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            Me.Cursor = Cursors.WaitCursor

            DataGridView1.AutoGenerateColumns = False

            Dim cmbInitData As DataTable = GetInitialComboBoxData()
            ' DataGridViewのコンボボックスの生成と初期化
            CreateGridOfComboBoxAndInitial(DataGridView1, cmbInitData, "value", "name", 1)

            ' グリッドデータの初期化
            Dim grdDataSource As DataTable = GetGridData()
            DataGridView1.DataSource = grdDataSource

            ' DBのValueにより、コンボボックス選択項目の設定
            SetGridCmbValueByDB(DataGridView1, grdDataSource, 1, 2)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            Me.Cursor = Cursors.Default
        End Try
    End Sub


    ''' <summary>
    ''' グリッド初期化データの取得
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetGridData() As DataTable
        Dim conn As New ADODB.Connection
        Dim constr As String
        constr = ("Driver={SQL Server};server=xx-pc\SQLEXPRESS;Uid=xx;Pwd=xxxx;Database=TestDB")
        Dim Rec As New ADODB.Recordset
        Dim recordTable As New DataTable
        Dim ChangeDA As New OleDb.OleDbDataAdapter
        Dim sql As String

        conn.Open(constr)
        sql = "select * from GVTest"
        Rec.Open(sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
        ChangeDA.Fill(recordTable, Rec)
        Rec.Close()
        conn.Close()
        Return recordTable
    End Function


    ''' <summary>
    ''' グリッド内コンボボックス初期化データの取得
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetInitialComboBoxData() As DataTable
        Dim conn As New ADODB.Connection
        Dim constr As String
        Dim Rec As New ADODB.Recordset
        Dim recordTable As New DataTable
        Dim ChangeDA As New OleDb.OleDbDataAdapter
        Dim sql As String

        constr = ("Driver={SQL Server};server=xxx-pc\SQLEXPRESS;Uid=xx;Pwd=xxxx;Database=TestDB")
        conn.Open(constr)

        sql = "select value, name from Dictionary order by sortid"
        Rec.Open(sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
        ChangeDA.Fill(recordTable, Rec)
        Rec.Close()
        conn.Close()
        Return recordTable
    End Function


    ''' <summary>
    ''' グリッドにコンボボックスの生成とデータ初期化
    ''' </summary>
    ''' <param name="grd"></param>
    ''' <param name="dataSource"></param>
    ''' <param name="valueField"></param>
    ''' <param name="display"></param>
    ''' <param name="cmbOfDispIndex"></param>
    ''' <remarks></remarks>
    Private Sub CreateGridOfComboBoxAndInitial(ByVal grd As DataGridView, ByVal dataSource As DataTable, _
                                             ByVal valueField As String, ByVal display As String, _
                                             ByVal cmbOfDispIndex As Integer)
        Dim cmbCol As New DataGridViewComboBoxColumn()


        cmbCol.HeaderText = "comboxColumn"
        cmbCol.ValueMember = valueField
        cmbCol.DisplayMember = display
        cmbCol.Name = "comboxColumn"
        cmbCol.DataSource = dataSource

        grd.Columns.Insert(cmbOfDispIndex, cmbCol)
        ' 表示形式の設定  
        cmbCol.DisplayStyleForCurrentCellOnly = True
        cmbCol.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
    End Sub


    ''' <summary>
    ''' DBの値をグッリドのコンボボックスに設定
    ''' </summary>
    ''' <param name="grd"></param>
    ''' <param name="dataSource"></param>
    ''' <param name="cmbClmIndex"></param>
    ''' <param name="dbValueFieldIndex"></param>
    ''' <remarks></remarks>
    Private Sub SetGridCmbValueByDB(ByVal grd As DataGridView, ByVal dataSource As DataTable, _
                                    ByVal cmbClmIndex As Integer, ByVal dbValueFieldIndex As Integer)
        For i As Integer = 0 To dataSource.Rows.Count - 1
            grd.Rows(i).Cells(cmbClmIndex).Value = dataSource.Rows(i).Item(dbValueFieldIndex).ToString()
        Next
    End Sub


    'CellEnterイベントハンドラ(下拉框单击一次打开)
    Private Sub DataGridView1_CellEnter(ByVal sender As Object, _
            ByVal e As DataGridViewCellEventArgs) _
            Handles DataGridView1.CellEnter
        Dim dgv As DataGridView = CType(sender, DataGridView)

        If dgv.Columns(e.ColumnIndex).Name = "comboxColumn" AndAlso _
            TypeOf dgv.Columns(e.ColumnIndex) Is DataGridViewComboBoxColumn Then
            SendKeys.Send("{F4}")
        End If
    End Sub


    Private dataGridViewComboBox As DataGridViewComboBoxEditingControl = Nothing
    ''' <summary>
    ''' EditingControlShowingイベントハンドラ
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, _
            ByVal e As DataGridViewEditingControlShowingEventArgs) _
            Handles DataGridView1.EditingControlShowing
        '表示されているコントロールがDataGridViewComboBoxEditingControlか調べる
        If TypeOf e.Control Is DataGridViewComboBoxEditingControl Then
            Dim dgv As DataGridView = CType(sender, DataGridView)

            '該当する列か調べる
            If dgv.CurrentCell.OwningColumn.Name = "comboxColumn" Then
                '編集のために表示されているコントロールを取得
                Me.dataGridViewComboBox = _
                    CType(e.Control, DataGridViewComboBoxEditingControl)
                'SelectedIndexChangedイベントハンドラを追加
                AddHandler Me.dataGridViewComboBox.SelectedIndexChanged, _
                    AddressOf dataGridViewComboBox_SelectedIndexChanged
            End If
        End If
    End Sub


    ''' <summary>
    ''' CellEndEditイベントハンドラ
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, _
            ByVal e As DataGridViewCellEventArgs) _
            Handles DataGridView1.CellEndEdit
        'SelectedIndexChangedイベントハンドラを削除
        If Not (Me.dataGridViewComboBox Is Nothing) Then
            RemoveHandler Me.dataGridViewComboBox.SelectedIndexChanged, _
                AddressOf dataGridViewComboBox_SelectedIndexChanged
            Me.dataGridViewComboBox = Nothing
        End If
    End Sub


    ''' <summary>
    ''' SelectedIndexChangedイベントハンドラ
    ''' </summary>
    ''' <remarks>DataGridViewに表示されているコンボボックスのValueMemberとDisplayMemberを取得する</remarks>
    Private Sub dataGridViewComboBox_SelectedIndexChanged(ByVal sender As Object, _
            ByVal e As EventArgs)
        '選択されたアイテムを表示
        Dim cb As DataGridViewComboBoxEditingControl = _
            CType(sender, DataGridViewComboBoxEditingControl)
        ' 編集中のValueMemberを取得する（DataGridView1.CurrentRow.Cells(1).Value.ToString()エラーが出る重要）
        MessageBox.Show(cb.SelectedValue)
        ' 編集中のDisplayMemberを取得する（EditedFormattedValue重要）
        MessageBox.Show(DataGridView1.CurrentRow.Cells("comboxColumn").EditedFormattedValue)
    End Sub


    ''' <summary>
    ''' グリッド内コンボボックスセル値の取得
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            ' 編集後のValueMemberを取得する
            MessageBox.Show(DataGridView1.CurrentRow.Cells(1).Value.ToString())
            ' 編集後のDisplayMemberを取得する
            MessageBox.Show(DataGridView1.CurrentRow.Cells("comboxColumn").FormattedValue.ToString())
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            Me.Cursor = Cursors.Default
        End Try

    End Sub


End Class