I'm using ADO .NET to connect to a Microsoft Access database, and
everything so far has been working in my program except one small
problem. I am constructing a query string to search for certain results
from a table, and it does not seem to return the correct results.


Dim dtResults As New DataTable("SearchResults")

Dim cmdSearchResults As New OleDb.OleDbCommand(queryString,
appointments.myConnection)
Dim daSearchResults As New OleDb.OleDbDataAdapter(cmdSearchResults)

daSearchResults.Fill(dtResults)

Debug.WriteLine(dtResults.Rows.Count)
Debug.WriteLine(queryString)

where queryString is the string I have constructed. It looks like this:

SELECT Appointments.AppointmentID, Appointments.AppointmentTime,
Buildings.Building, Appointments.RoomNumber, Appointments.Details FROM
(Appointments INNER JOIN Buildings ON
Appointments.BuildingID=Buildings.BuildingID) INNER JOIN Technicians ON
Appointments.TechnicianID=Technicians.TechnicianID WHERE 1 = 1 AND
Technicians.TechnicianName = "Matt" AND Appointments.Details LIKE "*test*";


When I copy this directly into a query in MS Access it returns the
correct records, however the data adapter in my code returns 0 results.
Whenever I remove the LIKE clause it seems that my queries do return
results back to the data table. Is there something buggy with using the
LIKE clause to send queries through ADO? Any ideas as to what the
problem is anyone?


-Matt

Not positive this is it, but you might try % as the wildcard instead of *,
and possibly drop the parentheses around the various joins, for a less
Access-ish syntax

"Matt Michael" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> I'm using ADO .NET to connect to a Microsoft Access database, and
> everything so far has been working in my program except one small problem.
> I am constructing a query string to search for certain results from a
> table, and it does not seem to return the correct results.
>
>
> Dim dtResults As New DataTable("SearchResults")
>
> Dim cmdSearchResults As New OleDb.OleDbCommand(queryString,
> appointments.myConnection)
> Dim daSearchResults As New OleDb.OleDbDataAdapter(cmdSearchResults)
>
> daSearchResults.Fill(dtResults)
>
> Debug.WriteLine(dtResults.Rows.Count)
> Debug.WriteLine(queryString)
>
> where queryString is the string I have constructed. It looks like this:
>
> SELECT Appointments.AppointmentID, Appointments.AppointmentTime,
> Buildings.Building, Appointments.RoomNumber, Appointments.Details FROM
> (Appointments INNER JOIN Buildings ON
> Appointments.BuildingID=Buildings.BuildingID) INNER JOIN Technicians ON
> Appointments.TechnicianID=Technicians.TechnicianID WHERE 1 = 1 AND
> Technicians.TechnicianName = "Matt" AND Appointments.Details LIKE
> "*test*";
>
>
> When I copy this directly into a query in MS Access it returns the correct
> records, however the data adapter in my code returns 0 results. Whenever I
> remove the LIKE clause it seems that my queries do return results back to
> the data table. Is there something buggy with using the LIKE clause to
> send queries through ADO? Any ideas as to what the problem is anyone?
>
>
> -Matt

I do not normally use Access, however is the txt you are looking for in
the like clause literally *test* or are the stars supposed to be
wildcards? If it is supposed to be 0 or any character before and after
test, try using the % instead of the * like this:

SELECT Appointments.AppointmentID, Appointments.AppointmentT*ime,
Buildings.Building, Appointments.RoomNumber, Appointments.De*tails
FROM
(Appointments INNER JOIN Buildings ON
Appointments.BuildingID=Buildings.BuildingID) INNER JOIN Tec*hnicians
ON
Appointments.TechnicianID=Technicians.TechnicianID WHERE 1 =* 1 AND
Technicians.TechnicianName = "Matt" AND Appointments.Details* LIKE
"%test%";

I am not sure if this is what you are looking for, however I saw the
LIKE and assumed that you were trying to do this.
Let me know if it helps --Andy

Andy, basically if I search for "*test*", I want access to return
results in which test is found in the field. The *'s are supposed to be
wildcards, searching for any characters before test, and any characters
after test. It works similar to the T-SQL CONTAINS clause. In access,
the query does return the correct results with %'s eg: "%test%", but
does work with "*test*". It just doesn't work when I pass the query
string from ADO to Access. Eg: results would return if the details field
contained something like:
blah blah test blah blah
test blah blah blah blah
blah blah blah blah test




Andy wrote:
> I do not normally use Access, however is the txt you are looking for in
> the like clause literally *test* or are the stars supposed to be
> wildcards? If it is supposed to be 0 or any character before and after
> test, try using the % instead of the * like this:
>
> SELECT Appointments.AppointmentID, Appointments.AppointmentT*ime,
> Buildings.Building, Appointments.RoomNumber, Appointments.De*tails
> FROM
> (Appointments INNER JOIN Buildings ON
> Appointments.BuildingID=Buildings.BuildingID) INNER JOIN Tec*hnicians
> ON
> Appointments.TechnicianID=Technicians.TechnicianID WHERE 1 =* 1 AND
> Technicians.TechnicianName = "Matt" AND Appointments.Details* LIKE
> "%test%";
>
> I am not sure if this is what you are looking for, however I saw the
> LIKE and assumed that you were trying to do this.
> Let me know if it helps --Andy
>

Robert,

I tried the %'s, and on the Access side the query doesn't work. I want
to look for results in which the Details field contains test anywhere in
it. Eg:
blah blah test blah blah
test blah blah blah blah
blah blah blah blah test

It's weird that it works with *'s on the access side.. however whenever
I pass the string from ADO it doesn't work. I appreciate the responses

Robert Gaston wrote:
> Not positive this is it, but you might try % as the wildcard instead of *,
> and possibly drop the parentheses around the various joins, for a less
> Access-ish syntax
>
> "Matt Michael" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>
>>I'm using ADO .NET to connect to a Microsoft Access database, and
>>everything so far has been working in my program except one small problem.
>>I am constructing a query string to search for certain results from a
>>table, and it does not seem to return the correct results.
>>
>>
>>Dim dtResults As New DataTable("SearchResults")
>>
>>Dim cmdSearchResults As New OleDb.OleDbCommand(queryString,
>>appointments.myConnection)
>>Dim daSearchResults As New OleDb.OleDbDataAdapter(cmdSearchResults)
>>
>>daSearchResults.Fill(dtResults)
>>
>>Debug.WriteLine(dtResults.Rows.Count)
>>Debug.WriteLine(queryString)
>>
>>where queryString is the string I have constructed. It looks like this:
>>
>>SELECT Appointments.AppointmentID, Appointments.AppointmentTime,
>>Buildings.Building, Appointments.RoomNumber, Appointments.Details FROM
>>(Appointments INNER JOIN Buildings ON
>>Appointments.BuildingID=Buildings.BuildingID) INNER JOIN Technicians ON
>>Appointments.TechnicianID=Technicians.TechnicianID WHERE 1 = 1 AND
>>Technicians.TechnicianName = "Matt" AND Appointments.Details LIKE
>>"*test*";
>>
>>
>>When I copy this directly into a query in MS Access it returns the correct
>>records, however the data adapter in my code returns 0 results. Whenever I
>>remove the LIKE clause it seems that my queries do return results back to
>>the data table. Is there something buggy with using the LIKE clause to
>>send queries through ADO? Any ideas as to what the problem is anyone?
>>
>>
>>-Matt
>
>
>

If you are running a SQL query on access, it should work the same with
ADO. I have only limited exposure to access.

I did find this wild card reference:
http://bloch.umkc.edu/classes/ward/m...03/tsld030.htm

My reccomendation for further testing is to create a simple query for
testing such as SELECT * FROM Appointments WHERE details LIKE '%test%'
and see what happens when using access and ADO.

I think syntax may depend on your choice of provider. Maybe try
experimenting with your connection string.
http://www.connectionstrings.com

"Andy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you are running a SQL query on access, it should work the same with
> ADO. I have only limited exposure to access.
>
> I did find this wild card reference:
> http://bloch.umkc.edu/classes/ward/m...03/tsld030.htm
>
> My reccomendation for further testing is to create a simple query for
> testing such as SELECT * FROM Appointments WHERE details LIKE '%test%'
> and see what happens when using access and ADO.
>