populating a list box using a DataReader
I'm hoping some very kind person can help me stop pulling my hair out. I've changed my approach to populating a list box by using a DataReader.
It displays the first match briefly then displays "Authenticate_User.MatchingRecord" for each instance of a match.
Authenticate_User is the name of the Program. MatchingRecord is a class.
I'm ready to give up. I've posted the code if you have time, I would buy you a big dinner.
The Public Class Works. I just included it in case you need it.
Public Class MatchingRecord
Private cAccountName As String
Public ReadOnly Property AccountName() As String
Get
Return cAccountName
End Get
End Property
Private cAccountId As String
Public ReadOnly Property AccountId() As String
Get
Return cAccountId
End Get
End Property
Public Sub New(ByVal name As String, ByVal id As Integer)
cAccountName = name
cAccountId = id
End Sub
End Class - This works
Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
Dim SQLStmt As String
Dim DisplayedInList As Integer
Dim Cnxn As New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=CommonAlgorithms.mdb")
Cnxn.Open()
If Cnxn.State <> ConnectionState.Open Then
MsgBox("Unable to connect to the database...",
MsgBoxStyle.Critical)
End
End If (My SQL statement Works)
SQLStmt = String.Format( _
"SELECT (LastName + ', ' + FirstName) as AccountName, Id from Accounts WHERE LastName like '{0}%' ORDER BY LastName, FirstName", _
txtSearch.Text)
'Debug.Write("SQLStmt=" & SQLStmt)
Dim cmdSearch As New OleDbCommand(SQLStmt, Cnxn)
Dim Matches As OleDbDataReader = cmdSearch.ExecuteReader
Dim MatchingRecords As New ArrayList
DisplayedInList = 0
lbMatches.DisplayMember = "AccountName"
lbMatches.ValueMember = "AcctId"
Do
MatchingRecords.Add(New MatchingRecord(Matches.Item
("AccountName"), Matches.Item("AcctId")))
DisplayedInList += 1
Loop Until (DisplayedInList > 50) Or Not Matches.Read
lbMatches.DataSource = MatchingRecords
lbMatches.Focus()
End If
Cnxn.Close()
txtSearch.Focus()
End Sub
Thank you.
|