|
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"
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
wSheet.Columns.AutoFit()
Dim strFileName As String = "C:\AAA\MemberList.xls" 'This will become a textbox later
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True
End Sub
|
|
|
|
|
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.
|
|
|
|
|
please help...
how can i open or activate window to launch excel using vb.net??
thanks...
jyn
|
|
|
|
|
|
Do you want to automate Excel or just start it as a separate process/application?
|
|
|
|
|
thanks for replies..
i need is to make the excel application visible to the user after populate the data on it..
or can download it after clicking the export button...
any idea?
thanks again...
jyn
|
|
|
|
|
You mentioned 'download it after clicking', so are you creating a web page/web application or a windows forms application?
|
|
|
|
|
Hi Guys...
i'm new to vs2008 express, after creating a simple app for testing, i wanted to create the setup package for installing....
i dont see the option for creating the setup package as in the case of vs2005...
does anyone know how to create this package or maybe the menu option that starts the wizard?
thanks in advance
Anoop Brijmohun
|
|
|
|
|
VS 2008 Express does not have setup projects. You will have to create your own (from scratch), find a third party solution, or dish out for a paid version of VS 2008.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Check out Inno Setup[^]. It's small, fast, free, and easy to learn.
|
|
|
|
|
I'll second the use of Inno Setup 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
thanks, much appreciated!
|
|
|
|
|
In Winforms, a TextBox has a MultiLine Property as well as an AcceptsReturn property.
But when used in a DataGridView via the DataGridViewTextBoxColumn, there are no such properties.
So at the moment, my DataGridView has a label, informing users to press Shift+Enter for a new line.
There must be a better way to accept the Return key for a new line in a textbox within a DataGridView.
modified on Thursday, October 2, 2008 7:09 AM
|
|
|
|
|
Assuming a datagridview control named dgvRows:
Private Sub dgvRows_KeyDown( _
ByVal sender As Object, _
ByVal e As System.Windows.Forms.KeyEventArgs) _
Handles dgvRows.KeyDown
' Detect the Enter key
If e.KeyCode = Keys.Return Then
' Determine the currently selected column
Select Case dgvRows.SelectedCells(0).ColumnIndex
Case 0
' Determine the currently selected row
Select Case dgvRows.SelectedCells(0).RowIndex
Case 0
' Code to manipulate the selected cell at (0,0)
Case 1
' Code to manipulate the selected cell at (0,1)
'
'
Case 99
' Etc
End Select
Case 1
'
'
Case 99
End Select
End If
End Sub
|
|
|
|
|
Hey George
Thanks for the answer but that doesn't work, I've tried it with the Keypress event as well.
The problem is that that event only fires when the entire cell is selected, but if the focus is on the control inside the cell, - in this case, the TextBox - the event doesn't fire for any keys pressed.
If you got this to work I would appreciate some more help.
Kind Regards
|
|
|
|
|
I love a worthwhile challenge. It's taken all morning, but, I think I've got it. The key is overriding the ProcessCmdKey method in the DataGridView class so that the Enter key can be detected and Ctrl^Enter substituted.
Step 1 is to add an extended DataGridView to your project where the override takes place.
Public Class myDataGridView
Inherits DataGridView
Protected Overrides Function ProcessCmdKey( _
ByRef msg As System.Windows.Forms.Message, _
ByVal keyData As System.Windows.Forms.Keys) _
As Boolean
'** When the Enter key is pressed in the textbox column,
'** substitute Ctrl^Enter
If keyData = Keys.Enter Then
SendKeys.SendWait("^{ENTER}")
Return True
Else
Return MyBase.ProcessDialogKey(keyData)
End If
End Function
End Class
Unfortunately, this means the DataGridView control will have to be completely built and positioned on the form at runtime to take advantage of the override. Here's the simple code I used for testing. You will have to flesh it out for your purposes.
Public Class Form1
Dim dgv As New myDataGridView
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
'** Add the datagridview to the form
Controls.Add(dgv)
dgv.Left = 10
dgv.Top = 10
'** Add a textbox column
Dim dgvCol As New DataGridViewTextBoxColumn
With dgvCol
.HeaderText = "Text Column"
.Name = "chdrTextCol"
.Width = 130
End With
dgv.Columns.Add(dgvCol)
dgvCol = Nothing
'** Add a row
dgv.Rows.Add()
End Sub
End Class
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
