Combobox SelectedValueChanged ValueMember

[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

[mindloop]

I wonder: using your code does your combobox get filled anyway ?
this is how new() for dataset is described in msdn:

Public Sub New(String)
This member supports the .NET Framework infrastructure and is not intended to be used directly from your code.

i doubt your dataset is created correctley

you should first add new dataset to your project by right-clicking on your project in solution explorer>add new item
then design the dataset to look like your table, add an instance of the dataset to your form from the toolbox (you will find dataset on the "Data" tab),specify the dataset you created when asked,
and just then
instead of adding each row to the combobox use this code:

cmbSurname.datasource = mydataset.Employees 'Employees is the table name you specify in dataset in design mode
cmbSurname.ValueMember = cmbsurname.Employees.employeeID
cmbSurname.DisplayMember = cmbsurname.Employees.Surname
'note you can choose the datasource, valuemember, and displaymember in design mode for the combobox

dataset creation is far more complicated through code as far as i read in msdn.
you must specify valuetype , and other properies for each column of a datatable , add the columns to the datatable, then add the datatable to the dataset.

if i am wrong and your combobox gets filled with your values well , then i'm wrong. i just read about creating datasets in msdn and decided to create datasets through designer. i never tried creating them by code.

[chakotha]

Thanks for the reply - the combobox is getting filled alright but I'll try it again through designer anyway.

Once I am in cmb_SelectedIndexChanged(...) will cmbSurname.ValueMember be the EmployeeID of the selected employee?

[taxes]

Hi Mindloop,

"Public Sub New(String)
This member supports the .NET Framework infrastructure and is not intended to be used directly from your code."


This refers to the Sub New which you will find in the region of code generated by Windows at the beginning of each form i.e.

#Region " Windows Form Designer generated code "
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Windows Form Designer.
        InitializeComponent()
 
    End Sub

Chakotha has use the New dclaration correctly.

Don't know where in MSDN you found Sub New in relation to datasets????

Also, the method you describe for creating datasets is one way of doing it. Most people do use code as Chakotha has done.

[mindloop]

taxes: thanks for the intervention, i never bothered trying to create a dataset by code, as i said.
anyway i'll keep in mind that it is possible.
and if what you say it is true then this is beginning to sound more interesting to me, you mean the way chakotha did is the right way to create datasets ?
what about the valuetypes for each column, don't you have to specify them anymore ?
if so then this is really easyer then designer.
chakotha: that is correct. if you create the dataset by designer, if you do it by code, i think taxes could help you more then i can.

[chakotha]

Hi taxes

If I stick with the code as I have got that far, how can I get the selected employee's ID from within cmbSurname_SelectedIndexChanged(...) ?

cmbSurname.ValueMember and cmbSurname.DisplayMember give me the last employee into the ComboBox

[mindloop]

i checked with taxes' post and it works

so what you do wrong is setting valuemember and displaymemeber for each row
here is the right code
instead of :

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

use

cbsurname.datasource = mydataset.tables("Employees")
cbsurname.valuemember = "EmpoyeeId"
cbsurname.displaymember = "Surname"

[taxes]

Hi,

I did not deal with your main query as I just rushed back from a tennis match and only took a cursory look at the forum before lunch.


First of all, I personally would not use both a Dataset and a DataTable. I would fill the DataTable directly from the Data Adapter.

myDataAdapter.Fill(myTable, "Employees")

With regard to your problem, you cannot reference a value of -1
as
cmbSurname.SelectedIndex

The value -1 is returned when you try to reference an item in a ComboBox with no item selected.

If you want to go to the first entry in the ComboBox use

cmbSurname.SelectedIndex = 0

If you want to view the ValueMember of the Selected item, try

(I edited out this bit as your existing code should now work, once you abandon the -1 bit. You simply use the combo to select an item)

I am a beginner and I have only checked the foregoing once, so forgive me if it needs amending.

[Hole-In-One]

Just use something like this:

Dim i as integer=cmbSurName.SelectedIndex

Dim ID as integer

ID=YourDataTable.Tables(0).Rows(i)("EmployeeID")

MsgBox(ID.ToString())



Where "EmployeID" is the name of your ID column and assuming that the table is the first table in your database.

[chakotha]

YeeaaH! A combination of everything worked. Thanks to everyone.

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
    Dim myRow As DataRow
 
    myTable = myDataSet.Tables("Employees")
 
    For Each myRow In myTable.Rows
        cmbSurname.Items.Add(myRow("Surname"))
    Next
 
End Sub
 
Public Sub cmbSurname_SelectedIndexChanged(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 & "\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 i As Integer = cmbSurname.SelectedIndex
 
    Dim ID As Integer
 
    ID = myDataSet.Tables(0).Rows(i)("EmployeeID")
 
    MsgBox(ID.ToString())
 
End Sub

That gives the ID needed.

I will have to check out ValueMember etc. again and try and get it that way - and a bit more practice with DataAdapters, DataSets, DataTables etc. will clear the fog a bit.

[taxes]

Hi Chakotha,

It's too easy

Choose the required item in the combobox then;

Dim ID as Integer
      ID= cmbSurname.SelectedValue

OR

if you want to choose the item to view:

Dim iCount as Integer
       iCount=2
       cbmSurname.SelectedIndex=iCount
       Dim ID as Integer
      ID= cmbSurname.SelectedValue

[taxes]

Hi Mindloop,

"and if what you say it is true then this is beginning to sound more interesting to me, you mean the way chakotha did is the right way to create datasets ?


chakotha's coding is one way; your's is another. It is not that only one way is right, but if you make a habit of creating objects in code, you will learn coding practices more quickly.

His Dataset declaration and filling is perfectly OK but, as I said earlier, I do not know why he used a DataSet AND a DataTable. You have to use a DataSet if you are referencing data from more than one table, but you would normally use a DataTable when you are referencing only one table. In any case, you do not need both. Perhaps one of the Gurus' will comment on this if I am wrong, but I have tested this in a programme today.

[chakotha]

taxes, what you say about using

cmbSurname.SelectedValue 

sounds good but it is returning nothing.

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 think I am filling the combobox wrongly to start with.

I am using

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 

The combobox is filling up from

cmbSurname.Items.Add(myRow("Surname")) 

but on each loop the ValueMember and DisplayMember are overwriting themselves.

Hole-in-One's suggestion

Dim i As Integer = cmbSurname.SelectedIndex

Dim ID As Integer

ID = myDataSet.Tables(0).Rows(i)("EmployeeID") 

works and is returning the required ID and I am using it thanks Hole-in-One. But someone said that will be dependant on the sort-order of the dataset never changing, and the dataset remaining in existence etc. and I have read in tutorials and taxes mentioned it above that something like this should be used to fill the combobox:

Dim myTable As DataTable

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

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

In that case I try

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 

but the combobox isn't even filling up.

Can anyone see what is wrong with that code? It don't work.

[taxes]

Hi,
I won't get much time to look at this for a couple of days but one immediate point is that you cannot use reserved words as variable names. You are using SQL as a variable. Alter it to something else like mySQL.