-
Sep 26th, 2017, 12:44 PM
#1
Thread Starter
Fanatic Member
[RESOLVED] display DGV list from more than one table
I have the following DataGridView.
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?
-
Sep 26th, 2017, 01:20 PM
#2
Hyperactive Member
Re: display DGV list from more than one table
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
-
Sep 26th, 2017, 01:28 PM
#3
Re: display DGV list from more than one table
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.
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Sep 26th, 2017, 01:47 PM
#4
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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).
-
Sep 26th, 2017, 01:50 PM
#5
Hyperactive Member
Re: display DGV list from more than one table
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?
-
Sep 26th, 2017, 01:57 PM
#6
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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?
-
Sep 26th, 2017, 02:08 PM
#7
Hyperactive Member
Re: display DGV list from more than one table
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.
Last edited by Mike Storm; Sep 26th, 2017 at 02:13 PM.
-
Sep 26th, 2017, 02:15 PM
#8
Hyperactive Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 02:16 PM
#9
Hyperactive Member
Re: display DGV list from more than one table
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;
-
Sep 26th, 2017, 02:17 PM
#10
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 02:29 PM
#11
Hyperactive Member
Re: display DGV list from more than one table
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.
Last edited by Mike Storm; Sep 26th, 2017 at 02:32 PM.
-
Sep 26th, 2017, 02:30 PM
#12
Hyperactive Member
Re: display DGV list from more than one table
You havent explain where you get the condition from, or i didnt understand it...
-
Sep 26th, 2017, 02:34 PM
#13
Hyperactive Member
Re: display DGV list from more than one table
If this isn't it, post your code, wrape it in [ Code ] Your code [ /Code ]
it makes it easyer to read and understand.
-
Sep 26th, 2017, 02:37 PM
#14
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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:
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?
-
Sep 26th, 2017, 02:41 PM
#15
Hyperactive Member
Re: display DGV list from more than one table
If i understand, the table has data but is not displaying it in the datagridview, is that it?
-
Sep 26th, 2017, 02:45 PM
#16
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 02:47 PM
#17
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 02:48 PM
#18
Hyperactive Member
Re: display DGV list from more than one table
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?
-
Sep 26th, 2017, 02:49 PM
#19
Hyperactive Member
Re: display DGV list from more than one table
yeah, if you have the forecolor equals to the cellback colr, you wont see the results.
-
Sep 26th, 2017, 03:09 PM
#20
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
Mike, the code above is the complete code. But here it is again anyway:
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.
-
Sep 26th, 2017, 03:12 PM
#21
Hyperactive Member
Re: display DGV list from more than one table
why "FillByChangeID" and not just "Fill", where did you got that?
Are you sure that FillByChangeID query is returning data?
Last edited by Mike Storm; Sep 26th, 2017 at 03:16 PM.
-
Sep 26th, 2017, 03:20 PM
#22
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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:
SELECT intApproveID, intChangeID, strName, strJobTitle, strDept, strSignature, strLogin, strPassword, strMasterBaseType, blnRemoved
FROM tblChangeApprove
WHERE intChangeID = ?
-
Sep 26th, 2017, 03:22 PM
#23
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 03:27 PM
#24
Hyperactive Member
Re: display DGV list from more than one table
Mi query:
Code:
SELECT ID, Nome, Morada, CodigoPostal, Cidade, Pais, TVA, Telefone, Telemovel, Fax, Email, Pagamento, Credito, Bloquear FROM dbo.TBCNTS WHERE ID=@Vlr
-
Sep 26th, 2017, 03:33 PM
#25
Hyperactive Member
Re: display DGV list from more than one table
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
-
Sep 26th, 2017, 03:34 PM
#26
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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:
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"
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.
-
Sep 26th, 2017, 03:42 PM
#27
Hyperactive Member
Re: display DGV list from more than one table
-
Sep 26th, 2017, 03:45 PM
#28
Hyperactive Member
Re: display DGV list from more than one table
By the way, in your code you define data member, but you not seting datasource to the dataset.
-
Sep 26th, 2017, 04:24 PM
#29
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 04:35 PM
#30
Hyperactive Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 04:40 PM
#31
Hyperactive Member
Re: display DGV list from more than one table
This means that if you disable scrolbars, and you added the columns in designer, you wont see the actual columns from the datatable.
-
Sep 26th, 2017, 05:09 PM
#32
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 05:26 PM
#33
Hyperactive Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 05:31 PM
#34
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 05:48 PM
#35
Hyperactive Member
Re: display DGV list from more than one table
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.
Last edited by Mike Storm; Sep 26th, 2017 at 05:56 PM.
-
Sep 26th, 2017, 05:58 PM
#36
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
OK, here is the query that resides in the table adapter and is called in my code:
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:
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?
-
Sep 26th, 2017, 06:04 PM
#37
Hyperactive Member
Re: display DGV list from more than one table
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)
-
Sep 26th, 2017, 06:26 PM
#38
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 06:27 PM
#39
Thread Starter
Fanatic Member
Re: display DGV list from more than one table
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.
-
Sep 26th, 2017, 06:37 PM
#40
Hyperactive Member
Re: display DGV list from more than one table
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|