Getting Value Member of Combobox

[riechan]

Let's say I bound a combobox to a datatable. How can I get the valuemember of the currently selected item in the combobox (for example, when I have to save the data, I need to get the valuemember - PatronTypePK - of the currently selected item in cmbPatronType)? And can anyone check if I made the code correctly or if there are any programming practice errors/inconsistencies? I wanted to fetch two tables into a single dataset (Patron, PatronType). Do I have to make a datatable for each, or can I simply use the dataadapter?

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

        'MUST FIRST CHECK IF THERE ARE ANY RECORDS IN THE PATRON TYPE MANAGER

        dbConn.ConnectionString = "Integrated Security=SSPI; Initial Catalog=CBIS; Persist Security Info=False;"
        dbConn.Open()

        'initially populate the dataset
        dbAdpPatron = New SqlClient.SqlDataAdapter("SELECT * FROM Patron", dbConn)
        dbDsetPatron = New DataSet
        dbAdpPatron.Fill(dbDsetPatron, "Patron")

        'display data from the database into the text fields and datagrid
        If dbDsetPatron.Tables(0).Rows.Count > 0 Then
            FillFields()
            btnFirst.Enabled = False
            btnPrevious.Enabled = False

            dgPatron.DataSource = dbDsetPatron
            dgPatron.DataMember = "Patron"

            'populate the adapter of patrontypes that will fill the combobox
            dbAdpPatronType = New SqlClient.SqlDataAdapter("SELECT PatronTypeName FROM PatronType", dbConn)
            dbDsetPatron = New DataSet
            dbAdpPatron.Fill(dbDsetPatron, "PatronType")

            With cmbPatronType
                .DataSource = dbDsetPatron.Tables("PatronType")
                .DisplayMember = "PatronTypeName"
                .ValueMember = "PatronTypeDesc"
                .SelectedIndex = 0
            End With

        ElseIf dbDsetPatron.Tables(0).Rows.Count = 0 Then
            MsgBox("There are no available Patron records. Click the" & vbCrLf & "[Register] button to start adding records.", MsgBoxStyle.Information, "No Available Records")
        End If

        're-initilize the flags as FALSE
        addflag = False
        editflag = False

        dbConn.Close()
    End Sub

The code for btnSave:

Dim Validated As Boolean = False

        Dim dbInsert As New SqlCommand("INSERT INTO Patron(PatronNumber, FirstName, LastName, StreetAddress, City, " & _
                "ContactNumber, EmailAddress, Remarks, ViolationCount, Status) VALUES (@insPatronNumber, @insFirstName, @insLastName," & _
                "@insStreetAddress, @insCity, @insContactNumber, @insEmailAddress, @insRemarks, @insViolationCount, @Status)", dbConn)

        'fetch the valuemember of the currently selected item in cmbPatronType
        PatronNumber = cmbPatronType.ValueMember
        MsgBox(PatronNumber)
        Exit Sub

        FirstName = Trim(txtFirstName.Text)
        LastName = Trim(txtLastName.Text)
        StreetAddress = Trim(txtStreetAddress.Text)
        City = Trim(txtCity.Text)
        ContactNumber = Trim(txtContactNumber.Text)
        EmailAddress = Trim(txtEmailAddress.Text)
        Remarks = Trim(txtRemarks.Text)
        ViolationCount = Val(txtViolationCount.Text)

        dbConn.Open()
        dbAdpPatron = New SqlClient.SqlDataAdapter("SELECT * FROM Patron", dbConn)
        dbDsetPatron = New DataSet
        dbAdpPatron.Fill(dbDsetPatron, "Patron")

        'sql insert cmd parameter declaration
        dbAdpPatron.InsertCommand = dbInsert
        With dbInsert
            .Connection = dbConn

            .Parameters.Add(New SqlParameter("@insPatronNumber", SqlDbType.VarChar, 15, "PatronNumber"))
            .Parameters.Add(New SqlParameter("@insFirstName", SqlDbType.VarChar, 40, "FirstName"))
            .Parameters.Add(New SqlParameter("@insLastName", SqlDbType.VarChar, 40, "LastName"))
            .Parameters.Add(New SqlParameter("@insStreetAddress", SqlDbType.VarChar, 60, "StreetAddress"))
            .Parameters.Add(New SqlParameter("@insCity", SqlDbType.VarChar, 30, "City"))
            .Parameters.Add(New SqlParameter("@insContactNumber", SqlDbType.VarChar, 20, "ContactNumber"))
            .Parameters.Add(New SqlParameter("@insEmailAddress", SqlDbType.VarChar, 40, "EmailAddress"))
            .Parameters.Add(New SqlParameter("@insRemarks", SqlDbType.VarChar, 50, "Remarks"))
            .Parameters.Add(New SqlParameter("@insViolationCount", SqlDbType.Int, 4, "ViolationCount"))
            .Parameters.Add(New SqlParameter("@insStatus", SqlDbType.VarChar, 10, "Status"))
        End With

        'check if values entered are valid
        Validated = Validate(FirstName, LastName, StreetAddress, City, ContactNumber, EmailAddress, Remarks, ViolationCount)

        If Validated = True Then

            If addflag = True Then
                'add clause

                Try
                    PatronNumber = Str(Date.Now.Year) & Str(Date.Now.Month) & Str(Date.Now.Day) & Str(Date.Now.Hour) & Str(Date.Now.Minute) & Str(Date.Now.Second)
                    Status = "Authorized"

                    dr = dbDsetPatron.Tables(0).NewRow()
                    dr("FirstName") = FirstName
                    dr("LastName") = LastName
                    dr("StreetAddress") = StreetAddress
                    dr("City") = City
                    dr("ContactNumber") = ContactNumber
                    dr("EmailAddress") = EmailAddress
                    dr("Remarks") = Remarks
                    dr("ViolationCount") = ViolationCount
                    dr("Status") = Status

                    dbDsetPatron.Tables(0).Rows.Add(dr)
                    dbAdpPatron.Update(dbDsetPatron.Tables("Patron"))
                    dbDsetPatron.AcceptChanges()

                    dbDsetPatron.Clear()
                    dbAdpPatron.SelectCommand.CommandText = "SELECT * FROM Patron"
                    dbAdpPatron.Fill(dbDsetPatron, "Patron")

                    'refresh the data on the datagrid
                    dgPatron.DataSource = dbDsetPatron
                    dgPatron.DataMember = "Patron"

                    CurIndex = dbDsetPatron.Tables(0).Rows.Count - 1
                    FillFields()

                    MsgBox(FirstName & " " & LastName & " registered.", MsgBoxStyle.Information, "Patron Registered")
                    disableFields()
                    addflag = False

                Catch parameter As System.Data.SqlClient.SqlException
                    Console.WriteLine(parameter.StackTrace)
                    MsgBox(parameter.ToString)
                Catch invalidparameter As InvalidOperationException
                    MessageBox.Show(invalidparameter.Message)
                End Try
...

[jmcilhinney]

The specific point of setting the ValueMember property is so that, when the user selects an item, you can get the value of the named member from the control's SelectedValue property.

[riechan]

The specific point of setting the ValueMember property is so that, when the user selects an item, you can get the value of the named member from the control's SelectedValue property.

@JMC:

Question, in this block of code, was the content of the PatronType table bound to cmbPatronType? It is, isn't it? I feel as if the bound was only read-only access... but then again, if it was bound in a read-only access, I should still be able to get the PatronTypeID from cmbPatronType, right? If not ValueMember, what method should I use then?

PatronNumber = cmbPatronType.ValueMember

            'populate the dataset of patrontypes that will fill the combobox
            dbAdpPatronType = New SqlClient.SqlDataAdapter("SELECT PatronTypeName FROM PatronType", dbConn)
            dbDsetPatron = New DataSet
            dbAdpPatron.Fill(dbDsetPatron, "PatronType")

            With cmbPatronType
                .DataSource = dbDsetPatron.Tables("PatronType")
                .DisplayMember = "PatronTypeName"
                .ValueMember = "PatronTypeID"
                .SelectedIndex = 0
            End With

[jmcilhinney]

First up, this code:

            With cmbPatronType
                .DataSource = dbDsetPatron.Tables("PatronType")
                .DisplayMember = "PatronTypeName"
                .ValueMember = "PatronTypeID"
                .SelectedIndex = 0
            End With

should be this:

            With cmbPatronType
                .DisplayMember = "PatronTypeName"
                .ValueMember = "PatronTypeID"
                .DataSource = dbDsetPatron.Tables("PatronType")
            End With

Your code will certainly work without any real issue but it's more efficient to set the DataSource last and there's no point setting the SelectedIndex to 0 because the first item will be selected by default.

As for the question, you quoted my previous post but you appear not to have read it:

you can get the value of the named member from the control's SelectedValue property

[riechan]

Like this?

PatronNumber = cmbPatronType.SelectedValue

Thing is, when I check the value using a msgbox, it doesn't return any value. I tried this:

PatronNumber = cmbPatronType.SelectedValue.ToString

But, it returned the following error: Object reference not set to an instance of an object.

[jmcilhinney]

That means that the SelectedValue property doesn't refer to an object. Either no item is selected or the selected item has no value for the property/field specified by the ValueMember.

[riechan]

Okay, so this part of the code initially loads the data from the database once the form loads:

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

        dbConn.ConnectionString = "Integrated Security=SSPI; Initial Catalog=CBIS; Persist Security Info=False;"
        dbConn.Open()

        'initially populate the dataset
        dbAdpPatron = New SqlClient.SqlDataAdapter("SELECT * FROM Patron", dbConn)
        dbDsetPatron = New DataSet
        dbAdpPatron.Fill(dbDsetPatron, "Patron")

        'display data from the database into the text fields and datagrid
        If dbDsetPatron.Tables(0).Rows.Count > 0 Then
            FillFields()
            btnFirst.Enabled = False
            btnPrevious.Enabled = False

            dgPatron.DataSource = dbDsetPatron
            dgPatron.DataMember = "Patron"

            'populate the dataset of patrontypes that will fill the combobox
            dbAdpPatronType = New SqlClient.SqlDataAdapter("SELECT PatronTypeName FROM PatronType", dbConn)
            dbDsetPatron = New DataSet
            dbAdpPatron.Fill(dbDsetPatron, "PatronType")

            With cmbPatronType
                .DisplayMember = "PatronType.PatronTypeName"
                .ValueMember = "PatronType.PatronTypeID"
                .DataSource = dbDsetPatron.Tables("PatronType")
            End With

        ElseIf dbDsetPatron.Tables(0).Rows.Count = 0 Then
            MsgBox("There are no available Patron records. Click the" & vbCrLf & "[Register] button to start adding records.", MsgBoxStyle.Information, "No Available Records")
        End If

        're-initilize the flags as FALSE
        addflag = False
        editflag = False

        dbConn.Close()
    End Sub

I'm thinking that the reason why there is no value that is returned when I use the .SelectedValue property of cmbPatrontype is because I re-created the instance of the dataset here:

    Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegister.Click

        dbConn.Open()

        'check if there are any records in the patron type manager
        dbAdpPatron = New SqlClient.SqlDataAdapter("SELECT * FROM PatronType", dbConn)
RIGHT HERE --->dbDsetPatron = New DataSet
        dbAdpPatron.Fill(dbDsetPatron, "PatronType")

        If dbDsetPatron.Tables(0).Rows.Count = 0 Then
            MsgBox("There are no available Patron Type records. Please create " & vbCrLf & "Patron Type Records in the [Patron Type Manager] form.", MsgBoxStyle.Information, "No Available Patron Type Records")
            dbConn.Close()
            Exit Sub
        End If

        enableFields()
        txtPatronNumber.Clear()
        txtFirstName.Clear()
        txtLastName.Clear()
        txtStreetAddress.Clear()
        txtCity.Clear()
        txtContactNumber.Clear()
        txtEmailAddress.Clear()
        txtRemarks.Clear()
        txtViolationCount.Clear()

        addflag = True
        cmbPatronType.Focus()

        dbConn.Close()
    End Sub

Is my query right or not? I mean, I did place the contents of the PatronType table in the dbAdpPatronType adapter, separate from the dbAdpPatron, but both adapters are in the same dataset. Wait... I think it is because I reinstantiated the dataset, that's why the contents of the dbAdpPatronType adapter is empty, right?

[jmcilhinney]

Try removing the qualifying table name from the DisplayMember and ValueMember, i.e. just use "PatronTypeName" and "PatronTypeID". See if that makes a difference.

[riechan]

I see the problem, as to why the it kept returning a null value. It's because cmbPatronType was never really bounded to the PatronType table. So, the question is, how can I bind the cmbPatronType to the corresponding table?