Datareader problem in ADO.NET

[briancps]

Hi
i am trying to use the datareader with an Access 2002 DB. The user can enter a filter (say b in this case) which will select all records with a b or B in the name. here is my SQL Statement

SELECT * FROM(TableTest) WHERE (((TableTest.Name) Like '*b*')) ORDER BY TableTest.Name

Works fine in access 2002 (returns 2 records in my test database) as a query - works fine in VB6 using DAO or ADO but I
get zero records returned when using the Datareader in VB.NET

simple code for the Datarefresh shown below. I've actually hardcoded the 'b' in case there was a problem with the way I'm putting it together - but still zero records.

Can anyone help

Thanks Brian H


Private Sub DataRefresh()
'refresh the list box based on the current search filter

Dim sSql As String
Dim cn As New OleDb.OleDbConnection(dbcConnection)
cn.Open()

'set sql string to reflect search criteria
If txtSearch.Text = "" Then
sSql = "SELECT * FROM TableTest ORDER BY Name"
Else
'sSql = "SELECT * FROM TableTest WHERE Name LIKE '*" & txtSearch.Text & "*' ORDER BY Name"
sSql = "SELECT * FROM(TableTest) WHERE (((TableTest.Name) Like '*b*')) ORDER BY TableTest.Name"
End If

'open the ADO.NET Datareader
Dim cmd As New OleDb.OleDbCommand(sSql, cn)
Dim drd As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'fill flexgrid here
lstSearch.Rows = 1
lstSearch.FormatString = "ID|Name"
lstSearch.set_ColWidth(0, 960)
lstSearch.set_ColWidth(1, 2700)

Do While drd.Read
lstSearch.AddItem(drd!ID & vbTab & drd!Name)
Loop
drd.Close()

End Sub

[briancps]

help anyone !

[Edneeis]

Not sure, but if the data is alread in a dataset then you can use the Select method to get a subset of the data (filter it).

[briancps]

Apparently the * around the Like field is ANSI 89 sql and now it needs a % sign in ADO.NET which is the latest standard. Thanks anyway

BH