Get Bound Listbox Value

[Ggilmann]

Hello everyone,

This should be simple, at least it was simple when I was working with Access VBA...

I've got a listbox that is bound to an access 2007 table. This table contains 2 columns, ID and Name. I've set my listbox to show the Name field but I want to retrieve the ID when the user clicks on an item.

I've got two questions :

1) What is the best way to obtain the value of the selecteditem? Many ways that I thought would work ends up returning System.DataRowView or something similar

2) How do I get the ID field value instead of the name field value? Do I have to set the listbox as having multiple columns or something?

Here's my code for now, if it can help :
_________________________________________________________________________

Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim strFilter AsString
Dim i AsInteger

i = ListBox1.SelectedIndex

cmd = New OleDbCommand("SELECT ID_Unite_Construite FROM Unite_Construite WHERE ID_Unite_Construite IN (SELECT ID_Unite_construite FROM Decoupage_Construite WHERE ID_Unite_Decoupage IN (SELECT ID_Unite_Decoupage FROM Unite_de_decoupage_sur_Terrain WHERE ID_Terrain IN (SELECT ID_Terrain FROM Terrain_Propriete WHERE ID_Propriete = " + ListBox1.GetItemText(ListBox1.Items.Item(i)) + ")))", cn)

dr = cmd.ExecuteReader
strFilter = ""

While dr.Read()
strFilter = strFilter + "ID_Unite_Construite = " + dr(0) + " OR "
EndWhile

strFilter = Mid(strFilter, strFilter.Length - 4)
Me.Unite_construiteBindingSource.Filter = strFilter
__________________________________________________________________________

As you can see, the idea is to run a query based on the listbox value. This query gives me a number of value from other tables that will allow me to filter a bindingsource for my form.

Note that the code is far from finished, so don't look at it in too much details.

Thanks,

Ggilmann

 

[Ggilmann]

Ok well, I figured out a solution. Not sure if it's the "right" way though, you guys can tell me I guess. Here it is :

Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim drv As DataRowView
Dim strFilter As String

drv = ListBox1.SelectedItem

cmd = New OleDbCommand("SELECT ID_Unite_Construite FROM Unite_Construite WHERE ID_Unite_Construite IN (SELECT ID_Unite_construite FROM Decoupage_Construite WHERE ID_Unite_Decoupage IN (SELECT ID_Unite_Decoupage FROM Unite_de_decoupage_sur_Terrain WHERE ID_Terrain IN (SELECT ID_Terrain FROM Terrain_Propriete WHERE ID_Propriete = " + drv.Row.Item(0) + ")))", cn)

dr = cmd.ExecuteReader
strFilter = ""

While dr.Read()
strFilter = strFilter + "ID_Unite_Construite = " + dr(0) + " OR "
End While

strFilter = Mid(strFilter, strFilter.Length - 4)
Me.Unite_construiteBindingSource.Filter = strFilter

So there, I havent tested ot extensively but I think it'll work.

 

[jmcilhinney]

When you bind the ListBox, do it like this:

myBindingSource.DataSource = myDataTable

With myListBox
    .DisplayMember = "DisplayColumnName"
    .ValueMember = "IDColumnName"
    .DataSource = myBindingSource
End With

You then get the selected ID from the ListBox's SelectedValue property.

I'm also not quite sure what you're trying to do with that filter.

 

[Ggilmann]

I'm trying to filter the records of my form according to what's selected in that listbox. I have to say I'm not sure if that's the right way to proceed...

 

[jmcilhinney]

It's definitely not the right way. There are basically two options and you need to choose one before implementing it:

1. Get all the data in one go at the beginning with a single data access. Bind all the data and then simply set the Filter of the BindingSource based on the selection.

2. Don't get any data to begin with. When the user makes a selection, get all the data that matches that selection and bind it. There's no local filtering at all.

 

[Ggilmann]

I thought that's what I was already doing, filtering the records through the binding source. Isn't this what the following line does, considering my form's controls are bound to the table named "Unite_construite"?

Me.Unite_construiteBindingSource.Filter = strFilter

To give a bit more context, my DataSet is an external Access 2007 database that I've added to my project. What's the best approach in this case? My database is not, and I don't think it ever will be, of an overwhelming size.

Thanks for your help!

Ggilmann

 

[jmcilhinney]

You are setting the BindingSource Filter but you are performing a query first and using a bit of a dodgy filter. If you're going to perform that query then you should just use it to get the data and bind that data without a filter. If you want to use a filter then get all the data upfront and then use the Filter to exclude those records you don't want to see. For instance, let's say that you have a Parent (ParentID, ParentName) table and a Child (ChildID, ParentID, ChildName) table. You could bind all the child data to a ListBox and then the parent data to a ComboBox:

parentBindingSource.DataSource = parentTable

With parentComboBox
    .DisplayMember = "ParentName"
    .ValueMember = "ParentID"
    .DataSource = parentBindingSource
End With

childBindingSource.DataSource = childTable

With childListBox
    .DisplayMember = "ChildName"
    .ValueMember = "ChildID"
    .DataSource = childBindingSource
End With

When the user selects a parent in the ComboBox, use that to filter the children:

childBindingSource.Filter = "ParentID = " & parentComboBox.SelectedValue.ToString()

 

[Ggilmann]

Yeah I see what you mean now. So I guess the best option would be to set my bindinsource's datasource to the results of the query. Question now is, how do I create a datasource from a query to assign to the bindingsource's datasource? In most of the examples I find, the datasource is always set to a table, not a query.

Thanks,

Ggilmann

 

[jmcilhinney]

If you have a typed DataSet, you can add your own TableAdapter to it in the designer and use whatever SQL code you like. You also don;t have to use a typed DataSet. You can use a DataAdapter or DataReader to populate a DataTable at run time and bind that to the BindingSource. That also allows you to select the columns at run time, if you need to.

 

[Ggilmann]

I managed to get it to work during the day today. I forgot to update this thread. Sorry about that.

It took me an hour to get it to work this morning. I was really hoping for a quick answer on your end. However, I realized that you're from Australia so that means roughly a 12 hours difference!

Anyhow, many thanks for all your help, I will most definitely be using this forum more and more since my project is wayyyy more complicated than I had expected at first.

Ggilmann

 

[jmcilhinney]

my project is wayyyy more complicated than I had expected at first.

Aren't they all?