VB.NET

Results from Select statement differ in VB.NET and Access

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2006

Posts: 3

Reputation: pclpossum is an unknown quantity at this point

Solved Threads: 0

pclpossum

Offline

Newbie Poster

Results from Select statement differ in VB.NET and Access

Sep 23rd, 2006

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:

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
        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*'));"
        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

•

Join Date: Sep 2006

Posts: 1

Reputation: sahra is an unknown quantity at this point

Solved Threads: 0

sahra

Offline

Newbie Poster

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

Sep 23rd, 2006

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

ystem 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

FP=False"

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

Last edited by sahra; Sep 23rd, 2006 at 9:05 am.

•

Join Date: Sep 2006

Posts: 3

Reputation: pclpossum is an unknown quantity at this point

Solved Threads: 0

pclpossum

Offline

Newbie Poster

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

Oct 1st, 2006

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

Peta Watson

•

Join Date: Sep 2006

Posts: 90

Reputation: Inanna is on a distinguished road

Solved Threads: 6

Inanna

Offline

Junior Poster in Training

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

Oct 1st, 2006

Uh, you didn't open the connection.

Help with Code Tags

(Toggle Plain Text)

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?

•

Join Date: Sep 2006

Posts: 3

Reputation: pclpossum is an unknown quantity at this point

Solved Threads: 0

pclpossum

Offline

Newbie Poster

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

Oct 1st, 2006

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)

Peta Watson