Populating Listbox

[rickb]

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 isn't poplulated. Below is the code I have for the sub (LoadList). Could someone please tell me what I'm missing or doing wrong? I have followed the advice of other posters who have answered similar questions, but I'm 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 doesn't fill.

 

Thanks for any help.

[jfackler]

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

 

 

[rickb]

You're right, thanks for the catch.

 

However, I corrected the Dataset name in the "Fill" statement, but that didn't solve the problem; the listbox still will not fill.

[PlausiblyDamp]

Is this a windows or web app?

[rickb]

Windows

[jfackler]

I think your problem lies within your sql statement &/or your txtPick.txt.

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, I'd put the portion in paranthesis (trim( LastName)" & ", " & "trim(FirstName)).

Jon

[jfackler]

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

[rickb]

Works perfectly, jfackler!

 

Thank you very much for the solution. I'm 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; it's greatly appreciated.