How Do I Add A New Column For Each Record From Table To Existing DatagridView
-
Please Help Me - Urgent How Do I Add A New Column For Each Record From A Table To An Existing DatagridView I have 3 tables. Each table has a common unique key field linked to the main table. I want to populate a datagridview with the contents of all the tables. For each record in the 2 sub tables, i want to add a new column in the datagridview. TABLE 1 TABLE 2 TABLE 3 Prd Dsc Qty Prd Cat Prd Loc 123 MyProd 10 123 Perishable 123 Fridge 1 123 Fridge 2 123 Fridge 3 DATAGRIDVIEW Prd Dsc Qty NewCol - Cat NewCol - Loc NewCol - Loc NewCol - Loc 123 MyProd 10 Perishable Fridge 1 Fridge 2 Fridge 3
-
Please Help Me - Urgent How Do I Add A New Column For Each Record From A Table To An Existing DatagridView I have 3 tables. Each table has a common unique key field linked to the main table. I want to populate a datagridview with the contents of all the tables. For each record in the 2 sub tables, i want to add a new column in the datagridview. TABLE 1 TABLE 2 TABLE 3 Prd Dsc Qty Prd Cat Prd Loc 123 MyProd 10 123 Perishable 123 Fridge 1 123 Fridge 2 123 Fridge 3 DATAGRIDVIEW Prd Dsc Qty NewCol - Cat NewCol - Loc NewCol - Loc NewCol - Loc 123 MyProd 10 Perishable Fridge 1 Fridge 2 Fridge 3
Which language? VB, C#? Which version of VS? 7, 8, 9? If v8 or later You could use a TableAdapter with ClearBeforeFill set to False provided that the primary keys of all tables have as you say "common and unique values"
-
Which language? VB, C#? Which version of VS? 7, 8, 9? If v8 or later You could use a TableAdapter with ClearBeforeFill set to False provided that the primary keys of all tables have as you say "common and unique values"
I am using VB.Net 2008 with an access database and i am using a TableAdapter to fill the datagrid. I am new to vb and do not now how to code for this. What i do have so far is that my program is working fine loading the datagridview from a single table. To link to the sub tables and add a column for every row of the sub table is my problem - i do not know how to code it. Here is my code so far: ' Iniatilize Connection cn = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source= " & Directory.GetCurrentDirectory & "\XXXDatabase.mdb") 'Populate Members DataGrid da = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM XXXDetails", cn) ds = New System.Data.DataSet da.Fill(ds) DataGridView1.DataSource = ds.Tables(0) cn.Close()
-
I am using VB.Net 2008 with an access database and i am using a TableAdapter to fill the datagrid. I am new to vb and do not now how to code for this. What i do have so far is that my program is working fine loading the datagridview from a single table. To link to the sub tables and add a column for every row of the sub table is my problem - i do not know how to code it. Here is my code so far: ' Iniatilize Connection cn = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source= " & Directory.GetCurrentDirectory & "\XXXDatabase.mdb") 'Populate Members DataGrid da = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM XXXDetails", cn) ds = New System.Data.DataSet da.Fill(ds) DataGridView1.DataSource = ds.Tables(0) cn.Close()
Mmm, Don't know why you're doing it in code when you can use the 'visual' designers to accomplish the same thing more easily. I would do the following: 1 add a new dataset to your project using 'Add New Item'. Choose a name for it, like 'dsProducts' 2 when you can see the new dataset in Solution Explorer, double click it to open the dataset designer. 3. Open Server Explorer and use the wizard to create a new data connection to your access database. 4 Once created you'll be able to navigate in VS to see the tables in the access db. 5 click and drag each of the tables from the server explorer to the dataset designer. This will automatically create the table adapters you need for all the tables. See if you can get this far, then come back here
-
Mmm, Don't know why you're doing it in code when you can use the 'visual' designers to accomplish the same thing more easily. I would do the following: 1 add a new dataset to your project using 'Add New Item'. Choose a name for it, like 'dsProducts' 2 when you can see the new dataset in Solution Explorer, double click it to open the dataset designer. 3. Open Server Explorer and use the wizard to create a new data connection to your access database. 4 Once created you'll be able to navigate in VS to see the tables in the access db. 5 click and drag each of the tables from the server explorer to the dataset designer. This will automatically create the table adapters you need for all the tables. See if you can get this far, then come back here
Thankyou, I have now done all 5 steps. What next?
-
Thankyou, I have now done all 5 steps. What next?
OK. 6 In the dataset designer click and select the main table (the one you've already used to fill the datagrid. 7 right-click on the TableAdapter row where it says Fill, GetData and choose 'Configure' 8 When a window appears you'll see the 'SELECT' statement for the query that VS uses to fill the table. 9 Click 'Query Builder' You'll now see a representation of your table showing all the fields in the top pane 10 Right click the top pane and choose 'Add table'. Do this for all the other tables you want to get data from 11 Now you need to link the 'Common ID fields' of all the tables. If you've set the primary keys correctly then these fields will be in bold type. To link the fields, click the key field in the main table and drag and drop it on to the corresponding field in one of the other tables. Repeat the process for the other tables. 12 Now click the check boxes next to the fields that contain the additional data you want to see in the data grid. When you do this you'll see the sql statement change. When you're done, click the OK button to close the Query Designer, then click Finish to close the TableAdapter Configuration window. 13 In the dataset designer, your main table should now contain the additional fields 14 Save the changes and close the dataset designer. Let me know when you've done the above.
-
Thankyou, I have now done all 5 steps. What next?
Forgot to ask... Do you have a one-to-one relationship between the main table and the sub-tables or are the relationships one-to-many, that is, each record in the main table links to more than one record in a sub table?
-
Forgot to ask... Do you have a one-to-one relationship between the main table and the sub-tables or are the relationships one-to-many, that is, each record in the main table links to more than one record in a sub table?
I'm not sure what it should be now. In the relationship diagram it says one to one. There is only one product-id in the main table. In the sub tables, the same product-id can occur many times, but with with different category groups for instance. So essentially, it is a one-to-one relationship. Don't forget i am trying to prevent multiple records in the main table when i add product categories, because the same product can occur in a number of categories. So the categories will be stored in a separate table with the same product-id. But when i display the datagrid, i want to only display one line per product-id, but with each category in a separate column.
-
I'm not sure what it should be now. In the relationship diagram it says one to one. There is only one product-id in the main table. In the sub tables, the same product-id can occur many times, but with with different category groups for instance. So essentially, it is a one-to-one relationship. Don't forget i am trying to prevent multiple records in the main table when i add product categories, because the same product can occur in a number of categories. So the categories will be stored in a separate table with the same product-id. But when i display the datagrid, i want to only display one line per product-id, but with each category in a separate column.
Mmm... I figured you might have a many-to-one relationship somewhere. Unfortunately the datagridview displays its data on a row-by-row basis so as soon as you assign a datasource it will display the data from the table, row by row. It's possible to show hierarchical data in the datagrid view by having related records appear as part of the main record but not as extra/additional columns. Instead you'll have extra rows beneath the parent row but these rows will be 'contained' within and below the parent row. If you've managed to do everything I've said in the previous posts then all that's left to do now is to assign the datasource to the datagridview. Open the form that has the datagridview on it in design view. Select the datagridview and look at it's top-right corner, you should see a 'smart tag' appear. Click it and use 'Choose Data Source' to navigate to select the main table in your dataset. VS should then set up the data source automatically for you and you should be able to see the records displayed in the grid when you run your app. To see the datagridview display records hierarchically you need to assign the datagridview's datasource to be the object that represents the relationship between the main table and the sub-table(s). I wish I could see your tables structure and relationships then I'd be able to advise you better.
-
Mmm... I figured you might have a many-to-one relationship somewhere. Unfortunately the datagridview displays its data on a row-by-row basis so as soon as you assign a datasource it will display the data from the table, row by row. It's possible to show hierarchical data in the datagrid view by having related records appear as part of the main record but not as extra/additional columns. Instead you'll have extra rows beneath the parent row but these rows will be 'contained' within and below the parent row. If you've managed to do everything I've said in the previous posts then all that's left to do now is to assign the datasource to the datagridview. Open the form that has the datagridview on it in design view. Select the datagridview and look at it's top-right corner, you should see a 'smart tag' appear. Click it and use 'Choose Data Source' to navigate to select the main table in your dataset. VS should then set up the data source automatically for you and you should be able to see the records displayed in the grid when you run your app. To see the datagridview display records hierarchically you need to assign the datagridview's datasource to be the object that represents the relationship between the main table and the sub-table(s). I wish I could see your tables structure and relationships then I'd be able to advise you better.
I looked at my database again. Corrected some errors. The sub tables are one-to-many relationships with the main table. It does not look as if what i want to achieve will work. I do not want the datagridview to display more than one record per item. What i am also doing is exporting my datagrid to excel. Here is the code (It exports the main table perfectly). If you could perhaps help me modify my code to ADD the additional columns from the sub table records to the excel spread sheet, that will also solve my problem because it will be used mainly for pivots and printing. The vb is merely a front-end for capturing and displaying the data and i thought it would be easy enough to just add the extra columns to the datagridview, because my export to excel already works very well. Thanks very much for your help so far. Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click 'Verfying the datagridview has data or not If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then Exit Sub End If 'Creating dataset to export ds = New System.Data.DataSet 'Add table to dataset ds.Tables.Add() 'Add column to the table For i As Integer = 0 To DataGridView1.ColumnCount - 1 ds.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText) Next 'Add rows to the table Dim dr1 As DataRow For i As Integer = 0 To DataGridView1.RowCount - 1 dr1 = ds.Tables(0).NewRow For j As Integer = 0 To DataGridView1.Columns.Count - 1 dr1(j) = DataGridView1.Rows(i).Cells(j).Value Next ds.Tables(0).Rows.Add(dr1) Next Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass Dim wBook As Microsoft.Office.Interop.Excel.Workbook Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet wBook = excel.Workbooks.Add() wSheet = wBook.ActiveSheet() Dim dt As System.Data.DataTable = ds.Tables(0) Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex As Integer = 0 Dim rowIndex As Integer = 0 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(1, colIndex) = dc.ColumnName Next For Each dr In dt.Rows rowIndex = rowIndex + 1 colIndex = 0
-
I looked at my database again. Corrected some errors. The sub tables are one-to-many relationships with the main table. It does not look as if what i want to achieve will work. I do not want the datagridview to display more than one record per item. What i am also doing is exporting my datagrid to excel. Here is the code (It exports the main table perfectly). If you could perhaps help me modify my code to ADD the additional columns from the sub table records to the excel spread sheet, that will also solve my problem because it will be used mainly for pivots and printing. The vb is merely a front-end for capturing and displaying the data and i thought it would be easy enough to just add the extra columns to the datagridview, because my export to excel already works very well. Thanks very much for your help so far. Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click 'Verfying the datagridview has data or not If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then Exit Sub End If 'Creating dataset to export ds = New System.Data.DataSet 'Add table to dataset ds.Tables.Add() 'Add column to the table For i As Integer = 0 To DataGridView1.ColumnCount - 1 ds.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText) Next 'Add rows to the table Dim dr1 As DataRow For i As Integer = 0 To DataGridView1.RowCount - 1 dr1 = ds.Tables(0).NewRow For j As Integer = 0 To DataGridView1.Columns.Count - 1 dr1(j) = DataGridView1.Rows(i).Cells(j).Value Next ds.Tables(0).Rows.Add(dr1) Next Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass Dim wBook As Microsoft.Office.Interop.Excel.Workbook Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet wBook = excel.Workbooks.Add() wSheet = wBook.ActiveSheet() Dim dt As System.Data.DataTable = ds.Tables(0) Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex As Integer = 0 Dim rowIndex As Integer = 0 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(1, colIndex) = dc.ColumnName Next For Each dr In dt.Rows rowIndex = rowIndex + 1 colIndex = 0
You still end up with the same problem, trying to convert row data into column data and matching it to the main table data. What version of MS Access are you using for your database? If it's 2003 or later then one course of action could be to create a Crosstab Query in MS Access for each of the tables that need the data rows converting into columns. You could then create a new Select Query to combine the data from the main table with that from the crosstab queries and then reference the Select query in your vb.net app instead of the main table. You would then not need to change your code to export to Excel.