Help with Visual Studio 2008

[Birdie010]

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

[jbennet]

Did you upgrade this code from an earlier version?

[Birdie010]

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

[Teme64]

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