Problem with ADO .NET and Microsoft Access

[lightweight]

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.

(Visual Basic .NET)
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. Any ideas as to what the problem is anyone?


-Matt

 

[lightweight]

anybody?

 

[Jim Bancroft]

I'd like to see more info about your connection-- the "appointments.myConnection" bit. Your code looks good so that's where I'd focus. This database isn't password protected in some way and maybe the connection isn't working for that reason?

What happens if you surround this code with a try...catch block? Is an exception thrown at some point? You may have already done that, so apologies if this sounds like a programming 101 lecture. :beer:

 

[torpid]

Hm... if your sql really does have double quotes then that is your problem. you have to use single quotes around string values.

 

[oog]

i'm not that familiar with SQL in Access but is the wildcard character for Access a *? In many databases, it's a %.

 

[torpid]

Originally posted by: oog
i'm not that familiar with SQL in Access but is the wildcard character for Access a *? In many databases, it's a %.

It is in access, but I'm not sure about ADO.net. It doesn't work in ADO, so my guess is that it may also be a problem.