[RESOLVED] display DGV list from more than one table

I have the following DataGridView.

Attachment 152239

This DGV is designed to display a list of people from either of two different tables in a database. In the DGV properties Window I created the columns shown in the DGV above. Since there could be either of two tables used I cannot set the Data Source and Data Member properties in the properties window (can I?).

I am aware of how one would approach this if instead of the DGV I had a set of textboxes/labels that I wanted to display:

'Form controls databound to tblChangeApprove.
Me.TblChangeApproveTableAdapter.FillByChangeIDLogin(Me._MasterBase4_0ItemMasterDataSet.tblChangeAppr ove, glbintCRNum, glbstrLogin)
With lblChangeID
.DataBindings.Add("Text", tblChangeApproveBindingSource, "intChangeID")
End With
With lblName
.DataBindings.Add("Text", tblChangeApproveBindingSource, "strName")
End With
With lblJobTitle
.DataBindings.Add("Text", tblChangeApproveBindingSource, "strJobTitle")
End With
With lblDepartment
.DataBindings.Add("Text", tblChangeApproveBindingSource, "strDept")
End With
With txtSignature
.DataBindings.Add("Text", tblChangeApproveBindingSource, "strSignature")
End With
End Sub

I know that some of you do not like using With/EndWith, but I do. Anyway, it is clear to me that this is not the approach one would take with a DGV. I have attempted a number of different command approaches for the DGV with no success. Could someone provide me with the syntax used to call the collection from the table and display them in the DGV?

There is no need using "with" command there
if you want to display data from two different tables then you have to use the Linq command

Code:

---

Dim Data = From MyTable In YourTableName
Select ChangeId, Name
        dgv.Columns(0).DataSource = Data.ChangeId
        dgv.Columns(2).DataSource = Data.Name

        Dim Data2 = From MyTable In YourTableName
        Select JobTitle, Department
        dgv.Columns(3).DataSource = Data2.JobTitle
        dgv.Columns(4).DataSource = Data2.Department

---

If these 2 tables come from the same database, is there a reason not to adjust the SQL command (presumably with a join) to get the exact data you want from the database directly?

If that is not possible, then maybe you could alias the selected field names in the SQL statement so they match the DGV column's DataPropertyName?

I guess the point here is if possible, you should be asking the database for the exact data you want and in the format you want it.

Actually Kebo, that is already done. The query that I am using is already setup that way. However, calling the query will not display the list into the datagridview. What is required is to set the Data Source and the Data Member properties in the datagridview properties window (it just occurs to me that I can set the Data Source property here since it is common for both tables). However, since either of two different tables might be used (from the same dataset), then the Data Member property must be set at runtime in the code (I assume after the query has been called).

Can you explain, you have 2 tables, 1 datagrid, and you want to show data eaither from one or from another, not the 2 at the same time?

TATAPRO,

OK, not a problem with not using With/EndWith. Even though I personally like it.

Having said that, I have a couple of question:

1. My VB wants an AS after the Dim Data. Perhaps As DataColumn? or what.
2. And since I am using a dataset with table adapters I was wondering if just the table name is enough after the In.
3. The Select statement implies to me that this might be a query. If not, this is not working the way it is.

So what am I missing here?

Assuming that you want to display one table at the time and that you have already the Dataset and dataadapter created:

Code:

---


Friend Sub DisplayTable_A()

        TBCNTSTableAdapter.Fill(Me.DBaseDataSet.TBCNTS)
        DGV.DataSource = DBaseDataSet
        DGV.DataMember = "TBCNTS"
        DGV.Columns(0).HeaderText = "x1"
        DGV.Columns(1).HeaderText = "x2"

    End Sub

    Friend Sub DisplayTable_B()

        TBFRNCRTableAdapter.Fill(Me.DBaseDataSet.TBFRNCR)
        DGV.DataSource = DBaseDataSet
        DGV.DataMember = "TBFRNCR"
        DGV.Columns(0).HeaderText = "x1"
        DGV.Columns(1).HeaderText = "x2"

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        DisplayTable_A()

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        DisplayTable_B()

    End Sub

---

You may need to add "DGV.Columns.Clear" at the beguining of each sub.

If you want to display bouth tables at the same time, either you do it on SQL, with a query like Kebo sugested, or you use Link, i would go for the SQL query.

Here is a link:
https://www.w3schools.com/sql/sql_join.asp

Code:

---


SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

---

Mike,

That is correct. So what I have is a datagridview that has an already defined Datasource (the two tables that I am interested in are both in the same dataset). So what I am after is to display a list from either one table or the other table, but not both.

I already have the required queries written into the table adapters of the two tables I am interested in. So here are what is called called in the load event.

If ReasonOne Then
Me.TblChangeApproveTableAdapter.FillByChangeID(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove, glbintCRNum)

ElseIf ReasonTwo Then
Me.TblChangeTrainTableAdapter.FillByChangeID(Me._MasterBase4_0ItemMasterDataSet.tblChangeTrain, glbintCRNum)

End If

However, what I am lacking is that after the query call is made, in either of the branches, I need to define the Data Member required to fill the datagridview from the table defined as the Data Member.

Probably it should go something like:

DataGridViewName.Columns(0) = ("Data Member", TableNameBindingSource, "ColumnName")

However, that isn't it, although I do believe that it is in the ball park.

If I was only using one table this would be done in the DataGridView properties window and everything would be copasetic.

I dont understand, lets see

Have a look in this:

Code:

---


 Enum TableToFill
        TableA
        TableB
    End Enum

    Friend Fill_Table As TableToFill

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        If Fill_Table = TableToFill.TableA Then

            DisplayTable_A()

        ElseIf Fill_Table = TableToFill.TableB Then

            DisplayTable_B()

        End If

    End Sub

    Friend Sub DisplayTable_A()

        TBCNTSTableAdapter.Fill(Me.DBaseDataSet.TBCNTS) ' This fills the table
        DGV.DataSource = DBaseDataSet  'DGV is a datagrid/DBaseDataSet  is a dataset
        DGV.DataMember = "TBCNTS" 'This is the table name i m using
        DGV.Columns(0).HeaderText = "x1" ' Sets columns header text
        DGV.Columns(1).HeaderText = "x2"

    End Sub

    Friend Sub DisplayTable_B()

        TBFRNCRTableAdapter.Fill(Me.DBaseDataSet.TBFRNCR)
        DGV.DataSource = DBaseDataSet
        DGV.DataMember = "TBFRNCR"
        DGV.Columns(0).HeaderText = "x1"
        DGV.Columns(1).HeaderText = "x2"

    End Sub

---

The Enum can be replaced from something else, is just a easy way to exemplify it.

You havent explain where you get the condition from, or i didnt understand it...

If this isn't it, post your code, wrape it in [ Code ] Your code [ /Code ]
it makes it easyer to read and understand.

Mike,

I tried your approach above and it comes very close to what I am looking for. However, something is still missing.

I set the code up in the load event for just one of the tables (I will get to the other one when I can do it for one).

Me.TblChangeApproveTableAdapter.FillByChangeID(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove, glbintCRNum)
dgvSignList.DataMember = "TblChangeApprove"
dgvSignList.Columns(0).HeaderText = "ChangeID"
dgvSignList.Columns(1).HeaderText = "Name"
dgvSignList.Columns(2).HeaderText = "Job Title"
dgvSignList.Columns(3).HeaderText = "Department"


What the dgv actually displays is this:

Attachment 152249

Now I can tell that it is accessing the correct data member because the list always has the correct number of records (for the intChangeID in the query call. What is lacking now is the content for the cells in the list.
So what do you think I might be lacking to fill in the content of the cells of the rows displayed?

If i understand, the table has data but is not displaying it in the datagridview, is that it?

That is what it appears to me. When I ran the code in the event I always get the appropriate number of records (based on the value of glbintChangeID), but not the content in the rows shown in the display. It seems to me that all I am doing with the code, as it is, is providing the column names (which I already have and do not really need).

As to your previous questions, I already have a working join query and I am NOT wanting to show both tables at the same time.

Oh, and just to make sure I checked the fore color properties in the datagridview properties window to make sure that it was set properly. I have been caught in the past with transparent fore color setting. Not good.

Ok, i m not sure what is not working, the first thing that ocurs to me is that u have a Designed Dataset and Dataadapter that has the table sheame, and thats why you get the columns in place, but you not actualy filling the table you think you are, can you post the complete code for the all process?

yeah, if you have the forecolor equals to the cellback colr, you wont see the results.

Mike, the code above is the complete code. But here it is again anyway:

Quote:

---

Private Sub SubSignList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.TblChangeApproveTableAdapter.FillByChangeID(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove, glbintCRNum)
dgvSignList.DataMember = "TblChangeApprove"
dgvSignList.Columns(0).HeaderText = "ChangeID"
dgvSignList.Columns(1).HeaderText = "Name"
dgvSignList.Columns(2).HeaderText = "Job Title"
dgvSignList.Columns(3).HeaderText = "Department"
End Sub

---

The only other code for this is contained in the query, which resides in the table adapter. I have checked it and it is providing the correct data.

why "FillByChangeID" and not just "Fill", where did you got that?

Are you sure that FillByChangeID query is returning data?

The query is not just a fill query. It is a filter query. I only want to show those records whose intChangeID value matches with glbintCRNum.

Here is the query as setup in the dataset and it does meet my requirements:

Quote:

---

SELECT intApproveID, intChangeID, strName, strJobTitle, strDept, strSignature, strLogin, strPassword, strMasterBaseType, blnRemoved
FROM tblChangeApprove
WHERE intChangeID = ?

---

I went to using just the fill query and the only change is that the number of rows equals the number of records I have in the table, but still no content to the records.

Mi query:

Code:

---

SELECT ID, Nome, Morada, CodigoPostal, Cidade, Pais, TVA, Telefone, Telemovel, Fax, Email, Pagamento, Credito, Bloquear FROM dbo.TBCNTS WHERE ID=@Vlr

---

Code for the form where i have the datagridview

Code:

---


Public Class Child_Frm

    Enum TableToFill
        TableA
        TableB
    End Enum

    Friend Fill_Table As TableToFill
    Friend FilterID As Integer

    Public Sub New(Tb As TableToFill, IDX As Integer)

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Fill_Table = Tb
        FilterID = IDX

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        If Fill_Table = TableToFill.TableA Then

            DisplayTable_A()

        ElseIf Fill_Table = TableToFill.TableB Then

            DisplayTable_B()

        End If

    End Sub

    Friend Sub DisplayTable_A()

        TBCNTSTableAdapter.FillBy(DBaseDataSet.TBCNTS, FilterID)

        With DGV

            .DataSource = DBaseDataSet
            .DataMember = "TBCNTS"
            .Columns(0).HeaderText = "x1"
            .Columns(1).HeaderText = "x2"

        End With


    End Sub

    Friend Sub DisplayTable_B()

        TBFRNCRTableAdapter.FillBy(DBaseDataSet.TBFRNCR, FilterID)

        With DGV

            .DataSource = DBaseDataSet
            .DataMember = "TBFRNCR"
            .Columns(0).HeaderText = "x1"
            .Columns(1).HeaderText = "x2"

        End With

    End Sub

End Class

---

Code on the Mdi parent form to call the child_form

Code:

---


Public Class MainFrm
    'Depending on how you set the condition you can get this done with one bottun and a If A  elseif B
    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        'Replace the 1 by the id you want to filter
        Dim FRM As New Child_Frm(Child_Frm.TableToFill.TableA, 1) With {
            .MdiParent = Me, .Text = "Displaying table A"}

        FRM.Show()

    End Sub

    Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
        'Replace the 3 by the id you want to filter
        Dim FRM As New Child_Frm(Child_Frm.TableToFill.TableB, 3) With {
            .MdiParent = Me, .Text = "Displaying table B"}

        FRM.Show()

    End Sub
End Class

---

I do not believe that the problem is in the query. This table has four records in it. So if I just use the fill query and run it the results are:

Quote:

---

Me.TblChangeApproveTableAdapter.Fill(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove)
dgvSignList.DataMember = "TblChangeApprove"
dgvSignList.Columns(0).HeaderText = "ChangeID"
dgvSignList.Columns(1).HeaderText = "Name"
dgvSignList.Columns(2).HeaderText = "Job Title"
dgvSignList.Columns(3).HeaderText = "Department"

---

Attachment 152253

Note that the number of rows shown matches the total number of records in this table now. So it is my impression that there is still something more than the above code that is required.

Have a look:


https://imgur.com/a/8FrsX

By the way, in your code you define data member, but you not seting datasource to the dataset.

The datasource is common to both tables, so I defined that in the DGV properties window. However, just in case, I defined it in the code, but the results were still the same.

You mention that you have added the column to the datagridview, when you have a bound datagridview you dont need to do that, i just did that to mine, and it adds the table columns after the ones i added in the designer.

This means that if you disable scrolbars, and you added the columns in designer, you wont see the actual columns from the datatable.

I get the columns using your method. Additionally, I had, but do not currently have, them defined in the DGV Tasks window at the upper corner of the DGV in the design window. Scroll bars are not disabled. Anyway, I still have not found a complete solution. I still get a display showing all of the rows defined in the query, but no content in any cell of any row. I know it is close, but cannot quite get over the hump.

Past this in to your form and click on the cells

Code:

---


Private Sub dgvSignList_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvSignList.CellClick

        If e.RowIndex > -1 AndAlso Not IsDBNull(dgvSignList.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then

            MessageBox.Show(CType(dgvSignList.Rows(e.RowIndex).Cells(e.ColumnIndex).Value, String))

        End If
    End Sub

---

See if they really empty.

I see where this might be going. And yes, the message box shows nothing. So based on this it appears to me that the DGV is actually just counting the records after the query is run and then just putting that number of empty records into the datagridview.

You will have to go tro your code, and tro the query, something is wrong.

You have this query:

Code:

---


SELECT intApproveID, intChangeID, strName, strJobTitle, strDept, strSignature, strLogin, strPassword, strMasterBaseType, blnRemoved
FROM tblChangeApprove
WHERE intChangeID = ?

---

and this code:

Code:

---


Me.TblChangeApproveTableAdapter.Fill(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove)
dgvSignList.DataMember = "TblChangeApprove"
dgvSignList.Columns(0).HeaderText = "ChangeID"
dgvSignList.Columns(1).HeaderText = "Name"
dgvSignList.Columns(2).HeaderText = "Job Title"
dgvSignList.Columns(3).HeaderText = "Department"

---

You DGV only show 4 columns, your query has 10.

The project i m using to test the code i sent you, is Visual Studio Community 2017, SQLServer 2014, Option Strict On.
And using SQLClient, not OleDb.
As for the query, if i use the ? mark, it gives me a error directly, so i assume you r not using or SQLClient or we in diferent versions.

OK, here is the query that resides in the table adapter and is called in my code:

Quote:

---

SELECT intApproveID, intChangeID, strName, strJobTitle, strDept, strSignature, strLogin, strPassword, strMasterBaseType, blnRemoved FROM tblChangeApprove

---

Remember that I previously, for simplicities sake began using the fill query.

Here is the code:

Quote:

---

Private Sub SubSignList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.TblChangeApproveTableAdapter.Fill(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove)
dgvSignList.DataSource = Me._MasterBase4_0ItemMasterDataSet
dgvSignList.DataMember = "TblChangeApprove"
dgvSignList.Columns(0).HeaderText = "Change ID"
dgvSignList.Columns(1).HeaderText = "Name"
dgvSignList.Columns(2).HeaderText = "Job Title"
dgvSignList.Columns(3).HeaderText = "Department"
End Sub

---

Those are the only columns that I want to show in the DGV. Would you be telling me that perhaps all of the columns should be defined, but those not being used just made visible = False?

Where did you made visible =false?
wheres that code?
For this you should be using a unbound datagridview, no columns in it.

In the code you sent header text like above, visisble true or false, or you simple exclude them from the fillById query(thats what i would do)

I did not do that. However, in the in the DataGridView tasks window with an Edit Columns selection, (located upper right corner of the DGV in the Design window) where you can define your columns (I only have the four columns I am interested in). Each column can be set to Visible = either True or False, as well as a number of other properties. This was not set in the code, but in the Design Window. With regard to which columns you want to exist in the DGV this can also be done, as you mentioned, in the query by only having those columns you are interested in be part of the SELECT command line.

By the way Mike, I really appreciate the time you are putting in on this. Even though I do not yet have what I am after this is helping my understanding a whole lot.

I just tested the same code using OleDB, its slitly diferent on the query, but it produces the same result.
As a sayed to you before:
Unbound datagrid view
NO COLUMNS IN IT

Columns will be added automaticly for you.
You just hide the ones you dont one, either by excluding them on the query, either by code.
Delete the columns in the datagrid view. Define its properties in the code after.

I understand that, I think. I had the Data bound set in the DGV Task Window. However, I got rid of that and put that into the code instead........

Whoa!!!! I just looked again in the DGV task Window and the Data Bound property was indeed set to the dataset. So I did several things. In the Task Window I changed the Data Bound setting to None, and disposed of all of the columns I had added there.
Attachment 152255I then went back to the query and did the following:

Quote:

---

SELECT intChangeID, strName, strJobTitle, strDept
FROM tblChangeApprove
WHERE intChangeID = ?

---

Here is the code as it is run now:

Quote:

---

Private Sub SubSignList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.TblChangeApproveTableAdapter.FillByChangeID(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove, glbintCRNum)
dgvSignList.DataSource = Me._MasterBase4_0ItemMasterDataSet
dgvSignList.DataMember = "TblChangeApprove"
dgvSignList.Columns(1).HeaderText = "Change ID"
dgvSignList.Columns(2).HeaderText = "Name"
dgvSignList.Columns(3).HeaderText = "Job Title"
dgvSignList.Columns(4).HeaderText = "Department"
End Sub

---

And here is the results as run in the application:

Attachment 152255

As can be seen, the data are all there and in the right place. The only remaining issue is why are the other fields showing. I have an idea I am going to try on that. Let me know what you see that needs to be done to finish this thorny issue.

That query wont work, you will need to create a new one for this propose, you using a tableadapter that has another Fill methode that contains the entire table, or you hide the columns setting the visible property to false.
the rest, you have the code samples i left you before.

OK, I have it all now. And I really thank you man for helping me through this.

1. In the DGV Task window (or properties window if you prefer). The Data Bound property must be set to (None). This turned out to be very, very important.
2. In the DGV task window you do not want to have any columns showing.
3. In the query it makes no difference whether the SELECT command has just the columns of interest or all of them.
4. The code should be as follows:

Quote:

---

Private Sub SubSignList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.TblChangeApproveTableAdapter.FillByChangeID(Me._MasterBase4_0ItemMasterDataSet.tblChangeApprove, glbintCRNum)
dgvSignList.DataSource = Me._MasterBase4_0ItemMasterDataSet
dgvSignList.DataMember = "TblChangeApprove"
dgvSignList.Columns(0).Visible = False
dgvSignList.Columns(1).HeaderText = "Change ID"
dgvSignList.Columns(1).Width = 100
dgvSignList.Columns(2).HeaderText = "Name"
dgvSignList.Columns(2).Width = 275
dgvSignList.Columns(3).HeaderText = "Job Title"
dgvSignList.Columns(3).Width = 275
dgvSignList.Columns(4).HeaderText = "Department"
dgvSignList.Columns(4).Width = 200
dgvSignList.Columns(5).Visible = False
dgvSignList.Columns(6).Visible = False
dgvSignList.Columns(7).Visible = False
dgvSignList.Columns(8).Visible = False
dgvSignList.Columns(9).Visible = False
End Sub

---

And here is the final result:

Attachment 152257

Oh, I went back to the original filter query that I was wanting to use. Again, it makes no difference how many columns you include in the SELECT command, as long as it includes the ones of interest.

Again, I really appreciate your helping me with this. You provided everything that was required and all I had to figure out is where I was going wrong. This definitely gets the job done.

You welcome.

Quote:

---

Originally Posted by gwboolean

By the way Mike, I really appreciate the time you are putting in on this. Even though I do not yet have what I am after this is helping my understanding a whole lot.

---

On a side note I see you are in Oregon City, I'm in Salem.

Karen,

Actually, I now live in The Couv (Vancouver). When I was getting ready to move from The OC though I did just a little looking in Salem. I really like it there and there were some really good deals on some really fine homes there. So I can assume you are pleased living there. The fact that you too are an Oregonian just moved you way, way up in my esteem. By the way, you seem to really get around these forums a lot.