ADO.NET not retrieving results correctly

  • Thread starter Thread starter Matt Michael
  • Start date Start date
M

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
 
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
 
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
 
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
 
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/mis552/W2000CH03/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.
 
Back
Top