Whats happening: I have a table consisting of Name, ID, Comments. Each ID is unique unless the user submits more than 1 entry. At that point that user may then have 3 rows where the ID is the same to show these entries were all entered by 1 user and they belong together.
Below I'll post what I had done originally to separate these into separate datagridview's with buttons below each one that will eventually be used to work with the datagrid entries above it.
So this leads into 2 things I've trying to figure out and haven't had much luck through trial and error and Google researching.
1.) How can I make this put any entries in the datatable that have the same ID into the same datagridview rather than creating a separate datagrid for each one.
2.) Assuming the above can be done how would I, if possible, go about making the button generating below each one only correspond to the datagrid above it? Can each datagrid and button although created programatically be assigned a unique ID that allows each one to correspond to the other?
Dim dst As New DataSet
For i As Integer = 0 To DT.Rows.Count - 1
Dim intLastRow As Integer = i
If intLastRow > DT.Rows.Count - 1 Then intLastRow = DT.Rows.Count - 1
Dim dtbNew As DataTable = DT.Clone
dtbNew.TableName = DT.TableName
For j As Integer = i To intLastRow
dtbNew.ImportRow(DT.Rows(j))
Next j
dst.Tables.Add(dtbNew)
Next i
For Each table As DataTable In dst.Tables
Dim DGV As New DataGridView
Dim BTN As New Button
BTN.Text = "Show Details"
BTN.Width = 120
DGV.DataSource = table
DGV.Width = 800
DGV.Height = 80
FlowLayoutPanel1.Controls.Add(DGV)
FlowLayoutPanel1.Controls.Add(BTN)
Next
FlowLayoutPanel1.AutoScrollPosition = New Point(FlowLayoutPanel1.Top)
Errors
Dim DT As New DataTable
DT.Load(SQLMP)
Dim groups As IEnumerable(Of IGrouping(Of Integer, DataRow)) = DT.Select.GroupBy(Function(row) row.Field(Of Integer)("ORDER_ID"))
An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.DataSetExtensions.dll`
Additional information: Specified cast is not valid
You can do what you want with LinQ and lambda functions. Let's say you have a DataTable with all those entries mixed.
Do this and you will get rows grouped by ID:
Dim groups = DT.Select.GroupBy(Function(row) row("ID"))
From now on you can extract partial datatables from it
Dim partialTables = groups.Select(
Function(group)
Dim partialTable = group.CopyToDataTable
partialTable.TableName = group.Key
Return partialTable
End Function).ToList
Then you can query your list of tables for a specific ID, retrieve it and use it as your datasource:
YourDataGridView.DataSource = partialtables.FirstOrDefault(
Function(table) table.TableName = "123")
EDIT Below you can see a complete implementation of my suggestion:
Private DT As DataTable, partialTables As List(Of DataTable)
Private WithEvents CB As ComboBox, DGV As DataGridView
Private Sub q37459856()
DT = New DataTable
DT.Columns.Add("Name", GetType(String))
DT.Columns.Add("ID", GetType(Integer))
DT.Columns.Add("Commens", GetType(String))
DT.Rows.Add("aaaaaa", 1, "aaaaaa")
DT.Rows.Add("bbbbbb", 2, "bbbbbb")
DT.Rows.Add("cccccc", 1, "cccccc")
DT.Rows.Add("dddddd", 2, "dddddd")
DT.Rows.Add("eeeeee", 3, "eeeeee")
DT.Rows.Add("ffffff", 1, "ffffff")
DT.Rows.Add("gggggg", 2, "gggggg")
DT.Rows.Add("hhhhhh", 3, "hhhhhh")
DT.Rows.Add("iiiiii", 4, "iiiiii")
Dim groups As IEnumerable(Of IGrouping(Of Integer, DataRow)) = DT.Select.GroupBy(Function(row) row.Field(Of Integer)("ID"))
partialTables = groups.Select(
Function(group)
Dim partialTable = group.CopyToDataTable
'IGrouping(Of Integer, DataRow) implements IEnumerable(of DataRow)
'therefore, it implements Function .CopyToDataTable()
partialTable.TableName = group.Key.ToString("000")
Return partialTable
End Function).ToList
Dim IDs = New List(Of String)({"all"}.Concat(partialTables.Select(Function(t) t.TableName)))
Dim F As New Form
CB = New ComboBox With {.Dock = DockStyle.Top, .DataSource = IDs}
F.Controls.Add(CB)
DGV = New DataGridView With {
.Left = CB.Left,
.Top = CB.Top + CB.Height + 3,
.Width = CB.Width,
.Height = F.ClientSize.Height - CB.Top - CB.Height - 6,
.Anchor = AnchorStyles.Left + AnchorStyles.Top + AnchorStyles.Right + AnchorStyles.Bottom,
.AllowUserToAddRows = False}
F.Controls.Add(DGV)
F.Show()
End Sub
Sub CB_SelectedValueChanged(sender As Object, e As EventArgs) Handles CB.SelectedValueChanged
If CB.SelectedValue = "all" Then
DGV.DataSource = DT
Else
DGV.DataSource = partialTables.FirstOrDefault(Function(t) t.TableName = CB.SelectedValue)
End If
End Sub
EDIT #2 In order to provide a working solution for the question, below goes a version with similar logic but relying on For...Next iterations instead of Linq:
Private DT As DataTable, partialTables As List(Of DataTable)
Private WithEvents CB As ComboBox, DGV As DataGridView
Private Sub q37459856_withoutLinq()
DT = New DataTable
DT.Columns.Add("Name", GetType(String))
DT.Columns.Add("ORDER_ID", GetType(Integer))
DT.Columns.Add("Comments", GetType(String))
DT.Rows.Add("aaaaaa", 1, "aaaaaa")
DT.Rows.Add("bbbbbb", 2, "bbbbbb")
DT.Rows.Add("cccccc", 1, "cccccc")
DT.Rows.Add("dddddd", 2, "dddddd")
DT.Rows.Add("eeeeee", 3, "eeeeee")
DT.Rows.Add("ffffff", 1, "ffffff")
DT.Rows.Add("gggggg", 2, "gggggg")
DT.Rows.Add("hhhhhh", 3, "hhhhhh")
DT.Rows.Add("iiiiii", 4, "iiiiii")
Dim allORDER_ID As New List(Of Integer)
For Each dr In DT.Select
If Not allORDER_ID.Contains(dr("ORDER_ID")) Then
allORDER_ID.Add(dr("ORDER_ID"))
End If
Next
Dim partialTable As DataTable
partialTables = New List(Of DataTable)
For Each ORDER_ID In allORDER_ID
partialTable = DT.Clone
partialTable.TableName = ORDER_ID.ToString("000")
For Each dr In DT.Select("ORDER_ID=" & ORDER_ID)
partialTable.ImportRow(dr)
Next
partialTables.Add(partialTable)
Next
Dim IDs = New List(Of String)({"all"})
For Each partialTable In partialTables
IDs.Add(partialTable.TableName)
Next
Dim F As New Form
CB = New ComboBox With {.Dock = DockStyle.Top, .DataSource = IDs}
F.Controls.Add(CB)
DGV = New DataGridView With {
.Left = CB.Left,
.Top = CB.Top + CB.Height + 3,
.Width = CB.Width,
.Height = F.ClientSize.Height - CB.Top - CB.Height - 6,
.Anchor = AnchorStyles.Left + AnchorStyles.Top + AnchorStyles.Right + AnchorStyles.Bottom,
.AllowUserToAddRows = False}
F.Controls.Add(DGV)
F.Show()
End Sub
Sub CB_SelectedValueChanged(sender As Object, e As EventArgs) Handles CB.SelectedValueChanged
If CB.SelectedValue = "all" Then
DGV.DataSource = DT
Else
DGV.DataSource = partialTables.FirstOrDefault(Function(t) t.TableName = CB.SelectedValue)
End If
End Sub