Query does not return data in VB.Net

[BobWman]

I have the following query that correctly returns 53 rows when I run it in Access.

Select L.ID, L.TracsNumber, P.OldProjectNumber, P.ADOTPrefix, L.TestHole, L.UpperDepth, L.LowerDepth, L.SamplePhase As Phase, Format(L.DateSampled, 'mm/dd/yyyy') As Sampled, T.Description As Type, L.SampledBy From (GeoLog L Left Join Project P On P.TracsNumber = L.TracsNumber) Left Join SAMaterialType T On T.MaterialTypeCode = L.MaterialTypeCode Where P.ADOTPrefix Like '*60*' Order By L.TracsNumber, L.SamplePhase, L.TestHole, L.UpperDepth;

I am using the query in VB.Net to populate a DataTable as follows:

mdtList = New DataTable
Try
da = New OleDb.OleDbDataAdapter(strSql, gstrConnect)
da.Fill(mdtList)

After running this code, mdtList contains zero rows. strSql contains the exact same query as shown above, and I am using the same database.

Does anyone see my problem? I am used to working with SQL Server, and am wondering if this is some quirky thing with Access.

Thanks.

 

[RiverGuy]

Expose the exception in a message box or something, to see if you are getting an exception.

 

[BobWman]

I just found the problem.

Even though Access requires * in Like clause, VB.Net still uses % just like SQL Server.

This is an adventure. Many queries written in SQL Server need to be re-written to work in Access. Then, when you use Access syntax in this case, it doesn't work. Fun.

Thanks for the response.

 

[chrissie1]

if you want to use access with sql-server try using an access project file or adp then you can use sqlserver syntax and storedprocedures just like in sql-server.

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong