locked
Pulling data from different tables into one gridview

    Question

  • Hello,

    I am developing an application that keeps track of employee points for the company I work for. I pull in all my employee data from another database through a web service and keep the information I want to edit (the points, awards, etc.) in a SQL database.

    I have my dataset set up with a number of tables and I successfully pull in data from the web service and fill that into another table in my dataset. I have created a relation between the primary keys of the two tables in question (the list of employees and their corresponding points).

    My issue comes into the application layer of things, I want to show in a grid view the first and last name of the employee from the employee table and a few columns from my points table all in the same grid view. The grid view must be databound to the points table in order to keep track of the changes but should not be to the employee table since I have no need to edit an employee's name, however I need to sort the employees by city, shift, and department and the information to do that is in the employee table. The question is how to do this, I can create a grid view to show one or the other but I can't seem to figure out out to show some columns of one table and some columns of another. The control is difficult too, as in I need to base my query off of one table and have the ability to edit the other.

    The only solution I have come across is to build a whole new table copying columns from each of the other two into it, but I have trouble understanding how I am going to keep my points table updated in this way. The new table would have to keep the points table updated which would have to keep the database updated after that. Seems too complicated, there must be a better way.

    I am using the Visual Basic 2005 Express edition to do this, any advise would be appreciated.
    Thanks

    Friday, March 23, 2007 3:11 PM

Answers

  • Hello to those looking for an answer to my problem,
    I have found a partial solution that did not work out for me but might work out for some. If you go to this site:

    http://support.microsoft.com/default.aspx/kb/325682/en-us

    You will find a class you can download and add to your project called JoinView. Its pretty useful in the sense that you can use it to create a data grid with some columns from one table and some from another as long as a relation exists between the two tables.

    The reason this did not work for me is because you can only modify and sort by one table, so as long as all you need to do is display columns from the second table this JoinView class is perfect! Its got some excellent examples on how to use it and would have been used by me if I didn't want to edit one table and sort using the other.

    My issue was worked around by not using a grid view at all. Because I could not find a solution to my liking I created a listbox to display and sort the columns of one table and a series of textboxes that were filled in depending on what was selected in the listbox, these textboxes were databound to the table I wanted to edit so that if you changed the values you could then save it to the database.

    I hope this helps some that are looking for similar functionality.


    Monday, April 02, 2007 1:12 PM