Datareader problem in ADO.NET
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
