roflkind
asked on
OleDbConnection: combining queries
Hello,
Question 1:
i am using an OleDbConnection to receive data from an Access DB.
I get all the news written with this command:
"SELECT * FROM news" and i fill them with OleDbDataAdapter into a dataset.
But there is another table called newscom, which is standing for the comments written for a news.
I get all the comments written for one news using this command:
SELECT * FROM newscom WHERE id LIKE 'newsid'
And now i want the number of comments written for one news to be a column in my news table fetched with the OleDbDataAdapter. (Combining the first query with the second one)
Question 2:
The second question is very similar to the first one.
This time i have the author from a news, and i want to get all informations stored in the user db for this author.
The command to get the userrow with the information in it is:
SELECT * FROM user WHERE id LIKE 'authorid'
Now i want to combine the news query and the user query.
Again I want the UserRow to be a column (as an object or sth like that) in my news table fetched with the OleDbDataAdapter.
Any suggestions?
Best regards,
roflkind
Question 1:
i am using an OleDbConnection to receive data from an Access DB.
I get all the news written with this command:
"SELECT * FROM news" and i fill them with OleDbDataAdapter into a dataset.
But there is another table called newscom, which is standing for the comments written for a news.
I get all the comments written for one news using this command:
SELECT * FROM newscom WHERE id LIKE 'newsid'
And now i want the number of comments written for one news to be a column in my news table fetched with the OleDbDataAdapter. (Combining the first query with the second one)
Question 2:
The second question is very similar to the first one.
This time i have the author from a news, and i want to get all informations stored in the user db for this author.
The command to get the userrow with the information in it is:
SELECT * FROM user WHERE id LIKE 'authorid'
Now i want to combine the news query and the user query.
Again I want the UserRow to be a column (as an object or sth like that) in my news table fetched with the OleDbDataAdapter.
Any suggestions?
Best regards,
roflkind
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
First off if you are going to display the news with the comments and the author then using the join query will help with performance. If you want to count the comments in the newscom table you just execute a small query. If it is for the whole comments table then select count(*) from newscom and then use the ExecuteScaler method from the OleDBCommand object to get the single value result.
I think I looked at you initial question wrong. If you are trying to show a news article and the x number of comments for it as well as the authors for it then you instead should combine the users and news tables and keep the newscom table seperate. In c# you can then add a data relation object to the newscom DataTable object in your DataSet. When it is done right then .NET and C# will handle the Master / Details part of it.
I think I looked at you initial question wrong. If you are trying to show a news article and the x number of comments for it as well as the authors for it then you instead should combine the users and news tables and keep the newscom table seperate. In c# you can then add a data relation object to the newscom DataTable object in your DataSet. When it is done right then .NET and C# will handle the Master / Details part of it.
ASKER
k thanks
ASKER
how to do this?