VB.NET Combobox SelectedValueChanged

chakotha

I have a combobox that I have filled with employees. I want to to query my database based on the selected employees ID. I have

Private Sub ComboBoxSetup()

    Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source _
 = " & Application.StartupPath & "\myDB.mdb"

    Dim myConnectionString As New OleDbConnection(strDSN)

    Dim sql As String = "SELECT EmployeeID, Surname FROM EmployeeDetails _
 WHERE CompanyID=" & CurrentCompanyID & " ORDER BY Surname"

    Dim myDataAdapter As New OleDbDataAdapter(sql, myConnectionString)
    Dim myDataSet As New DataSet

    myDataAdapter.Fill(myDataSet, "Employees")

    Dim myTable As DataTable
    Dim myRow As DataRow

    myTable = myDataSet.Tables("Employees")

    For Each myRow In myTable.Rows
        cmbSurname.ValueMember = myRow("EmployeeID")
        cmbSurname.DisplayMember = myRow("Surname")
        cmbSurname.Items.Add(myRow("Surname"))
    Next

    cmbSurname.SelectedIndex = -1

End Sub

In my SelectedIndexChanged event handler how do I reference the ID corresponding to the selected surname? I have tried a few things.

Public Sub cmbSurname_SelectedIndexChanged(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles cmbSurname.SelectedIndexChanged

     Dim SelectedID as Integer = cmbSurname.ValueMember
     MsgBox(SelectedID)

    End Sub

pulls out the ID matching the last employee to be loaded into the combobox

Wood...Trees...thanks in advance

Kernel32

cmbSurname.SelectedItem

chakotha

Thanks for replying Kernel32 - I haven't got the IDE in front of me now but I think cmbSurname.SelectedItem gave me the selected surname.

What eventually worked was
[php]Dim i As Integer = cmbSurname.SelectedIndex

Dim ID As Integer

ID = myDataSet.Tables(0).Rows(i)("EmployeeID")[/php] in the cmbSurname_SelectedIndexChanged handler.

I think there is a neat way to do it using something like

[php]cmbSurname.datasource = mydataset.tables("Employees")
cmbSurname.valuemember = "EmployeeId"
cmbSurname.displaymember = "Surname"[/php]
instead of
[php]For Each myRow In myTable.Rows
cmbSurname.ValueMember = myRow("EmployeeID")
cmbSurname.DisplayMember = myRow("Surname")
cmbSurname.Items.Add(myRow("Surname"))
Next[/php] in the subroutine that fills the ComboBox but it escaped me that time. The ComboBox wasn't even filling up. I couldn't get the DataAdapter/DataSet/DataTable linkages right I think.

bonkey

Hmmm....your solution appears to produce the correct answer, but it will be dependant on the sort-order of the dataset never changing, and the ds remaining in existence etc.etc.etc.

If I might make a suggestion.....

You've gone to the trouble of using .DisplayMember and .ValueMember , which define the content to be displayed, and the underlying value which you are interested in for each row.

Why not use .SelectedValue to get the ID you're looking for?

You may need to cast back to an int while reading it...not sure, but its a hell of a lot neater than reading back to the dataset.

ANd if you're not going to do that...don't bother with assigning the .SelectedValue, because populating it is only taking up processor time on something you're not gonna use

jc

chakotha

Hi bonkey - how do you mean exactly? The following doesn't produce a value.

[php]Public Sub cmbSurname_SelectedIndexChanged_1(ByVal sender As _
System.Object, ByVal e As System.EventArgs) Handles cmbSurname.SelectedIndexChanged

Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source _
= " & Application.StartupPath & "\PayeRoll.mdb"

Dim myConnectionString As New OleDbConnection(strDSN)

Dim sql As String = "SELECT EmployeeID, Surname FROM EmployeeDetails WHERE _
CompanyID=" & myForms.UserControl1.CurrentCompanyID & " ORDER BY Surname"

Dim myDataAdapter As New OleDbDataAdapter(sql, myConnectionString)
Dim myDataSet As New DataSet

myDataAdapter.Fill(myDataSet, "Employees")

MsgBox("cmbSurname.SelectedValue = " + cmbSurname.SelectedValue)

End Sub

Private Sub ComboBoxSetup()

Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " _
& Application.StartupPath & "\PayeRoll.mdb"

Dim myConnectionString As New OleDbConnection(strDSN)

Dim sql As String = "SELECT EmployeeID, Surname FROM EmployeeDetails _
WHERE CompanyID=" & myForms.UserControl1.CurrentCompanyID & " ORDER BY Surname"

Dim myDataAdapter As New OleDbDataAdapter(sql, myConnectionString)
Dim myDataSet As New DataSet

myDataAdapter.Fill(myDataSet, "Employees")

Dim myTable As DataTable
Dim myRow As DataRow

myTable = myDataSet.Tables("Employees")

For Each myRow In myTable.Rows
cmbSurname.ValueMember = myRow("EmployeeID")
cmbSurname.DisplayMember = myRow("Surname")
cmbSurname.Items.Add(myRow("Surname"))
Next

End Sub[/php]
All that is displayed in the message box is

cmbSurname.SelectedValue =

bonkey

Originally posted by chakotha
Hi bonkey - how do you mean exactly? The following doesn't produce a value.

Hmm. That should work. you may find that you need to use :

cmbSurname.SelectedValue.ToString()

You may need this, because .SelectedValue will typically return an object-type, not a string value.

If that doesn't work, then it may be the way you are populating the combo. Close to what you posted yourself in an earlier post, you should be able to do it with :

[php]
Dim myTable As DataTable

myTable = myDataSet.Tables("Employees")
cmbSurname.DataSource = myTable

cmbSurname.ValueMember = "EmployeeId"
cmbSurname.DisplayMember = "Surname"
[/php]

If that doesn't work, then to be honest....I'm stumped. I'm converting my C# to VB.Net on the fly here, so I can't really tell you why it mightn't be working...

jc

Justice

you should turn
option strict on chakotha.

include line "Option Strict On" on first line of ur vb file)

that will force you to cast all variables into the correct type (it will force you to use cmbSurname.SelectedValue.ToString instead of cmbSurname.SelectedValue)

chakotha

Hey Justice I will try that. It is causing dozens of nasty blue jaggy underlines in the .vb file so I will have to see to those.

What you say about using [php]cmbSurname.SelectedValue.ToString()[/php] sounds good but it returning nothing.

I think I am filling the combobox wrongly.

I am using[php]Private Sub ComboBoxSetup()

Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source _
= " & Application.StartupPath & "\myDB.mdb"

Dim myConnectionString As New OleDbConnection(strDSN)

Dim sql As String = "SELECT EmployeeID, Surname FROM EmployeeDetails WHERE _
CompanyID=" & myForms.UserControl1.CurrentCompanyID & " ORDER BY Surname"

Dim myDataAdapter As New OleDbDataAdapter(sql, myConnectionString)
Dim myDataSet As New DataSet

myDataAdapter.Fill(myDataSet, "Employees")

Dim myTable As DataTable

For Each myRow In myTable.Rows
cmbSurname.ValueMember = myRow("EmployeeID")
cmbSurname.DisplayMember = myRow("Surname")
cmbSurname.Items.Add(myRow("Surname"))
Next

End Sub[/php]
The combobox is filling up from [php]cmbSurname.Items.Add(myRow("Surname"))[/php] but on each loop the ValueMember and DisplayMember are overwriting themselves.

In the cmbSurname_SelectedItemChanged(...) handler

cmbSurname.SelectedValue shows nothing and
cmbSurname.SelectedValue.ToString() throws an error

...Object Reference not set  to an Instance of an Object

I have read in tutorials and bonkey mentioned it above that something like
[php]Dim myTable As DataTable

myTable = myDataSet.Tables("Employees")
cmbSurname.DataSource = myTable

cmbSurname.ValueMember = "EmployeeId"
cmbSurname.DisplayMember = "Surname"[/php] should be used it fill the combobox.

In that case I try
[php]Private Sub ComboBoxSetup()

Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = _
" & Application.StartupPath & "\myDB.mdb"

Dim myConnectionString As New OleDbConnection(strDSN)

Dim sql As String = "SELECT EmployeeID, Surname FROM EmployeeDetails WHERE _
CompanyID=" & myForms.UserControl1.CurrentCompanyID & " ORDER BY Surname"

Dim myDataAdapter As New OleDbDataAdapter(sql, myConnectionString)
Dim myDataSet As New DataSet

myDataAdapter.Fill(myDataSet, "Employees")

Dim myTable As DataTable

myTable = myDataSet.Tables("Employees")
cmbSurname.DataSource = myTable

cmbSurname.ValueMember = "EmployeeId"
cmbSurname.DisplayMember = "Surname"

End Sub[/php]but the combobox isn't even filling up.

Can anyone see what is wrong with that code? It don't work. Is it something DataSet/DataTable/"Employees" ?

bonkey

AHA!!!

I've spotted something :

[php]
For Each myRow In myTable.Rows
cmbSurname.ValueMember = myRow("EmployeeID")
cmbSurname.DisplayMember = myRow("Surname")
cmbSurname.Items.Add(myRow("Surname"))
Next
[/php]

This is wrong.

What it should read is :

[php]
cmbSurname.ValueMember = "EmployeeID"
cmbSurname.DisplayMember = "Surname"
For Each myRow In myTable.Rows
cmbSurname.Items.Add(myRow("Surname"))
Next
[/php]

The ValueMember and DisplayMember fields need to be set to the *name* of the column, not teh value in the column. You only do this once, then add all of your columns.

In what you had, if you had an employeeID of 100, and a Surname of Smith, then when you selected an item, VB would look for a column called 100 to get the value for the SelectedValue, and a column called Smith to get the value for the DisplayValue.

Naturally, because your recordset doesn't have a column called 100 and a column called Smith, that will return blanks.

Sorry I didn't spot it sooner...lack of caffeine or something.

I have no idea why my suggested approach isn't working, but if the above modification to your own code works, then go with it...

jc