value member into listbox

[dennist]

I finally figured out what others were telling me and was
able to fill as list box with the following code:

Imports System.Data
Imports System.Data.OleDb


Public Class Form1


Inherits System.Windows.Forms.Form


#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents lstCustomers As
System.Windows.Forms.ListBox
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.lstCustomers = New System.Windows.Forms.ListBox
Me.SuspendLayout()
'
'lstCustomers
'
Me.lstCustomers.Location = New
System.Drawing.Point(8, 16)
Me.lstCustomers.Name = "lstCustomers"
Me.lstCustomers.Size = New System.Drawing.Size
(120, 147)
Me.lstCustomers.TabIndex = 0
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.lstCustomers)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region


Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=H:\Program Files\Microsoft Office\Office10
\Samples\Northwind.mdb;" ' & "Admin" & ""
strSQL = "SELECT CustomerID, CompanyName FROM
Customers"
Dim cn As New OleDbConnection(strConn)
cn.Open()

Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
While rdr.Read()
'lstCustomers.Items.Add(rdr(1))
'MsgBox(rdr("CustomerID") & " - " & rdr
("CompanyName"))
'Console.WriteLine(rdr("CustomerID") & " - "
& rdr("CompanyName"))
End While
rdr.Close()
cn.Close()

End Sub
End Class


The problem remaining is how do I get rdr(0) into the
valuemember. This would really make me feel I'm getting
somewhere.

dennist

 

[Carl Prothman [MVP]]

Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
While rdr.Read()
'lstCustomers.Items.Add(rdr(1))

Dennist,
Try the following:

lstCustomers.Items.Add(rdr.GetString(1))

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[dennist]

Thanks, carl,

that is superior coding in getting the first column into
the displaymember, but doesn't help me get the 0th column
into the valuemember.

dennist

 

[Carl Prothman [MVP]]

Thanks, carl,

that is superior coding in getting the first column into
the displaymember, but doesn't help me get the 0th column
into the value member.

Dennist,
Do you mean like this?
lstCustomers.Items.Add(rdr.GetString(0) & " - " & rdr.GetString(1))

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Stephen Muecke]

dennist

Using ValueMember/DisplayMember is only relevant when you filling the list
with objects that contain more that one property (eg a DataTable with
columns or an object with properties)

Create a simple class with 2 properties (say ID and Name)
When reading the data, create a new instance and set its properties and then
add this instance to the listbox

myListBox.ValueMember = "ID"
myListBox.DisplayMember = "Name"

Do While rdr.Read
Dim obj as New myObj
With obj
.ID = rdr.GetInt32(0) 'assumes its an integer!
.Name = rdr.GetString(1)
End With
myListBox.Items.Add(obj)
Loop

SelectedItem will return myObj
SelectedValue will return myObj.ID

Stephen

 

[dennis turner]

Thanks Stephen, you got me part way there. But...

Here are the changes in my code I made according to your suggestions:

Dim cls1 As New Class1

While rdr.Read()
With cls1
.CustomerID = rdr.GetString(0)
.CustomerName = rdr.GetString(1)
End With
lstCustomers.Items.Add(cls1)
End While
rdr.Close()
cn.Close()

End Sub

Private Sub lstCustomers_SelectedIndexChanged(ByVal sender As
Object, ByVal e As System.EventArgs) Handles
lstCustomers.SelectedIndexChanged
MsgBox("CustomerID = " & lstCustomers.SelectedValue)
End Sub
End Class

and here is the class

Public Class Class1
Private mCustomerID As String
Private mCustomerName As String

Public Property CustomerID() As String
Get
Return mCustomerID
End Get
Set(ByVal Value As String)
mCustomerID = Value
End Set
End Property

Public Property CustomerName() As String
Get
Return mCustomerName
End Get
Set(ByVal Value As String)
mCustomerName = Value
End Set
End Property

End Class

It works insofar as getting the names into the listbox, as before. But
I can't access the values, if any, and it looks like they are empty.








if the selectedindexchanged event handler is

Private Sub lstCustomers_SelectedIndexChanged(ByVal sender As
Object, ByVal e As System.EventArgs) Handles
lstCustomers.SelectedIndexChanged
MsgBox("CustomerID = " & lstCustomers.SelectedItem)
End Sub

I get this error message

An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'Class1' to type 'String' is not
valid.


if the event handler is:

Private Sub lstCustomers_SelectedIndexChanged(ByVal sender As
Object, ByVal e As System.EventArgs) Handles
lstCustomers.SelectedIndexChanged
MsgBox("CustomerID = " & lstCustomers.SelectedValue)
End Sub


Then the message box comes up saying CustomerID =

and no value. In this case, being the northwind database, the customer
id is also a string.

How do I find out if the value is really in there, and how do I access
it. Why would it be blank?

Perhaps you have another way altogether of getting - in simple code -
values into a list box from a table in a database. It was certainly
easy enough in ADO without contortions.

dennist

 

[dennist]

Stephen, you helped me get part way there, but..

Here are the changes in my code I made according to your
suggestions:

Dim cls1 As New Class1

While rdr.Read()
With cls1
.CustomerID = rdr.GetString(0)
.CustomerName = rdr.GetString(1)
End With
lstCustomers.Items.Add(cls1)
End While
rdr.Close()
cn.Close()

End Sub

Private Sub lstCustomers_SelectedIndexChanged(ByVal
sender As Object, ByVal e As System.EventArgs) Handles
lstCustomers.SelectedIndexChanged
MsgBox("CustomerID = " &
lstCustomers.SelectedValue)
End Sub
End Class

and here is the class

Public Class Class1
Private mCustomerID As String
Private mCustomerName As String

Public Property CustomerID() As String
Get
Return mCustomerID
End Get
Set(ByVal Value As String)
mCustomerID = Value
End Set
End Property

Public Property CustomerName() As String
Get
Return mCustomerName
End Get
Set(ByVal Value As String)
mCustomerName = Value
End Set
End Property

End Class

It works insofar as getting the names into the listbox,
as before. But I can't access the values, if any, and it
looks like they are empty.








if the selectedindexchanged event handler is

Private Sub lstCustomers_SelectedIndexChanged(ByVal
sender As Object, ByVal e As System.EventArgs) Handles
lstCustomers.SelectedIndexChanged
MsgBox("CustomerID = " &
lstCustomers.SelectedItem)
End Sub

I get this error message

An unhandled exception of
type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'Class1' to
type 'String' is not valid.


if the event handler is:

Private Sub lstCustomers_SelectedIndexChanged(ByVal
sender As Object, ByVal e As System.EventArgs) Handles
lstCustomers.SelectedIndexChanged
MsgBox("CustomerID = " &
lstCustomers.SelectedValue)
End Sub


Then the message box comes up saying CustomerID =

and no value. In this case, being the northwind
database, the customer id is also a string.

How do I find out if the value is really in there, and
how do I access it. Why would it be blank?

Perhaps you have another way altogether of getting - in
simple code - values into a list box from a table in a
database. It was certainly easy enough in ADO without
contortions.

dennist

 

[dennist]

No, that doesn't work. I want a way to get the
customerID - which is also a string, since this is the
northwind database, into the value member.

dennist

 

[Stephen Muecke]

Dennis,

The statement 'Dim cls1 as New Class1' should go beneath the 'While...'
statement (before With ...) so that you create a new instance of Class1
each time the DataReader returns a row

The InvalidCastException is caused because .SelectedItem returns a Class1
object (not a string). If you use SelectedItem, you need to use the
following code

Dim cust as Class1 = DirectCast(lstCustomers.SelectedItem, CLass1)
MsgBox cust.CustomerID

A few suggestions
Rename your class from Class1 to Customer and your properties from
CustomerID to ID and CustomerName to Name
? = Customer.Name is much easier to read and understand than ? =
Class1.CustomerName
In the Customer Class, add
Public Sub New(ByVal ID as String, ByVal name as String)
mCustomerID = ID
mCustonerName = name
End Sub
then you can use the following
While reader.read
With reader
lstCustomers.Items.Add(New Customer(.GetString(0), .GetString(1)))
End With
End While
reader.Close

Stephen

 

[Carl Prothman [MVP]]

No, that doesn't work. I want a way to get the
customerID - which is also a string, since this is the
northwind database, into the value member.

Oh, you mean the ListBox's ValueMember property.

You can only specify the contents of the ValueMember property in cases
where you **bind data** to the ListBox.

Plus for Windows Applications, you can't bind a DataReader to a ListBox!
You must an object that implements the IList interface, such as a DataSet
or an Array.

e.g.

' Create the connection and sql strings
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind40.mdb;"
Dim sqlString As String = "SELECT CustomerID, CompanyName FROM Customers"

' Create a new connection, command and data adapter
Dim oledbConn As OleDbConnection = New OleDbConnection(connString)
Dim oledbCmd As OleDbCommand = New OleDbCommand(sqlString, oledbConn)
Dim oledbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
oledbDataAdapter.SelectCommand = oledbCmd

' Fill the DataSet
Dim customerDataSet As DataSet = New DataSet
oledbDataAdapter.Fill(customerDataSet, "Customers")

' Bind the DataSet to the DataList
ListBox1.ValueMember = customerDataSet.Tables("Customers").Columns(0).ColumnName
ListBox1.DisplayMember = customerDataSet.Tables("Customers").Columns(1).ColumnName
ListBox1.DataSource = customerDataSet.Tables("Customers")

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Carl Prothman [MVP]]

Since then, I've managed to put most of the logic in a
module as a generic listbox filler. I have a global
connect string, and send to the module sub FillListBox
the listbox name by reference, the strSQL byval, and the
table name by val.
Is there any way I could overload the sub so it could
accept either a listbox or a combobox?

Sure, just create another sub with the same name, but have it
accept a combobox parameter.

With VB.NET, you'll need to the use the Overloads keyword.
http://msdn.microsoft.com/library/en-us/vblr7/html/vastmsub.asp

Then each sub will call your common data access code and fill
the corresponding control that was pass in.

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com