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