VB.Net binding datagridview Comboboxcolumn a datagridviewTextboxColumn

bindingsource

Question

Estoy cargando datos de dos tablas: instituto y país. El instituto tiene 3 columnas: instId, name, countryId. Y el país tiene 2 columnas: countryId, nombre donde countryID es una clave externa de la tabla de países. Lleno estas dos tablas en el conjunto de datos. Tengo datagridview y establezco su fuente de datos para instituir la tabla en mi conjunto de datos. También creo datagridviewcomboboxcolumn y lo ato la tabla de país. Eche un vistazo al siguiente código:

    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

¿Cómo puedo mostrar el nombre de país correcto en el cuadro combinado basado en el identificador de país en la vista de cuadrícula de datos utilizando las técnicas de enlace que no utilizan un bucle? Ver la siguiente imagen:

Answer 1

Cambie DataPropertyName para comboboxcolumn en su datagridview:

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

.DataPropertyName - es un nombre de columna de datagridview.DataSource , que desea mostrar en la columna actual.

Answer 2

Para ser sincero, no estoy seguro si puedes debido a la forma en que has vinculado esto.

¿La columna del país debe estar en un ComboBox? Simplemente podría incluir el nombre del país en su primera consulta.

La única forma que realmente podría ver de hacer esto sería algo así como

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

}

Answer 3

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