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("Sear chResults")

Dim cmdSearchResult s As New OleDb.OleDbComm and(queryString ,
appointments.my Connection)
Dim daSearchResults As New OleDb.OleDbData Adapter(cmdSear chResults)

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.Ap pointmentID, Appointments.Ap pointmentTime,
Buildings.Build ing, Appointments.Ro omNumber, Appointments.De tails FROM
(Appointments INNER JOIN Buildings ON
Appointments.Bu ildingID=Buildi ngs.BuildingID) INNER JOIN Technicians ON
Appointments.Te chnicianID=Tech nicians.Technic ianID WHERE 1 = 1 AND
Technicians.Tec hnicianName = "Matt" AND Appointments.De tails 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.De tails LIKE "%test%";

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

AND Appointments.De tails LIKE '%test%';

Kerry Moorman
"Matt Michael" wrote:

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

SELECT Appointments.Ap pointmentID, Appointments.Ap pointmentTime,
Buildings.Build ing, Appointments.Ro omNumber, Appointments.De tails FROM
(Appointments INNER JOIN Buildings ON
Appointments.Bu ildingID=Buildi ngs.BuildingID) INNER JOIN Technicians ON
Appointments.Te chnicianID=Tech nicians.Technic ianID WHERE 1 = 1 AND
Technicians.Tec hnicianName = "Matt" AND Appointments.De tails 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.De tails
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.De tails LIKE "%test%";

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

AND Appointments.De tails LIKE '%test%';

Kerry Moorman
"Matt Michael" wrote:

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

SELECT Appointments.Ap pointmentID, Appointments.Ap pointmentTime,
Buildings.Bui lding, Appointments.Ro omNumber, Appointments.De tails FROM
(Appointmen ts INNER JOIN Buildings ON
Appointments. BuildingID=Buil dings.BuildingI D) INNER JOIN Technicians ON
Appointments. TechnicianID=Te chnicians.Techn icianID WHERE 1 = 1 AND
Technicians.T echnicianName = "Matt" AND Appointments.De tails LIKE "*test*";