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

Re: Results from Select statement differ in VB.NET and Access 80 80

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:\...

Re: Results from Select statement differ in VB.NET and Access 80 80

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)?

Re: Results from Select statement differ in VB.NET and Access 80 80

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?

Re: Results from Select statement differ in VB.NET and Access 80 80

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)

Re: Results from Select statement differ in VB.NET and Access 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.