Please review the code below, I'm not able to determine why I'm receiving the error message: " Conversion from DBNull to type String is valid. Any help is appreciated.
Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
If txtSearch.Text = "" Then
lblAdvice.Text = "Enter characters from the last name to search for a record. Click the New button to add a new account record."
Return
End If
Dim SQLStmt As String
Dim DisplayedInList As Integer
Dim UsersCnxn As New OleDbConnection( _
"Provider=Microsoft.JET.OLEDB.4.0;" _
& "Data Source=DB.mdb")
UsersCnxn.Open()
If UsersCnxn.State <> ConnectionState.Open Then
MsgBox("Unable to connect to the database...", MsgBoxStyle.Critical)
End
End If
SQLStmt = String.Format( _
"SELECT (LastName + ', ' + FirstName) as AccountName, Id from BE1 WHERE LastName like '{0}%' ORDER BY LastName, FirstName", _
txtSearch.Text)
'Debug.Write("SQLStmt=" & SQLStmt)
Dim cmdSearch As New OleDbCommand(SQLStmt, UsersCnxn)
Dim Matches As OleDbDataReader = cmdSearch.ExecuteReader
If Not Matches.Read Then
txtSearch.Focus()
lblAdvice.Text = "No matches found for '" & txtSearch.Text & "'..."
Else
'Put MatchingRecords in an ArrayList
Dim MatchingRecords As New ArrayList
DisplayedInList = 0
Do
MatchingRecords.Add(New ListItem(Matches.Item("AccountName"), Str(Matches.Item("Id"))))
DisplayedInList += 1
Loop Until (DisplayedInList > 50) Or Not Matches.Read
'If DisplayedInList > 50 And Matches.Read Then
' 'This needs handling, intention is to let them keep scrolling thru a heap of dupes only if they want to...
' 'MatchingRecords.Add(New MatchingRecord("(" & Matches.Item("AccountName") & ")", Matches.Item("AccountId")))
'End If
'Setup lbMatches to use MatchingRecords for its DataSource
lbMatches.DisplayMember = "DisplayValue"
lbMatches.ValueMember = "IdValue"
lbMatches.DataSource = MatchingRecords
lblAdvice.Text = "Click on the name for the desired account in the list, or try another search..."
lbMatches.Focus()
End If
UsersCnxn.Close()
txtSearch.Focus()
End Sub