Jump to content

dataset


Recommended Posts

Hello,I have two tables in ms access; writers and articles and want to list latest article of each writer by a datalist. (A writer has many articles.) But it lists all the articles. Here is the code;

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM writers INNER JOIN articles ON writers.author_id=articles.author_id", conn);adapter.Fill(dataset, "writers");writersList.DataSource = new DataView(dataset.Tables[0]);writersList.DataBind();

In the item template I use fields of both tables. I can't limit second table. :)

Link to post
Share on other sites
Hello,I have two tables in ms access; writers and articles and want to list latest article of each writer by a datalist. (A writer has many articles.) But it lists all the articles. Here is the code;
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM writers INNER JOIN articles ON writers.author_id=articles.author_id", conn);...

Well, presumably articles has an article_date field? You'll need to refer to that, to select the latest article. :) You could either load the articles table separately into the dataset, and set bindings/relationships to suit, or, if you want it all in the one dataset table, you'll need a subquery:
SELECT * FROM writers w INNER JOIN articles a ON w.author_id=a.author_idWHERE a.article_date = 	( SELECT max(article_date) FROM articles		  WHERE author_id = w.author_id )

Link to post
Share on other sites
Well, presumably articles has an article_date field? You'll need to refer to that, to select the latest article. :) You could either load the articles table separately into the dataset, and set bindings/relationships to suit, or, if you want it all in the one dataset table, you'll need a subquery:
SELECT * FROM writers w INNER JOIN articles a ON w.author_id=a.author_idWHERE a.article_date = 	( SELECT max(article_date) FROM articles		  WHERE author_id = w.author_id )

Thank you! Since an author may send more articles in the same day and my article_date field doesn't contain time, I changed it to max(article_id). Thank you very much for your help.
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...