why is oledb dataadapter not fetching any data from access database in vb.net?

Question

I am stuck with a very peculiar problem. I am working in asp .net vb with ms access 2007

Inorder to fetch data I am using the following code snippet

        connection = utility.GetConnection()
        Dim command As New OleDbCommand(sQuery, connection)
        Dim adapter As New OleDbDataAdapter(sQuery, connection)
        Dim dt As New DataTable()
        adapter.SelectCommand = command
        adapter.Fill(dt)

When I use the query without the where clause it works. i.e. IT fetches all the rows and fills the data table. But when the sQuery has the where clause with it dt.Rows.Count always gives 0. i.e. no data is fetched from database. I saying this is a peculier problem because while debigging I copied the sQuery with where clause and ran in the ms access and there it is returning the data. I don understand what am I missing. I am showing the queires that is generated for sQuery

    SELECT * FROM ORDER_VIEW WHERE 1 = 1 (I don have any problem with that)

But when sQuery has

  SELECT * FROM ORDER_VIEW WHERE 1 = 1 AND ITEM_ID_NO LIKE '011*' 

It fetches rows in the ms access but, adapter.Fill(dt) line does not fill datatable with any row

I am stuck with this problem, for any suggesion and solution thanks in advance.

Answer 1

When using the OleDB provider, try using '011%' instead of '011*'.

I believe the OleDB engine uses the % for a wild card character where the Access engine uses the * character. It comes down to the provider.