Results from Select statement differ in VB.NET and Access

[pclpossum]

Hello!

I have an SQL query designed to search a string field and return results based on input from the user.

If I use VB.NET to interrogate the Access Db using the following SQL:

"Select *FROM TRAINING WHERE ((Trainer Like '*Rob*'));"

I get no results.

If I perform this same query directly in the DB (eg in Access iteslf) I get a number of hits (which are correct).

The code I'm using to create the connection etc is:

Dim adapter As OleDb.OleDbDataAdapter
        Dim Connection As OleDb.OleDbConnection

        searchResults = New DataSet

        Connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Trainers.mdb")
        adapter = New OleDb.OleDbDataAdapter(searchText, Connection)
        adapter.Fill(searchResults)

        Connection.Close()

' Where searchText  = 
' "Select * FROM  TRAINING WHERE (( Trainer Like '*Rob*'));"

(searchResults is defined as a DataSet outside of this procedure)

This code and the way I'm creating the query works for every other search I perform.

I'm not sure if its the version of VB Express/MS Access I'm using, or if I'm missing something simple.

Any help would be greatfully recieved.

Cheers

[sahra]

Dear

use this code to connect to the Access database:
Private Const CONNSTR As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
"User ID=Admin;" & _
"Data Source=Trainers.mdb;" & _
"Mode=Share Deny None;Extended Properties="""";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB: Database Password="""";" & _
"Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB: Database Locking Mode=1;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Jet OLEDB:New Database Password="""";" & _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDB: Don't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False"

in the data source you have to enter the path such as C:\...

[pclpossum]

Thank you, but the results haven't changed. I've tried putting the db in different places, but that brings no joy either.

Do I need to have something set to allow the connection to handle complex queries such as the "like" query, or a query drawing results from multiple tables (which I'm also having trouble with)?

[Inanna]

Uh, you didn't open the connection.

Dim adapter As OleDbDataAdapter
Dim Connection As OleDbConnection

searchResults = New DataSet

Connection = New OleDbConnection(connectionString)

Connection.Open();

adapter = New OleDbDataAdapter(searchText, Connection)
adapter.Fill(searchResults)

Connection.Close()

That should have thrown an exception. Are you catching and ignoring exceptions in a try block somewhere higher up?

[pclpossum]

I don't catch an execption for that code as the connection doesn't have to be explicitly opened in this situation.

Having the connection.Open() in there doesn't change the results; regardless if it goes before or after the adapter = New OleDb.OleDbDataAdapter(query, Connection)

[steilx]

Try using % instead of * in the like query in the vb code .
eg:
"Select * FROM TRAINING WHERE ((Trainer Like '%Rob%'));"