Solved

ADO.NET - Populating multi-table dataset in MS Access

Posted on 2007-03-22
5
344 Views
Last Modified: 2008-09-12
Hello,

I'm trying to figure out how to populate a dataset with 2 tables in ADO.NET.  The code below should work but it doesn't.  It throws an exception at the line 'da.Fill(dSetWF)' saying 'Characters found after end of SQL statement.'  I believe this is because microsoft Access doesn't allow multi statement queries.  What is the best way to accomplish what I am trying to do?

Thanks.

Here is my code:

dSetWF = new DataSetWorkflow();  //dSetWF is a typed dataset

OleDbDataAdapter da = new OleDbDataAdapter("select * from workflow; select * from comments;", cnWF);
da.TableMappings.Add("Table", "Workflow");
da.TableMappings.Add("Table1", "Comments");
da.Fill(dSetWF);

0
Comment
Question by:prosh0t
  • 3
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
dstanley9 earned 500 total points
ID: 18773198
Two separate queries?

dSetWF = new DataSetWorkflow();  //dSetWF is a typed dataset

OleDbDataAdapter daWorkflow = new OleDbDataAdapter("select * from workflow", cnWF);
OleDbDataAdapter daComments = new OleDbDataAdapter("select * from comments", cnWF);
daWorkflow.Fill(dSetWF.Workflow);
daComments.Fill(dSetWF.Comments);

0
 
LVL 7

Author Comment

by:prosh0t
ID: 18773342
i was just hoping there was a more elegant way to do keep this confined to 1 data adapter... is this impossible with ms access?




0
 
LVL 7

Author Comment

by:prosh0t
ID: 18773349
i forgot to mention this is access 97

0
 
LVL 25

Expert Comment

by:dstanley9
ID: 18773399
I don't believe the JET driver supports batch queries.

From http://msdn2.microsoft.com/en-us/library/aa832700(office.10).aspx:

"Access databases cannot return multiple Recordset objects because Microsoft Jet SQL statements do not support multiple SELECT statements."

This is in reference to ADO but it will apply to ADO.NET as well
0
 
LVL 7

Author Comment

by:prosh0t
ID: 18773821
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sort GridView by ID Descending 1 17
Dot Net 4.6.1 installation fails on W2012 R2 server 14 40
What are the simplest ways to create a Merge Sort? 3 29
Code enhancement 4 22
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question