access query and DataAdapter have different data

[Guest]

I have a access query with 3 tables with left joins and some criteria conditions. Viewing the query in access 4 records are returned which is correct. If I use a .NET OLEdbAdapter to read the query I get only the first record in the .NET application. Many onther queries and tables work fine. It appears that the jet 4 connection to access can not be relied upon and access should not be used as an undelying application database. Has anyone seen simular problems or know a solution?

 

[William Ryan eMVP]

Carl:

Right off the bat I'd make absolutely sure the commandtext is the exact
same. A date implementatino for isntance may act differently between access
and .NET depending on if you used a function to get a date. There are other
things that can cause this that would be syntax related.

Can you post the actual text of the access command and the commandtext for
your oledb command?

Next, I'd really really try to avoid using joins in this context. If you
search through this ng or the .NET ones, it's discussed pretty frequently.
I'm not saying joins are never ok, but in general, you should probalby
consider opting for individual queries with matching Where clauses and then
use a DataRelation object.

Personally I avoid access like the plague but I know a lot of folks that
use it regularly and I've never heard of such a problem (I"m not saying it
doesn't exist, but if it does, I've never come across it). I've heard many
occassions when people thought it was access but it was what was being used
in the query that was the cause each time.

Just for giggles, go ahead and pull all 3 tables over individually with no
join into one dataset and then add a datarelation(s) and see what the total
number of rows you see are. Here's a link on how to do it if you are
unfamiliar http://www.knowdotnet.com/articles/datarelation.html

If that still yeilds the wierd results, I'd be glad to try to replicate it.
If you could send me the MDB and the query, or just give me the table
structures adn the query, I'll gladly try to replicate it. BTW, what
version of access and what version of the framework are you running.

HTH,

Bill

I have a access query with 3 tables with left joins and some criteria

conditions. Viewing the query in access 4 records are returned which is
correct. If I use a .NET OLEdbAdapter to read the query I get only the first
record in the .NET application. Many onther queries and tables work fine.
It appears that the jet 4 connection to access can not be relied upon and
access should not be used as an undelying application database. Has anyone
seen simular problems or know a solution?

 

[Guest]

Here is the command text configured for access view AdHocItem
Me.OleDbSelectCommand7.CommandText = "SELECT articlenumber, [Desc] FROM AdHocItems" which returns 1 record in .NE

Here is the Access Query AdHocItem
SELECT Item.articlenumber, Item.Description AS [Desc
FROM (Item LEFT JOIN Node ON (Item.Leg = Node.Leg) AND (Item.LevelNumber = Node.LevelNumber) AND (Item.UnitName = Node.UnitName)) LEFT JOIN [lib:articleDb] ON Item.articlenumber = [lib:articleDb].Articl
WHERE ((([lib:articleDb].WBSFeature) Like "tvl*" Or ([lib:articleDb].WBSFeature) Is Null) AND ((Node.ExcludeUnit)=No) AND ((Item.ProductCatalogID)=[ItemNo])
GROUP BY [Item].[articlenumber], [Item].[Description
ORDER BY [Item].[articlenumber]

which displays 4 records wen opened in access

I'll look over your article and also put the SQL in the DataAdapter to see what happens. I'll post the resul
My concern is I plan to query the access database for sums to be used in proposals etc. where the individual records may not be displayed to the user. I have lost confidence that I will get the right answer in .NET even though I have tested the queries in access. I am considering switching to msde or some other database.

Thanks for the post

 

[William Ryan eMVP]

I defintely would suggest MSDE over Access. I"d recommend just about
anything over access. MSDE is free, holds just as much data, is real client
server, has real backup capabilities on and on. BTW, if you are going to
use aggregates, don't fire them on the DB unless you have to. You can grab
the data in a datatable and use DataTable.Compute(Aggregate(FieldName),
Condition > Whatever)

This saves stress on the db. That's very interesting what's happening but I
still have a feeling it's the query. Just for the sake of curiosity, make
the commandtext the same on the client side and see what it returns. I know
that, for instance, you can used reserved words in Access directly but when
connecting via ADO.NET, it will blow up. I wonder if something similar may
be in place since there obviously is different behavior at different times.

Nonetheless, using a Stored Proc with MSDE would be a breeze here, and using
a DataRelation would almost certainly fix the problem too (I'd really be
interested in seeing the record count on each before you apply the relation
....

Let me know what happens and Good Luck!

Bill

Here is the command text configured for access view AdHocItems
Me.OleDbSelectCommand7.CommandText = "SELECT articlenumber, [Desc] FROM

AdHocItems" which returns 1 record in .NET

Here is the Access Query AdHocItems
SELECT Item.articlenumber, Item.Description AS [Desc]
FROM (Item LEFT JOIN Node ON (Item.Leg = Node.Leg) AND (Item.LevelNumber =

Node.LevelNumber) AND (Item.UnitName = Node.UnitName)) LEFT JOIN
[lib:articleDb] ON Item.articlenumber = [lib:articleDb].Article

WHERE ((([lib:articleDb].WBSFeature) Like "tvl*" Or

([lib:articleDb].WBSFeature) Is Null) AND ((Node.ExcludeUnit)=No) AND
((Item.ProductCatalogID)=[ItemNo]))

GROUP BY [Item].[articlenumber], [Item].[Description]
ORDER BY [Item].[articlenumber];

which displays 4 records wen opened in access

I'll look over your article and also put the SQL in the DataAdapter to see

what happens. I'll post the result

My concern is I plan to query the access database for sums to be used in

proposals etc. where the individual records may not be displayed to the
user. I have lost confidence that I will get the right answer in .NET even
though I have tested the queries in access. I am considering switching to
msde or some other database.

 

[Ron Allen]

Carl,
You're going to have to use different wild cards as the OleDb provider
uses the ANSI wildcard chars, i.e. % vs * in my experince. I use joins all
the time in my current billing system (currently being migrated to MSDE for
performance reasons) and they work. You will have to write your own custom
Delete/Insert/Update logic though.
If you want to use Access syntax put these into Querys and call them
from .NET. MSDE would have much better performance especially with stored
procedures.

Ron Allen

Here is the command text configured for access view AdHocItems
Me.OleDbSelectCommand7.CommandText = "SELECT articlenumber, [Desc] FROM

AdHocItems" which returns 1 record in .NET

Here is the Access Query AdHocItems
SELECT Item.articlenumber, Item.Description AS [Desc]
FROM (Item LEFT JOIN Node ON (Item.Leg = Node.Leg) AND (Item.LevelNumber =

Node.LevelNumber) AND (Item.UnitName = Node.UnitName)) LEFT JOIN
[lib:articleDb] ON Item.articlenumber = [lib:articleDb].Article

WHERE ((([lib:articleDb].WBSFeature) Like "tvl*" Or

([lib:articleDb].WBSFeature) Is Null) AND ((Node.ExcludeUnit)=No) AND
((Item.ProductCatalogID)=[ItemNo]))

GROUP BY [Item].[articlenumber], [Item].[Description]
ORDER BY [Item].[articlenumber];

which displays 4 records wen opened in access

I'll look over your article and also put the SQL in the DataAdapter to see

what happens. I'll post the result

My concern is I plan to query the access database for sums to be used in

proposals etc. where the individual records may not be displayed to the
user. I have lost confidence that I will get the right answer in .NET even
though I have tested the queries in access. I am considering switching to
msde or some other database.