Populating Listbox

R

rickb

Member
Joined
Dec 31, 2002
Messages
17
Location
St. Louis, Missouri
I have a button whose text property is passed to a textbox, txtpick. The value of the textbox is used as the parameter in a SQL query, which in turn is supposed to fill a listbox. When I click on, for example, the "A" button, the txtpick textbox is filled, but the listbox isnt poplulated. Below is the code I have for the sub (LoadList). Could someone please tell me what Im missing or doing wrong? I have followed the advice of other posters who have answered similar questions, but Im having no luck.

Public Sub LoadList()
oledbconn.Open()

Dim dsUserList As New DataSet()
Dim daUserList As New OleDbDataAdapter("SELECT UserProfile, trim( LastName)" & ", " & "trim(FirstName) AS FullName,LastName,FirstName,LOC,Building,DeviceID FROM tblAS400Users WHERE LastName LIKE " & Me.txtPick.Text & "% ORDER BY LastName,FirstName", oledbconn)

Try
daUserList.Fill(dsAS400, "tblAS400USers")
With Me.lstUsers
.DataSource = dsUserList.Tables("tblAS400Users")
.DisplayMember = "FullName"
.ValueMember = "UserProfile"
End With

Catch ex As Exception
MsgBox(ex.ToString)
Exit Sub
End Try

Enable the Delete and Edit Button
cmdDelete.Enabled = True
cmdEdit.Enabled = True

oledbconn.Close()
End Sub

Here is the code for the "A" button:

Private Sub cmdA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdA.Click
Me.txtPick.Text = cmdA.Text
LoadList()
End Sub

I do not receive an error of any type from the "Try-Catch" statement; the listbox just doesnt fill.

Thanks for any help.
 
Originally posted by rickb
Public Sub LoadList()
oledbconn.Open()

Dim dsUserList As New DataSet()
Dim daUserList As New OleDbDataAdapter("SELECT UserProfile, trim( LastName)" & ", " & "trim(FirstName) AS FullName,LastName,FirstName,LOC,Building,DeviceID FROM tblAS400Users WHERE LastName LIKE " & Me.txtPick.Text & "% ORDER BY LastName,FirstName", oledbconn)

Try
daUserList.Fill(dsAS400, "tblAS400USers") should this be dsUserList?
With Me.lstUsers
.DataSource = dsUserList.Tables("tblAS400Users")
.DisplayMember = "FullName"
.ValueMember = "UserProfile"
End With

Catch ex As Exception
MsgBox(ex.ToString)
Exit Sub
End Try

Enable the Delete and Edit Button
cmdDelete.Enabled = True
cmdEdit.Enabled = True

oledbconn.Close()
End Sub
Click to expand...
 
Youre right, thanks for the catch.

However, I corrected the Dataset name in the "Fill" statement, but that didnt solve the problem; the listbox still will not fill.
 
I think your problem lies within your sql statement &/or your txtPick.txt.
Code: 
Dim daUserList As New OleDbDataAdapter("SELECT UserProfile, trim( LastName)" & ", " & "trim(FirstName) AS FullName,LastName,FirstName,LOC,Building,DeviceID FROM tblAS400Users WHERE LastName LIKE " & Me.txtPick.Text & "% ORDER BY LastName,FirstName", oledbconn)
Your where statement looks for LastName like. Is the textbox actually receiving the full name or just the last name? Also, Id put the portion in paranthesis (trim( LastName)" & ", " & "trim(FirstName)).
Jon
 
Code: 
Public Sub LoadList()
oledbconn.Open() since your using a dataadapter this is 
unnecessary the da will open and close the connection as needed
Dim dsUserList As New DataSet()
simplify your select
Dim daUserList As New OleDbDataAdapter("SELECT UserProfile,LastName,FirstName,LOC,Building,DeviceID FROM tblAS400Users WHERE LastName LIKE " & Me.txtPick.Text & "% ORDER BY LastName,FirstName", oledbconn)

then, independent of the db, create your calculated column
Try
daUserList.Fill(dsUserList, "tblAS400Users")
create a new column
Dim dcName As System.Data.DataColumn
dcName = New System.Data.DataColumn("Name")
dcName.DataType = System.Type.GetType("System.String")
dcName.Expression = "FirstName +   + LastName"

Add the calculated column
me.dsUserList.Tables("tblAS400Users").Columns.Add(dcName)

Bind to the listbox
With Me.lstUsers
.DataSource = dsUserList.Tables("tblAS400Users")
.DisplayMember = "Name"
.ValueMember = "UserProfile"
End With

Catch ex As Exception
MsgBox(ex.ToString)
Exit Sub
End Try

Enable the Delete and Edit Button
cmdDelete.Enabled = True
cmdEdit.Enabled = True

oledbconn.Close()  as above, unnecessary
End Sub

Just some thoughts.

Jon
 
Works perfectly, jfackler!

Thank you very much for the solution. Im still learning VB.Net, so this was a valuable lesson. This whole procedure worked perfectly using the data adapter wizard, but I was trying to duplicate it using code only.

Once again, thanks for your time and effort; its greatly appreciated.
 
Back
Top