Multi column databinding to a list box 3

[Sech]

Hey all,

I'm trying to link multiple columns from a table based in SQL Server to a list box on one of my Visual Basic 2005 forms. I've set up the datatable correctly, and I can obtain individual values from it using datarow, however I wish to populate a list box with all the records from both its fields. When I try to set the lists datasource to the datatable, it displays System.Data.DataRowView in the list box for however many records there are. Can anyone please help? And yes don't be cynical, I do not have massive knowledge on VB.Net, but the book I've read through doesn't go into enough detail on binding, and I can't find the answer elsewhere on the net. If someone could please let me know the commands to use, or where i can find them I would be most grateful.

 

[Keyth]

I think without checking.... but forgive me if I am wrong .... although the listbox allows the display of multiple columns it is for displaying single column datasource.

You might be better using a datagridview, or loop through your dataset to join the two source columns and display them as a single column in the listbox.

 

[Sech]

Ok let's say I just want to set the list to a single column, I've got the the table from SQL Server linked to a datatable, how do I get the Name field from the table to display in the list box on screen?

 

[Keyth]

Try this Sech

With Me.ListBox1
   .ValueMember = "Name"  'The name of the column
   .DataSource = dt 'dt = your datatable
   '.DataSource = ds.tables(0) 'datatable index 0 in dataset
End With

 

[TipGiver]

Keyth,

- The multiple columns to a databound listbox works perfectly.

- Tip: Instead of the '.ValueMember', use the '.DisplayMember'. So you can have for the '.ValueMember' something unique like the ID. Then, for a selected item in the listbox, you can get its ID by writting 'Me.Listbox1.SelectedValue'

With Me.ListBox1
   .DisplayMember = "Name"  'The name of the column to display
   .ValueMember = "NameID"  'The ID of the table
   .DataSource = dt 'dt = your datatable
   '.DataSource = ds.tables(0) 'datatable index 0 in dataset
End With

 

[Sech]

To Keyth, thankyou for your very useful posts! However I am still currently getting the same error - it says System.Data.DataRowView in the list box instead of displaying the values. The code I am using is as follows:

dtCustomers = New DataTable
sqlda.Fill(dtCustomers)
Me.lstCustomers.ValueMember = "CustomerName"
Me.lstCustomers.DataSource = dtCustomers

Do I need to set up a dataset on the form before it will work? I want to do everything in coding rather than using wizards or automatic set ups.

 

[Sech]

Following on from my previous post Keyth, the code works when I use "Select * From tblCustomers" as the datatable, but if I use a query view result set instead "Select * From qryCustomers" it brings back the error. Do I need to use different code for dealing with a query? I thought data tables stored the results of tables, queries or stored procedures?

 

[Sech]

Got it working at last, thanks for your help Keyth and TipGiver. Do you know how to display multiple columns of visible data in a list box? Say for example Name and Date Of Birth shown, and another hidden ID field?

 

[TipGiver]

Glad you got it.
I just saw your replies.

Multicolumn means to show many columns of the same collection. For this you want, use a datagrid, or *grid controls


__

 

[TipGiver]

If you are using the 2.0 framework... you have some good options, like the DataGridView and the ListView, which i find very very nice!

 

[spizotfl]

I know it is bad mojo to jump in with a question in the middle of a thread, but I was wondering if you combine two columns from a dataset to add to a listbox, if it is possible to use some sort of formatting to evenly space the columns?

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[Keyth]

spizotfl: Without having a preset length for each column it would be a difficult/rough approach. As I said earlier its best to stick to a grid type where you can define the columns and have more control over the formatting regardless of the cell content or length.

Sech: I am also glad you have sorted it

Following on from my previous post Keyth, the code works when I use "Select * From tblCustomers" as the datatable, but if I use a query view result set instead "Select * From qryCustomers" it brings back the error.

As long as you bind to the column names or aliases as they appear in your datatable, I dont think you will have a problem. If you do have a problem, in debug mode loop through the datatable column names to check them against your bindings.

 

[TipGiver]

I was wondering if you combine two columns from a dataset to add to a listbox

> Yes you can. You can do it from your query string (SELECT FirstName + " " LastName AS Name , ..) or you can do it by joining the columns of the table (which is in the dataSet).

if it is possible to use some sort of formatting to evenly space the columns?

> Yes you can do that too. Note that a listbox does not have columns as a grid has. The multicolumn usage is to see more items at a time and not having to scroll down.

> To format it as you think, use a font that has fixed char space... e.g "Courier New". Have a look at the string PadLeft and PadRight properties. Note again that you will not have the option to sort by "column". There is a way to do that, by getting the cursor's position - and you will be able to know which "column" the user points.
But the above way is not the best practice as there is the grid for that situation.

 

[spizotfl]

Thank you both. I am coming to VB 2005 from Access and I have gotten used to the Access way of handling list and combo boxes. Must learn the new way, but this thread has been very helpful.

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[TipGiver]

Access way" ? I guess that you mean the gui within the Access (vba).