VB.Net 绑定 datagridview Comboboxcolumn 到 datagridviewTextboxColumn

VB.Net binding datagridview Comboboxcolumn to datagridviewTextboxColumn

提问by Hilal Al-Rajhi

I am loading data from two tables: institute and country. Institute has 3 columns: instId, name, countryId. And country has 2 columns: countryId, name where countryId is a foreign key from country table. I fill these two tables in dataset. I have datagridview and set its datasource to institute table in my dataset. I also create datagridviewcomboboxcolumn and bind it country table. Have a look to the following code:

我正在从两个表中加载数据：institute 和 country。Institute 有 3 列：instId、name、countryId。country 有 2 列：countryId，name，其中 countryId 是 country 表中的外键。我在数据集中填充了这两个表。我有 datagridview 并将其数据源设置为在我的数据集中建立表。我还创建了 datagridviewcomboboxcolumn 并将其绑定到国家/地区表。看看下面的代码：

    Public Class frmDGV

    Dim sqlConn As SqlConnection
    Dim dsOptions As DataSet
    Dim daInstitute As SqlDataAdapter
    Dim daAdapter As SqlDataAdapter
    Dim bsCountry As BindingSource

    Private Sub frmTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dsOptions = New DataSet
            loadOptions()

            dgvInstitute.DataSource = dsOptions.Tables("institute")
            bsCountry = New BindingSource(dsOptions, "country")

            Dim col As New DataGridViewComboBoxColumn
            col.DataPropertyName = "countryName"
            col.HeaderText = "Country"
            col.Name = "cName"

            col.DataSource = bsCountry
            col.DisplayMember = "countryName"
            col.ValueMember = "countryId"

            dgvInstitute.Columns.Add(col)
            dgvInstitute.Columns(0).Width = 60
            dgvInstitute.Columns(1).Width = 200
            dgvInstitute.Columns(2).Width = 60
            dgvInstitute.Columns(3).Width = 120

        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
    End Sub

    Sub loadOptions()
        Dim sql As String

        Try
            sqlConn = New SqlConnection(connString)
            sqlConn.Open()

            sql = "select instId, name, countryId from institute"
            daInstitute = New SqlDataAdapter(sql, sqlConn)
            daInstitute.Fill(dsOptions, "institute")
            '----------------------------------------------------------------------

            sql = "select countryId, countryName from country"
            daAdapter = New SqlDataAdapter(sql, sqlConn)
            daAdapter.Fill(dsOptions, "country")
            '----------------------------------------------------------------------

            sqlConn.Close()
        Catch ex As Exception
            sqlConn.Close()
            MsgBox(Err.Description)
        End Try
    End Sub
End Class

How can I display the proper country name in the combobox based on the countryId in the datagridview using the binding techniques not using a loop? See the following picture:

如何使用不使用循环的绑定技术基于 datagridview 中的 countryId 在组合框中显示正确的国家/地区名称？请看下图：

回答by Fabio

Change DataPropertyNamefor comboboxcolumn in your datagridview:

更改DataPropertyNamedatagridview 中的组合框列：

...
col.DataPropertyName = "countryId"
...

.DataPropertyName- is a column name from datagridview.DataSource,which you want to show in the current column.

.DataPropertyName- 是来自 的列名称datagridview.DataSource，您希望在当前列中显示该名称。

回答by JohnyHarkness

To be honest im not sure if you can due to the way you have bound this.

老实说，由于您绑定的方式，我不确定您是否可以。

Does the country column need to be in a ComboBox? You could just include the country name in your first query.

country 列是否需要在 ComboBox 中？您可以在第一个查询中包含国家/地区名称。

The only way i could really see of doing this would be something like

我能真正看到这样做的唯一方法是

for(int i = 0; i<dgvInstitute.Rows.Count; i++)
{
    dgvInstitute.Rows[rowIndexYouWant].Cells["cName"].Value = dgvInstitute.Rows[rowIndexYouWant].Cells["countryId"].Value;

}

回答by Yat Fei Leong

Imports System.Data.SqlClient
Imports System.Data

Public Class DataGridViewBind

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



    'Preparing The Column
    Dim col0 As New DataGridViewTextBoxColumn
    col0.HeaderText = "InstID"
    col0.Name = "InstID"
    col0.Width = 100
    col0.MaxInputLength = 100
    dgv1.Columns.Add(col0)

    Dim col1 As New DataGridViewTextBoxColumn
    col1.HeaderText = "Name"
    col1.Name = "Name"
    col1.Width = 100
    col1.MaxInputLength = 100
    dgv1.Columns.Add(col1)

    Dim col2 As New DataGridViewTextBoxColumn
    col2.HeaderText = "CountryID"
    col2.Name = "CountryID"
    col2.Width = 100
    col2.MaxInputLength = 100
    dgv1.Columns.Add(col2)

    Dim Col3 As New DataGridViewComboBoxColumn
    Col3.HeaderText = "CountryName"
    Col3.Name = "CountryName"
    Col3.Width = 100
    ProcessCountryComboBox(Col3) 'Bind Country Name to the ComboBox
    dgv1.Columns.Add(Col3)


    'Prepare the DataGridView

    ListDataGridView(dgv1)


End Sub

Public Sub ProcessCountryComboBox(ByVal colX As DataGridViewComboBoxColumn)
    Dim StrConn As String = My.Settings.ImportLinkCS
    Dim CN = New SqlConnection(StrConn)

    LoadGridComboByQuery(colX, "Select Name from Country order by Name")

End Sub


Public Sub LoadGridComboByQuery(ByVal cbo As DataGridViewComboBoxColumn, ByVal SQLString As String)
    Dim i As Integer
    Dim cmdUser As New SqlCommand
    Dim daUser As New SqlDataAdapter
    Dim dsUser As New DataSet
    Dim dtUser As New DataTable
    Dim conn As New SqlConnection(My.Settings.ImportLinkCS)
    Try
        cbo.Items.Clear()

        cmdUser = conn.CreateCommand
        cmdUser.CommandText = SQLString
        daUser.SelectCommand = cmdUser
        daUser.Fill(dsUser, "Sqltable")
        dtUser = dsUser.Tables("Sqltable")
        For i = 0 To dtUser.Rows.Count - 1
            cbo.Items.Add(dtUser.Rows(i).Item(0))
        Next

        cbo.Items.Add("")

    Catch ex As Exception
        MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Error conection!!")
    End Try
    conn.Close()
    conn = Nothing
End Sub

Private Sub ListDataGridView(ByVal dgv As DataGridView)



    dgv.DataSource = Nothing
    Try
        Dim dbBindSource As New BindingSource
        Dim strCon As String = My.Settings.ImportLinkCS
        Dim strSQL As String = "Select I.InstID, I.Name, I.Country, C.Name as CountryName " + _
                                "from Institute I left Join Country C on C.CountryID = I.Country"

        Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strCon)
        Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataAdapter)

        'Populate a new data table and bind it to the BindingSource.
        Dim table As DataTable = New DataTable()
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        dataAdapter.Fill(table)

        dgv.AutoGenerateColumns = False


        dgv.Columns(0).DataPropertyName = "InstID"
        dgv.Columns(1).DataPropertyName = "Name"
        dgv.Columns(2).DataPropertyName = "Country"
        dgv.Columns(3).DataPropertyName = "CountryName"


        dbBindSource.DataSource = table
        //Resize the DataGridView columns to fit the newly loaded content.
        //dgv.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)

        dgv.DataSource = dbBindSource


    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "error")
    End Try


End Sub


End Class