DataAdapter returns 0 results, what am I doing wrong?

[Matt Michael]

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

[Kerry Moorman]

Matt,

You might need to use the % character instead of the * character with Like:

AND Appointments.Details LIKE "%test%";

Also, double-quote delimiters might work, but you might need single-quotes:

AND Appointments.Details LIKE '%test%';

Kerry Moorman
"Matt Michael" wrote:

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*";

[Matt Michael]

Kerry,

Thanks for the reply. On the MS Access side, using the like clause with
% signs does not return the desired results (AND Appointments.Details
LIKE "%test%"; Basically I want to return results where the Details
field contains the word test (like T-SQL CONTAINS clause). Single
quotes with *'s does not work through ADO, however returns correct
results in MS Access.
Kerry Moorman wrote:

Matt,

You might need to use the % character instead of the * character with Like:

AND Appointments.Details LIKE "%test%";

Also, double-quote delimiters might work, but you might need single-quotes:

AND Appointments.Details LIKE '%test%';

Kerry Moorman
"Matt Michael" wrote:

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.Technician ID WHERE 1 = 1 AND
Technicians.TechnicianName = "Matt" AND Appointments.Details LIKE "*test*";