Ad:

VB.NET Discussion Thread
Unsolved
Views: 13638

Sep 23rd, 2006

Results from Select statement differ in VB.NET and Access

Expand Post »

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:

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

Similar Threads

Question about SELECT statement in MySQL
Getting current value in Select Statement in MS SQL
cfselect issue trying to get results from a sql select statement in ColdFusion
Program Problem with a select statement to access Data base in C

pclpossum

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Sep 2006

Permalink

Sep 23rd, 2006

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

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.

sahra

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Sep 2006

Permalink

Oct 1st, 2006

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

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

pclpossum

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Sep 2006

Permalink

Oct 1st, 2006

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

Uh, you didn't open the connection.

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

Inanna

Reputation Points: 53

Solved Threads: 6

Junior Poster in Training

Offline

90 posts
since Sep 2006

Permalink

Oct 1st, 2006

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

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)

pclpossum

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Sep 2006

Permalink

Mar 2nd, 2008

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

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

steilx

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Mar 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Field token out of range

Next Thread in VB.NET Forum Timeline: For the love of GOD! Someone please help me!!!

DaniWeb Message

Cancel Changes

User Name
Password