Results 1 to 12 of 12

Thread: [RESOLVED] DGV usage

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Resolved [RESOLVED] DGV usage

    I would like to use a DGV to display data from two tables. The first table has one of the fields I am interested in and is the parent table for a second table (child) that has the other two fields I am interested in. The first table has a primary key that is related to the child table with a foreign key. Can this be done and, if so, how?

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: DGV usage

    Sure it can be done, the main question is, will the DGV be used for display purposes only? Modifying data from two different tables using a DGV is a different story.

    Give me a coupleof minutes to come up with an example.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: DGV usage

    Wes,

    What the DGV consists of is a list of employee names (from table one), their job title and their department (both from table two). The DGV is used in a double click event to select a row, define a set of variable from the data in the row which is then used as parameters in a query method that is used to filter to a specific employee, whose data is transferred to another table. So no, the data is not modified.

    I have been playing with this for a few minutes and have been able to use DGV Task window to setup what appears to be a set of columns containing the fields I am wanting. However, it will be awhile before I have the form finished enough to run any tests on it.

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: DGV usage

    Here's a two table example
    Code:
    Imports System.Data.SqlClient
    Public Class Form7
        Private con As New SqlConnection(My.Settings.RecipesConnectionString)
        Private da As New SqlDataAdapter("select tblRecipeNames.RecipeNamesId, tblRecipeDetails.RecipeOrigen From tblRecipenames INNER JOIN tblRecipeDetails ON tblRecipeNames.RecipeNamesId = tblRecipeDetails.RecipeNamesId", con)
        Private dt As New DataTable
        Private Sub Form7_Load(sender As Object, e As EventArgs) Handles Me.Load
            Try
                da.Fill(dt)
                Me.DataGridView1.DataSource = dt
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    End Class

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: DGV usage

    If I remember right from your previous posts, you are using a Typed Data and TableAdapters. That's not a problem, just open the dataset, Right Click on an empty space, select Add->TableAdapter. Then create your SQL statement.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: DGV usage

    Thanks Wes, I will take a close look at that as soon as I can get this form to that point. I was a little premature in asking this and was not as close to where I want to do that as I thought I was. I also want to take a closer look at what I was able to setup using the DGV task window and this query to fill it:

    SELECT tblEmployee.intSiTechID, tblEmployee.strFullName, strInfo, tblEmployee.blnActive, tblEmployee.blnInactive, lnkEmployeeInformation.strJobTitle, lnkEmployeeInformation.Department, lnkEmployeeInformation.strLogin, lnkEmployeeInformation.strPassword
    FROM (lnkEmployeeInformation INNER JOIN tblEmployee ON tblEmployee.intSiTechID = lnkEmployeeInformation.intSiTechID)
    WHERE (tblEmployee.blnActive = TRUE AND tblEmployee.intSiTechID = ?)

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: DGV usage

    If you add a new TableAdapter to your Typed Dataset, like I explained in the previous post, you can then use the DGV designer to select that DataTable as the DGV DataSource.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DGV usage

    Quote Originally Posted by wes4dbt View Post
    If I remember right from your previous posts, you are using a Typed Data and TableAdapters. That's not a problem, just open the dataset, Right Click on an empty space, select Add->TableAdapter. Then create your SQL statement.
    I could be wrong but I think you actually need to add a DataTable in the designer rather than a table adapter. If you add a table adapter then that's all you get, i.e. you get no DataTable. If you add a DataTable then you get that and a table adapter to populate it.

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: DGV usage

    I actually came up with what appears to be a pretty good approach to dealing with this and it appears to work rather well. Of course I have done nothing more than run it a couple of times and do not yet know how well this will do. But what I did was the following:

    1. Starting with two tables that are related to each other. The first (Table A) is a parent table with a primary key and the second (Table B) is a child table with a foreign key related to the parent table's primary.
    2. In the DGV I opened the DGV tasks window at the top right hand corner of the DGV in the Design window.
    3. I then selected Table A to be databound.
    Once that is done there is a drop down showing all tables related to Table A
    4. I then used the DGV Edit Columns to select the columns I wanted visible (I kept all of the columns).
    5. I then went back to the Choose Data Source, clicked the drop down and selected Table B.
    6. I went back to the DGV Edit columns and selected which columns I wanted to keep from this table and those that should be visible.

    Then, Low and Behold, the DGV actually worked and displayed the first column from Table A and the 2 desired columns from Table B. Since I have done nothing beyond this yet, I do not know if this is going to actually work for what I want or not. But since all I am going to do is double click on a row, then use that event to assign the values from that row to variables I would be surprised if it doesn't do what I want.
    Anyway, while a rather quirky way of doing this and finding no documentation for it, it seems pretty cool to me.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: DGV usage

    Quote Originally Posted by jmcilhinney View Post
    I could be wrong but I think you actually need to add a DataTable in the designer rather than a table adapter. If you add a table adapter then that's all you get, i.e. you get no DataTable. If you add a DataTable then you get that and a table adapter to populate it.
    It does create a DataTable if you Select Add->TableAdapter.

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: [RESOLVED] DGV usage

    Just a last update on this. The approach of using the DGV Task Window to select and display columns from multiple tables seems to work very well. It turned out that some very quirky queries were required and I ended up with some binding sources that I never even imagined could exist. But I find that it is actually pretty easy to setup and use a DGV with data from multiple tables.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: [RESOLVED] DGV usage

    For anyone who is interested here was the problem and how it works. To begin with what I had was tblEmployee, which contained some employee information. This is the parent table and it is related to several child tables that contain other categories of information. All of the relations are 1-to-1 Primary key/Foreign Key. All of these related fields are a 9 digit number.

    Name:  tables.jpg
Views: 135
Size:  35.7 KB

    In this exercise I was wanting a DGV that had the name of the employee, from tblEmployee, along with the job title, Department, login and password from tblEmployeeInformation. Those were the fields I wanted to keep, but I only wanted to display three fields:

    Name:  DGV.jpg
Views: 131
Size:  8.3 KB

    To do this the first thing was to open the DGV Task Window and make some selections. To do that I first set tblEmployee as the Datasource and took the columns I wanted from that. I then set the child table, lnkEmployeeInfo as the Datasource and took the columns I wanted from that. However, when finished I had to go back and make tblEmployee as the Datasource.

    Name:  taskwindow.jpg
Views: 119
Size:  56.0 KB

    All that was left was the queries for displaying the data I wanted. There are two queries that I use (under different conditions). This was very weird to me, but both actually delivered exactly what I wanted.

    Code:
    SELECT        tblEmployee.intSiTechID, tblEmployee.strFirstName, tblEmployee.strMiddleName, tblEmployee.strLastName, tblEmployee.strFullName, tblEmployee.strInfo, tblEmployee.blnActive, tblEmployee.blnInactive, 
                             lnkEmployeeInformation.intSiTechID AS Expr1, lnkEmployeeInformation.strJobTitle, lnkEmployeeInformation.strDepartment, lnkEmployeeInformation.strLogin, lnkEmployeeInformation.strPassword
    FROM            (lnkEmployeeInformation INNER JOIN
                             tblEmployee ON tblEmployee.intSiTechID = lnkEmployeeInformation.intSiTechID)
    WHERE        (tblEmployee.blnActive = TRUE) AND (tblEmployee.intSiTechID = ?) AND (lnkEmployeeInformation.intSiTechID = ?)
    Method call

    Code:
    Me.TblEmployeeTableAdapter.FillBySiTechID(Me._MasterBase5_0DataSet.tblEmployee, glbintSiTechID, glbintSiTechID)
    Code:
    SELECT        tblEmployee.intSiTechID, tblEmployee.strFirstName, tblEmployee.strMiddleName, tblEmployee.strLastName, tblEmployee.strFullName, tblEmployee.strInfo, tblEmployee.blnActive, tblEmployee.blnInactive, 
                             lnkEmployeeInformation.intSiTechID AS Expr1, lnkEmployeeInformation.strJobTitle, lnkEmployeeInformation.strDepartment, lnkEmployeeInformation.strLogin, lnkEmployeeInformation.strPassword
    FROM            (lnkEmployeeInformation INNER JOIN
                             tblEmployee ON tblEmployee.intSiTechID = lnkEmployeeInformation.intSiTechID)
    WHERE        (tblEmployee.blnActive = TRUE) AND (tblEmployee.intSiTechID = ?) AND (lnkEmployeeInformation.intSiTechID = ?)
    This was the only way I could get this particular method to work. I know that something is not entirely right with this, but I could never figure it out. It does, however, return exactly what is required. The next query and method call are the one that I will use the most and it goes like this:

    Code:
    Me.TblEmployeeTableAdapter.FillByDepartment(Me._MasterBase5_0DataSet.tblEmployee, glbstrDepartment)
    Code:
    SELECT        tblEmployee.intSiTechID, tblEmployee.strFirstName, tblEmployee.strMiddleName, tblEmployee.strLastName, tblEmployee.strFullName, tblEmployee.strInfo, tblEmployee.blnActive, tblEmployee.blnInactive, 
                             lnkEmployeeInformation.intSiTechID AS Expr1, lnkEmployeeInformation.strJobTitle, lnkEmployeeInformation.strDepartment, lnkEmployeeInformation.strLogin, lnkEmployeeInformation.strPassword
    FROM            (lnkEmployeeInformation INNER JOIN
                             tblEmployee ON tblEmployee.intSiTechID = lnkEmployeeInformation.intSiTechID)
    WHERE        (tblEmployee.blnActive = TRUE) AND (lnkEmployeeInformation.strDepartment = ?)
    I have more columns in the Select than I actually need, but I figure that it doesn't cost much and I might decide I need them later.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width