none
combobox properties

    Question

  • I thought I knew how to do this, but I apparently don't.

    I have a combobox that needs to have three values set.  The first is that the combobox should display whatever currently exists in the record and it should show the list of values from a lookup table for selection.

    This is what I thought would work, but as usual I was wrong.  For the datasource, I have attempted many different approaches, including even creating a dataset approach, none of which worked.

    If I have the first line of code by itself, the combobox will display the value of whatever exists in the column, but no values for display and value.  When I use that with the rest of the code, I get nothing at all.

                    cboOwner.DataBindings.Add("Text", MasterBaseTable, "chrOwner")
                    cboOwner.DataSource = _MasterBase1_0DataSet.lkpDepartment
                    cboOwner.DisplayMember = "chrDepartment"
                    cboOwner.ValueMember = "chrDepartment"


    gwboolean

    Saturday, May 25, 2019 3:17 PM

Answers

  • The suggestion I got were pretty good, but they seemed overly complicated.  So what I went back and did was the following:

    #Region "Query"
                    MasterBaseCommand = New OleDbCommand("SELECT * FROM tblChangeMaster WHERE chrChangeID = '" & _strChangeMasterID & "'", MasterBaseConnection)
                    MasterBaseAdapter.SelectCommand = MasterBaseCommand
                    MasterBaseAdapter.Fill(MasterBaseTable)
                    'Currency Manager
                    MasterBaseManager = DirectCast(BindingContext(MasterBaseTable), CurrencyManager)
    #End Region
    #Region "Control Binding"
                    lblChangeID.DataBindings.Add("Text", MasterBaseTable, "chrChangeID")
                    lblMasterBaseID.DataBindings.Add("Text", MasterBaseTable, "chrArticleID")
                    txtTitle.DataBindings.Add("Text", MasterBaseTable, "chrTitle")
                    txtRevision.DataBindings.Add("Text", MasterBaseTable, "chrRevision")
                    lblArticle.DataBindings.Add("Text", MasterBaseTable, "chrArticleObject")
                    lblFilePath.DataBindings.Add("Text", MasterBaseTable, "chrFilePath")
                    txtChanges.DataBindings.Add("Text", MasterBaseTable, "chrChangeMade")
                    txtReason.DataBindings.Add("Text", MasterBaseTable, "chrChangeReason")
                    txtResult.DataBindings.Add("Text", MasterBaseTable, "chrChangeResult")
                    txtOpen.DataBindings.Add("Text", MasterBaseTable, "dteOpen", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtSubmit.DataBindings.Add("Text", MasterBaseTable, "dteSubmit", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtApprove.DataBindings.Add("Text", MasterBaseTable, "dteApprove", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtTrain.DataBindings.Add("Text", MasterBaseTable, "dteTrain", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtEffective.DataBindings.Add("Text", MasterBaseTable, "dteEffective", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtClose.DataBindings.Add("Text", MasterBaseTable, "dteClose", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    chkQualify.DataBindings.Add("Checked", MasterBaseTable, "blnQualify", True, DataSourceUpdateMode.OnValidation, CheckState.Indeterminate)
                    mboEmployee.DataBindings.Add("Text", MasterBaseTable, "chrManager")
                    cboOwner.DataBindings.Add("Text", MasterBaseTable, "chrOwner")
                    'cboOwner.DataSource = MasterBaseTable.DefaultView
                    'cboOwner.DisplayMember = "chrDepartment"
                    'cboOwner.SelectedItem = "chrDepartment"
                    cboWhere.DataBindings.Add("Text", MasterBaseTable, "chrWhere")
    #End Region
    
    
            Dim EmployeeTable = New DataTable()
            MasterBaseCommand = New OleDbCommand("SELECT chrFullName FROM tblEmployee", MasterBaseConnection)
            MasterBaseAdapter.SelectCommand = MasterBaseCommand
            MasterBaseAdapter.Fill(EmployeeTable)
            mboEmployee.DataSource = EmployeeTable
            mboEmployee.DisplayMember = "chrFullName"
            mboEmployee.ValueMember = "chrFullName"
            Dim OwnerTable = New DataTable()
            MasterBaseCommand = New OleDbCommand("SELECT chrDepartment FROM lkpDepartment", MasterBaseConnection)
            MasterBaseAdapter.SelectCommand = MasterBaseCommand
            MasterBaseAdapter.Fill(OwnerTable)
            cboOwner.DataSource = OwnerTable
            cboOwner.DisplayMember = "chrDepartment"
            cboOwner.ValueMember = "chrDepartment"
            Dim WhereTable = New DataTable()
            MasterBaseCommand = New OleDbCommand("SELECT chrWhere FROM lkpWhere", MasterBaseConnection)
            MasterBaseAdapter.SelectCommand = MasterBaseCommand
            MasterBaseAdapter.Fill(WhereTable)
            cboWhere.DataSource = WhereTable
            cboWhere.DisplayMember = "chrWhere"
            cboWhere.ValueMember = "chrWhere"

    This actually worked quite well, but it still seems to me to be an overkill of what I thought would be a very easy process.  I only displayed one of three comboboxes.  All three met requirements.


    gwboolean

    • Marked as answer by gwboolean Sunday, May 26, 2019 4:41 PM
    Sunday, May 26, 2019 4:41 PM

All replies

  • Hello,

    Are there two different data sources? I ask because you have one data source for line one and another datasource for line two. Seems there would be one or both lines.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 25, 2019 4:37 PM
    Moderator
  • That is the part I am unable to figure out.  One is dataset and the other is a table.  I thought, what the hell, why not try a code salad and see what happens?  The answer is nothing.

    So forget the dataset, I don't use it anyway.  It was just a failed experiment. 

    Which brings me back to how do I set the properties so that the combobox displays whatever is in the column of the table queried, and simultaneously show what is contained in a lookup table in the dropdown list.

    The first line of code gets me the first part.

    Oh, and I have tried it using MasterBaseTable as the datasource as well.


    gwboolean


    • Edited by gwboolean Saturday, May 25, 2019 4:52 PM
    Saturday, May 25, 2019 4:51 PM
  • One way to do this is to use a List(Of T) for populating the ComboBox from a reference table then load say a DataGridView (which in this case could be other controls e.g. TextBox etc) with a DataTable. Both the list and DataTable have a key field to track the binding.

    In the example below I have the following class to hold contact types. ToString override will be the display member for the ComboBox.

    Public Class Contact
        Public Property ContactTypeIdentifier() As Integer
        Public Property Name() As String
    
        Public Overrides Function ToString() As String
            Return Name
        End Function
    End Class

    Load the contact types

    Public Function ContactTitles() As List(Of Contact)
        Dim contactList As New List(Of Contact)
    
        Dim connectionString1 = "Data Source=KARENS-PC;Initial Catalog=NorthWindAzure;Integrated Security=True"
        Dim selectStatement = "SELECT ContactTypeIdentifier,ContactTitle FROM ContactType"
    
        Using cn As New SqlConnection With {.ConnectionString = connectionString1}
            Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
    
                cn.Open()
                Dim reader = cmd.ExecuteReader()
                While reader.Read()
                    contactList.Add(New Contact() With
                                       {.ContactTypeIdentifier = reader.GetInt32(0), .Name = reader.GetString(1)})
                End While
            End Using
        End Using
    
        Return contactList
    End Function

    Load the DataTable

    Public Function LoadCustomerRecords() As DataTable
        Dim connectionString1 = "Data Source=KARENS-PC;Initial Catalog=NorthWindAzure1;Integrated Security=True"
        Dim selectStatement = "SELECT CustomerIdentifier, CompanyName, ContactTypeIdentifier FROM Customers"
    
        Dim customerDataTable = New DataTable
    
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
    
                cmd.CommandText = selectStatement
    
                cn.Open()
    
                customerDataTable.Load(cmd.ExecuteReader())
    
            End Using
        End Using
    
        Return customerDataTable
    End Function

    Form code calls the method above which are in a class named DataOperationsSqlServer (well the name comes from an article I'm working on and writing your code suggestion in that solution). Both the List(Of Contact) and the DataTable are linked by the field/property name ContactTypeIdentifier. Form load sets up displaying data while SelectedIndexChange event for the ComboBox handles syncing things.

    Imports BasicRead.Classes
    
    Public Class Form1
        Private bsCustomers As New BindingSource
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim dataOperations As New DataOperationsSqlServer
            bsCustomers.DataSource = dataOperations.LoadCustomerRecords
            ContactTypeComboBox.DataSource = dataOperations.ContactTitles()
            DataGridView1.DataSource = bsCustomers.DataSource
    
        End Sub
    
        Private Sub ContactTypeComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) _
            Handles ContactTypeComboBox.SelectedIndexChanged
    
            CType(bsCustomers.Current, DataRowView).
                Row.SetField("ContactTypeIdentifier",
                             CType(ContactTypeComboBox.SelectedItem, Contact).ContactTypeIdentifier)
        End Sub
    End Class

    In the screenshot below changing the selected item in the ComboBox updates ContactTypeIdentifier in the DataGridView or if no DataGridView updates the field in the DataTable which when you are ready to save will have the correct value. In a real app we would not be displaying the first and last column in the DataGridView.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 25, 2019 6:08 PM
    Moderator
  • Hi

    Re-reading you question several times did not enlighten me enough to propose an answer.

    Give an example of the data and what you want in the CB (again)

    There appears tobe a DataTable a List(Of Something) and a ComboBox - it is straightforward to assign a column of the DT tothe CB,but where does the List come in?


    Regards Les, Livingston, Scotland

    Saturday, May 25, 2019 6:10 PM
  • The suggestion I got were pretty good, but they seemed overly complicated.  So what I went back and did was the following:

    #Region "Query"
                    MasterBaseCommand = New OleDbCommand("SELECT * FROM tblChangeMaster WHERE chrChangeID = '" & _strChangeMasterID & "'", MasterBaseConnection)
                    MasterBaseAdapter.SelectCommand = MasterBaseCommand
                    MasterBaseAdapter.Fill(MasterBaseTable)
                    'Currency Manager
                    MasterBaseManager = DirectCast(BindingContext(MasterBaseTable), CurrencyManager)
    #End Region
    #Region "Control Binding"
                    lblChangeID.DataBindings.Add("Text", MasterBaseTable, "chrChangeID")
                    lblMasterBaseID.DataBindings.Add("Text", MasterBaseTable, "chrArticleID")
                    txtTitle.DataBindings.Add("Text", MasterBaseTable, "chrTitle")
                    txtRevision.DataBindings.Add("Text", MasterBaseTable, "chrRevision")
                    lblArticle.DataBindings.Add("Text", MasterBaseTable, "chrArticleObject")
                    lblFilePath.DataBindings.Add("Text", MasterBaseTable, "chrFilePath")
                    txtChanges.DataBindings.Add("Text", MasterBaseTable, "chrChangeMade")
                    txtReason.DataBindings.Add("Text", MasterBaseTable, "chrChangeReason")
                    txtResult.DataBindings.Add("Text", MasterBaseTable, "chrChangeResult")
                    txtOpen.DataBindings.Add("Text", MasterBaseTable, "dteOpen", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtSubmit.DataBindings.Add("Text", MasterBaseTable, "dteSubmit", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtApprove.DataBindings.Add("Text", MasterBaseTable, "dteApprove", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtTrain.DataBindings.Add("Text", MasterBaseTable, "dteTrain", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtEffective.DataBindings.Add("Text", MasterBaseTable, "dteEffective", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    txtClose.DataBindings.Add("Text", MasterBaseTable, "dteClose", True, DataSourceUpdateMode.OnValidation, DateFormat.ShortDate)
                    chkQualify.DataBindings.Add("Checked", MasterBaseTable, "blnQualify", True, DataSourceUpdateMode.OnValidation, CheckState.Indeterminate)
                    mboEmployee.DataBindings.Add("Text", MasterBaseTable, "chrManager")
                    cboOwner.DataBindings.Add("Text", MasterBaseTable, "chrOwner")
                    'cboOwner.DataSource = MasterBaseTable.DefaultView
                    'cboOwner.DisplayMember = "chrDepartment"
                    'cboOwner.SelectedItem = "chrDepartment"
                    cboWhere.DataBindings.Add("Text", MasterBaseTable, "chrWhere")
    #End Region
    
    
            Dim EmployeeTable = New DataTable()
            MasterBaseCommand = New OleDbCommand("SELECT chrFullName FROM tblEmployee", MasterBaseConnection)
            MasterBaseAdapter.SelectCommand = MasterBaseCommand
            MasterBaseAdapter.Fill(EmployeeTable)
            mboEmployee.DataSource = EmployeeTable
            mboEmployee.DisplayMember = "chrFullName"
            mboEmployee.ValueMember = "chrFullName"
            Dim OwnerTable = New DataTable()
            MasterBaseCommand = New OleDbCommand("SELECT chrDepartment FROM lkpDepartment", MasterBaseConnection)
            MasterBaseAdapter.SelectCommand = MasterBaseCommand
            MasterBaseAdapter.Fill(OwnerTable)
            cboOwner.DataSource = OwnerTable
            cboOwner.DisplayMember = "chrDepartment"
            cboOwner.ValueMember = "chrDepartment"
            Dim WhereTable = New DataTable()
            MasterBaseCommand = New OleDbCommand("SELECT chrWhere FROM lkpWhere", MasterBaseConnection)
            MasterBaseAdapter.SelectCommand = MasterBaseCommand
            MasterBaseAdapter.Fill(WhereTable)
            cboWhere.DataSource = WhereTable
            cboWhere.DisplayMember = "chrWhere"
            cboWhere.ValueMember = "chrWhere"

    This actually worked quite well, but it still seems to me to be an overkill of what I thought would be a very easy process.  I only displayed one of three comboboxes.  All three met requirements.


    gwboolean

    • Marked as answer by gwboolean Sunday, May 26, 2019 4:41 PM
    Sunday, May 26, 2019 4:41 PM