incorrect data to data adapter

  • Thread starter Thread starter carl morahan
  • Start date Start date
C

carl morahan

I have a 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 data
adapter 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 see simular
problems?
 
Hi Carl,

From your description, I understand that you would like to know why you
could not get the right result from .Net data adapter when you are using 3
tables with left joins.

However, I am not sure about your steps. Based on my knowledge, if you
could get right result from Access's query. There should be no error with
the query. So would you please show me more information about your using of
.net data adapter and Tables and Queries you used.

In addition, plesae ensure that you have upgraded to the latest service
pack of Jet, which could be get
Jet 4.0 Service Pack 8 (SP8) for Windows XP (KB829558)
http://www.microsoft.com/downloads/details.aspx?FamilyID=2deddec4-350e-4cd0-
a12a-d7f70a153156&DisplayLang=en

Hope this helps and I am looking forward more information

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Thanks for the reply Michael but still no luck

Installed latest jet but the result is the same

All I have in the .NET adapter i
Me.OleDbSelectCommand7.CommandText = "SELECT articlenumber, [Desc] FROM AdHocItems

Where AdHocItems is the microsoft access query that returns one record in .NET but four records if I open AdHocItems using Microsoft Access 200

The Actual Access SQL is
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]

Do you think this is a jet 4.0 problem or a .NET problem? The problem followed on installtion of the .NET windows app on another machine

I can provide the access db is desired. It's a 4 MB test d
 
Hi Carl,

Thanks for you instant reply.

I am not very familiar with .net development and I think posting in .net
newsgroup might have a quicker response to the solution, However, I would
like to have a try on this.

Do you receive any error message such as Syntax Error when using Data
Adapter when using Visual Studio .Net Query Builder? In this case, you
could have a look at
Visual Studio .NET Query Builder Builds Incorrect Syntax for Jet Queries
That Involve Joins
http://support.microsoft.com/?id=318646

It seems that you call the query in Access and get the result from query,
which could not get the right result. How about make the query in VS.NET
and get the data directly? Will this do as a workaround?

Last but not the least, it above all could not resolve your issue, would
you please show me the table infomation so that I could reproduce it on my
machine. As you see thant it's not recommand to send email with mdb file by
newsgroup support.

Thank you for your patience and cooperation. I am looking forward to
hearing from you.

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Hi Carl,

I have tried it on my machine according to your description. I created a
query which left joins three tables, filled it to a DataSet and check the
content of the results. However, I cannot reproduce it. Could you please
send me your Access database and the steps for repro this issue, so that we
can deliver our assistance more quickly? Thanks very much for your
cooperation.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Carl,

I forgot to mention that removing 'online' from the no spam alias is my
email address.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Carl,

Thanks for your patience and corperations.

I, along with Kevin, successfully reproduce the situation as you mentioned.

Based on my knowledge, Microsoft Jet database engine SQL is generally ANSI
-89 Level 1 compliant. However, certain ANSI SQL features are not
implemented in Microsoft Jet SQL. With the release of Microsoft Jet version
4.X, the Microsoft OLE DB Provider for Jet exposes more ANSI-92 SQL syntax.
Conversely, Microsoft Jet SQL includes reserved words and features not
supported in ANSI SQL.

For example, Microsoft Access uses the wildcard characters "*" to denote
"any number of characters at this position" and "?" to denote "any one
character at this position." In Visual Studio SQL statements, you must use
"%" and "_" instead.

Your view contains LIKE "tvl*", which make it correct displayed in Access
but not in VS.NET. While we modify it as LIKE "tvl%", you will get it
correct in VS.NET and wrong in Access. So, in my opinion, you'd better make
a query in VS.NET if you want to get correct both in Access View and VS.NET

For more information about wildcard in Access and Jet Engine, search the
Microsoft Access Help topics for "Comparison of Microsoft Jet Database
Engine SQL and ANSI SQL."

and you could find this in Books Online
ACC2002: No Effect When You Programmatically Set ANSI-92 Mode
http://support.microsoft.com/?id=282398

Thank you for your patience and cooperation again. If you have any
questions or concerns, don't hesitate to let me know.

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Thanks for the follow-up. The ANSI-89/ANSI-92 issue is something I had not considered. This definitely explains some of the symptom I am seeing. It looks like the best solution is to forget the use of access queries as views for the data adapter and write the queries in .Net. At least I now have my confidence restored in access. Thanks
 
Back
Top