CODEWITHSUNDEEP
vb.netoledbdataadapter
Aranxo
Aranxo

Reputation: 1183

OleDbDataAdapter doesn't like LIKE conditions

I have some code to find the first DataRow in some DB Table for a matching WHERE condition and an ORDER BY sort order which looks like:

Public Function GetRow(SQL As String) As DataRow
    If Not OpenConnection() Then Return Nothing

    Dim DT As New DataTable With {
        .Locale = Globalization.CultureInfo.InvariantCulture
    }

    Dim Cmd As New OleDbCommand(SQL, Connection, Transaction)

    Try
        Using Adapter As New OleDbDataAdapter With {.SelectCommand = Cmd}
            Adapter.Fill(DT)
        End Using
    Catch Ex As OleDbException
        MsgBox(Ex.Message)
    Finally
        CloseConnection()
    End Try

    If DT.Rows.Count = 0 Then
        Return Nothing
    Else
        Return DT.Rows(0)
    End If
End Function

Assume this is part of a class, so the functions OpenConnection and CloseConnection do what their names are promising. This also applies to the Variables Connection and Transaction. The Catch part is only temporarily in it to test if there is an exception. But there wasn't.

Then I have an Access Database with a Table named 'Players' and some guys in it, especially a guy named 'Fred Bauer', which fills the colums Name and Forename like Name:=Bauer, Forename:=Fred (just as example). Now I like to search for the first guy which name begins with B, so the SQL looks like:

SELECT * FROM Players WHERE [Name] LIKE 'B*' ORDER BY [Name]

And the code finds nothing. DT.Rows.Count will be 0. Hmmmmm?! I know that the guy is in there, the SQL should have found him. To test the correctness of the code I tried the same SQL Code directly in Access as a view and it works fine. It returns a view with Fred Bauer as one row.

Then I tried to make the counter test and find the guy directly with my code and with:

SELECT * FROM Players WHERE [Name] = 'Bauer' ORDER BY [Name]

This works fine. The correct DataRow is found and given back. WHAT?!!

It seems like the OleDbDataAdapter doesn't like LIKE conditions. Maybe someone can help me out and get it work also for LIKE conditions? Or if you have any suggestions to do it in a complete different way, it's welcome.

Upvotes: 1

Views: 82

Answers (1)

Mary
Mary

Reputation: 15081

You shouldn't be using an OpenConnection and CloseConnection methods. Connections and other database objects should be local to the method where they are used so they can be disposed. There is nothing in this code that requires a transaction or DataAdapter.

As mentioned in comments use % for wildcard outside of Access.

Public Function GetRow() As DataRow
    Dim SQL = "SELECT * FROM Players WHERE [Name] LIKE 'B%' ORDER BY [Name]"
    Dim DT As New DataTable With {.Locale = Globalization.CultureInfo.InvariantCulture}
    Try
        Using con As New OleDbConnection("Your connection string"),
                    Cmd As New OleDbCommand(SQL, con)
            con.Open()
            DT.Load(Cmd.ExecuteReader)
        End Using 'Closes and disposes the connection and command
    Catch Ex As OleDbException
        MsgBox(Ex.Message)
    End Try
    If DT.Rows.Count = 0 Then
        Return Nothing
    Else
        Return DT.Rows(0)
    End If
End Function

Upvotes: 1

Related Questions