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

Did you upgrade this code from an earlier version?

Yes, originally i was using Access 2007, now I'm using 2003.

The error message you get is "Conversion from DBNull to type String is not valid". It means that you have a null value in LastName and/or FirstName fields. I assume ID field can't contain null values.

You can drop nulls in the query by changing the select statement: ...WHERE (LastName IS NOT NULL AND FirstName IS NOT NULL) AND LastName like '{0}%' ORDER BY... Other solution is to check what the SQLReader returns:

If Matches.Item("AccountName") IsNot DBNull.Value Then
  ' Use value
End If
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.