[RESOLVED] DataGridView creation based on user choice of columns

[crater]

I have an app that starts up with a datagridview on a Winform. The DGV show the user typical generic data so they may make a choice on which record thay want to choose. IE: Fname,Lname,StreetNo ect.. on full row select. No problem so far.

I want the columns that are displayed to the user to be choosen by the user from a listbox on another form and then saved to my Access db. Still no problems

This is where I'm stuck.
I placed a bound DGV on my form and hide all the columns, then just change the visible property in a loop of the dgv columns collection, if the header text prop. matches my datafield that I read back.

This method sucks as I have no control over the column index. The columns are created and they are filled with data, but that's about all I can say about this method.

I've thought of creating a temp table then binding the DGV to it, but again the index would be only as good as the order the user choose to pick the fields and the order they're saved in.

Here's how I'm currently do this, but there has to be a better way.

 Private Sub CreateDGV()
       
        ' Input string from datatable
        Dim value As String = UserRow.FieldName
        ' Split on semicolon.
        Dim arr() As String = value.Split(";")
        ' Convert to List.
        Dim vals As List(Of String) = arr.ToList()
        ' Add the column to the dgv
        For Each val As String In vals

            For Each Col As DataGridViewColumn In Me.BidDataGridView.Columns
                Dim ColName As String = Col.HeaderText
                If ColName = val Then
                    Col.Visible = True
                    Me.BidDataGridView.Columns(0).Visible = False 'This is the only way I could find to hide column(0)
                End If
            Next
        Next
    End Sub

Any ideas out there how to do this differently?

[kebo]

I have a form for my users that has a has 2 listboxes; one for columns that are not shown (hiddenColumns) and one for columns that are shown (shownColumns). Double clicking an item in either list moves the item to the other list. There are also buttons that allow the user to reorder the items in the shownColumns Listbox. When the form is closed, I build a List(of String) using the items in the shownColumns list box and I add the item in the order they appear in the list. Then when the datagrid form is shown, I use the list(of string) to hide or show the columns in the order choosen by the user....

The code to update the grid looks like this...

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

'populate the grid with some columns....
        With Me.dGv.Columns
            Dim c As New DataGridViewTextBoxColumn()
            c.Name = "c1"
            .Add(c)
            c = New DataGridViewTextBoxColumn
            c.Name = "c2"
            .Add(c)
            c = New DataGridViewTextBoxColumn
            c.Name = "c3"
            .Add(c)
            c = New DataGridViewTextBoxColumn
            c.Name = "c4"
            .Add(c)
            c = New DataGridViewTextBoxColumn
            c.Name = "c5"
            .Add(c)

        End With
'create the list of visible columns...
        Dim columnList As New List(Of String)
        columnList.Add("c2")
        columnList.Add("c5")
        columnList.Add("c1")

        Call updateGrid(columnList)

    End Sub

    Private Sub updateGrid(columnList As List(Of String))
        For Each col As DataGridViewColumn In dGv.Columns

            If columnList.Contains(col.Name) Then
                col.Visible = True
                col.DisplayIndex = columnList.IndexOf(col.Name)

            Else

                col.Visible = False
            End If
        Next
    End Sub

kevin

[crater]

Kebo,
Yea I also have two listboxs. One with available fields(the entire table) and one with columns to be displayed. 2 buttons to move fields back and forth. I guess I didn't think about a couple more button to change the order of the 2nd listbox.

So the user has to choose which columns are displayed each time they want to view the datagridview? or are you saving the list

[kebo]

I save the list.

[kareninstructor]

Hello,

Try this project

[crater]

Kevin,
Sorry for the delay, having quite a time with Chrome browser.

I'm developing with VS2010 with 4.0 framework. Looks like your project was developed with a later version of VS and the 4.5.2 framework.

At any rate, what I was able to open, I looks like your changing the columns visible property based on if the columns name matches what is read out of the text file, is that correct?
I seen something in there about the index also and I will investigate further today, now that I'm on a 32bit machine.
Last night was a nightmare trying to open your project on a 64bit Vista box running 4.0 framework and VS2010. Even d/l 4.5.2 but the 64 bit thing got in the way trying to run the .exe out of the bin/debug

[crater]

ok so I almost got it work the way I want.
I have 2 more questions:
1) I have a fieldname(Archived) that appears in my list of string that has been saved to my db. The string is not in the last one yet the column with the same headertext is placed last. Any ideas why. I can move it up in the list, but it doesn't position correctly in the DGV.

Please see image:

2) Now that I can add fields and move them around. When I save the data (save button) how do I get the DGV to automatically hide or show the columns I changed. Right now I have to exit the application and restart to show the correct columns.

[crater]

Here is my code to build the list and then to deal with the DGV columns

Private Sub BuildColumnList()

        ' Input string from datatable
        Dim value As String = UserRow.FieldName

        ' Split on colon.
        Dim arr() As String = value.Split(";")

        ' Convert to List.
        Dim vals As List(Of String) = arr.ToList()

        ' Add the column to the dgv
        For Each val As String In vals

            ColumnList.Add(val)

        Next
    End Sub

    Private Sub CreateDGV(columnList As List(Of String))

        For Each col As DataGridViewColumn In BidDataGridView.Columns

            If columnList.Contains(col.HeaderText) Then
                col.Visible = True
                col.DisplayIndex = columnList.IndexOf(col.HeaderText)
            Else
                col.Visible = False
            End If
        Next

    End Sub

[kebo]

columnList needs to be an exposed (public) member of the options form. When you close the options form, grab the list, call a routine that will set the columns (pass the list to that routine) and save the list. When you need to show the options again, fetch the list, pass it to the options form, when the form closes, fetch the list....
kevin

[kareninstructor]

Kevin,
Sorry for the delay, having quite a time with Chrome browser.

I'm developing with VS2010 with 4.0 framework. Looks like your project was developed with a later version of VS and the 4.5.2 framework.

At any rate, what I was able to open, I looks like your changing the columns visible property based on if the columns name matches what is read out of the text file, is that correct?
I seen something in there about the index also and I will investigate further today, now that I'm on a 32bit machine.
Last night was a nightmare trying to open your project on a 64bit Vista box running 4.0 framework and VS2010. Even d/l 4.5.2 but the 64 bit thing got in the way trying to run the .exe out of the bin/debug

Actually the project was originally done in VS2010 Framework 4 but all I have now is VS2012 and VS2013. In any event if you created a blank project, remove the form then add my files in it will work.

[crater]

I need some help here. I tryng to follow along with Kebo's logic.

'Make ColumnList a Public Member of Option Form

 Public Property NewColumnList As New List(Of String)

Private Sub frmOptions_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

        'Grab the list 
        CreateFieldList()
        BuildColumnList(FieldList)

        'Call routine on frmSearch that creates columns
        frmSearch.CreateDGVColumns(NewColumnList) 

    End Sub

Code to build string from my listbox

Private Property FieldList As String

 Private Function CreateFieldList()

        'Create a list of Fields to display in the search form that will be saved to db

        If lstboxUIFieldNames.Items.Count > 0 Then
            Dim StrArray(lstboxUIFieldNames.Items.Count)
            For item = 0 To lstboxUIFieldNames.Items.Count - 1
                StrArray(item) = lstboxUIFieldNames.Items(item).ToString()
            Next
            FieldList = String.Join(";", StrArray).Trim(";")
        Else
            FieldList = String.Empty
        End If
        Return FieldList

    End Function

Code to build list(of String) out of string

Private Sub BuildColumnList(FieldList)

        ' Input string from datatable
        Dim value As String = UserRow.FieldName

        ' Split on colon.
        Dim arr() As String = value.Split(";"c)

        ' Convert to List.
        Dim vals As List(Of String) = arr.ToList()

        ' Add the Column Name to the list
        For Each val As String In vals

            NewColumnList.Add(val)

        Next

    End Sub

And finally the code to create the columns. This is in frmSearch

Private ColumnList As New List(Of String)

 Public Sub CreateDGVColumns(columnList As List(Of String))

        'Hide all coulums in the DGV
        For Each Col As DataGridViewColumn In Me.BidDataGridView.Columns
            Col.Visible = False
        Next

        For Each col As DataGridViewColumn In BidDataGridView.Columns

            If columnList.Contains(col.HeaderText) Then
                col.Visible = True
                col.DisplayIndex = columnList.IndexOf(col.HeaderText)
            Else
                col.Visible = False
            End If
        Next

    End Sub

This all seems to work except my datagridview does not re-populate to columns even if I re-hide them.

Any ideas

[crater]

I think I have determined that my problem has something to do with the createDVGColumns sub. I created another sub, named it CreateNewDGVColumns(FieldList as(List of String)). To make sure I wasn't having variables messed with causing the problem. Change the call in frmOptions to CreateNewDGVColumns(ColumnList as List(of String)). I included a line to hide all the dgv columns and commented out the other code. The code to hide the columns doesn't even work, and I know it does in the Form Load method, it is the exact same code.
So why can't I just change the visible property to false and true if there in my list? Almost like the dgv has to redraw or something like that. I read on MSDN about DGV.columns(xxx).visible property and it seems like everything I'm doing is correct \, but it just doesn't hide or show the columns that I had changed in the frmOptions.

Sorry for being long winded but I'm getting very frustrated at this point. Stepping through code (f8) and everything seems to execute properly. Yet it doesn't work.

[crater]

Finally got it to work. Thanks for the example Kebo and Kevin.
My problem had to do with how I was creating the list(of string) in the options form.

So here's the working code in case anyone else should need this.

Private Sub SelectFieldsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SelectFieldsToolStripMenuItem.Click

        Dim Options As frmOptions
        Options = New frmOptions(OneDataDataSet, ErvDataDataSet, UserName, UserID, UserRow, Me.Name)

        Dim result As DialogResult = Options.ShowDialog(Me)

        If result = Windows.Forms.DialogResult.Yes Then

            'Hide all coulums in the DGV
            For Each Col As DataGridViewColumn In Me.BidDataGridView.Columns
                Col.Visible = False
            Next

            Try
                For Each col As DataGridViewColumn In Me.BidDataGridView.Columns

                    If Options.ColList.Contains(col.Name) Then
                        col.Visible = True
                        col.DisplayIndex = Options.ColList.IndexOf(col.Name)
                    Else
                        col.Visible = False
                    End If
                Next

            Catch ex As Exception
                MsgBox(ex)
            End Try

        End If

    End Sub

and the code in the Options form. Any question please ask.

Public Class frmOptions
    Private UserRow As ErvDataDataSet.UserOptionsRow
    Private IDFieldRow As ErvDataDataSet.IDFieldsRow
    Private Calling_Form As String

    Private NewColumnList As New List(Of String)
    Public Property ColList() As List(Of String)
        Get
            Return NewColumnList
        End Get
        Set(ByVal value As List(Of String))

        End Set
    End Property

    Sub New(ByVal OneDs As OneDataDataSet, ByVal ErvDs As ErvDataDataSet, ByVal Name As String, _
             ByVal StaffID As Integer, ByVal Urow As DataRow, FormName As String)

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

        ' Add any initialization after the InitializeComponent() call.
        OneDataDataSet = OneDs
        ErvDataDataSet = ErvDs
        UserRow = Urow

        BidBindingSource.DataSource = OneDs
        IDStaffBindingSource.DataSource = OneDs
        IDStatusBindingSource.DataSource = OneDs
        IDProgramBindingSource.DataSource = OneDs
        Calling_Form = Microsoft.VisualBasic.Mid(FormName, 4)

        UserOptionsBindingSource.DataSource = ErvDs
        IDFieldsBindingSource.DataSource = ErvDs

    End Sub

    Private Sub btnSetRowColor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSetRowColor.Click

        If GridColorDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            UserRow.AlternatingRowColor = GridColorDialog.Color.ToArgb.ToString
        End If

    End Sub

    Private Sub frmOptions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.lstboxUIFieldNames.Items.Clear()

        Try
            'TODO: This line of code loads data into the 'ErvDataDataSet.IDFields' table. You can move, or remove it, as needed.
            Me.IDFieldsTableAdapter.Fill(Me.ErvDataDataSet.IDFields)

            ' Dim IDFieldRow As ErvDataDataSet.IDFieldsRow
            IDFieldRow = CType(CType(Me.IDFieldsBindingSource.Current, DataRowView).Row, ErvDataDataSet.IDFieldsRow)

        Catch ex As Exception
        End Try

        If UserRow.IsProgramNull Then
            UserRow.Program = ""
        Else
            ProgramComboBox.Text = UserRow.Program
        End If

        StaffNameComboBox.Text = UserRow.StaffName
        StatusComboBox.Text = UserRow.StatusID

        If UserRow.Archived = True Then
            ArchivedComboBox.Text = "Yes"
        ElseIf UserRow.Archived = False Then
            ArchivedComboBox.Text = "No"
        Else
            ArchivedComboBox.Text = "All"
        End If

        FillDBField_ListBox()
        FillUIFiled_ListBox_Default()

    End Sub
    Private Sub Save_User_Settings()

        Me.Validate()
        UserOptionsBindingSource.EndEdit()
        UserOptionsTableAdapter.Update(ErvDataDataSet)

    End Sub


    Private Sub FillDBField_ListBox()

        Try
            'Read from then IDField table

            ' Input string from datatable
            Dim value As String = IDFieldRow.FieldName

            ' Split on colon.
            Dim arr() As String = value.Split(";"c)

            ' Convert to List.
            Dim vals As List(Of String) = arr.ToList()

            ' Add the column to the dgv
            For Each val As String In vals

                lstboxDBFieldName.Items.Add(val)

            Next
        Catch ex As Exception
            MsgBox(ex)
        End Try

    End Sub

    Private Sub FillUIFiled_ListBox_Default()

        'Read from the UserOptions Table

           ' Input string from datatable
        Dim value As String = UserRow.FieldName

        ' Split on colon.
        Dim arr() As String = value.Split(";"c)

        ' Convert to List.
        Dim vals As List(Of String) = arr.ToList()

        ' Add the column to the dgv
        For Each val As String In vals

            lstboxUIFieldNames.Items.Add(val)

        Next

    End Sub


    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnRemoveField_Click(sender As System.Object, e As System.EventArgs) Handles btnRemoveField.Click

        Dim selectedItems = (From i In lstboxUIFieldNames.SelectedItems).ToArray()

        For Each selectedItem In selectedItems
            lstboxDBFieldName.Items.Add(selectedItem)
            lstboxUIFieldNames.Items.Remove(selectedItem)
        Next
    End Sub

    Private Sub btnAddField_Click(sender As System.Object, e As System.EventArgs) Handles btnAddField.Click

        Dim selectedItems = (From i In lstboxDBFieldName.SelectedItems).ToArray()

        For Each selectedItem In selectedItems
            lstboxUIFieldNames.Items.Add(selectedItem)
            lstboxDBFieldName.Items.Remove(selectedItem)
        Next
    End Sub


    Private Fieldlist As String

    Private Function CreateFieldList()

        'Create a list of Fields to display in the search form that will be saved to db

        If lstboxUIFieldNames.Items.Count > 0 Then
            Dim StrArray(lstboxUIFieldNames.Items.Count)
            For item = 0 To lstboxUIFieldNames.Items.Count - 1
                StrArray(item) = lstboxUIFieldNames.Items(item).ToString()
            Next
            Fieldlist = String.Join(";", StrArray).Trim(";"c)
        Else
            Fieldlist = String.Empty
        End If
        Return Fieldlist

    End Function


    Private Sub BuildColumnList(FieldList)

        ' Input string from datatable
        Dim value As String = FieldList

        ' Split on colon.
        Dim arr() As String = value.Split(";"c)

        ' Convert to List.
        Dim vals As List(Of String) = arr.ToList()

        ' Add the Column Name to the list
        For Each val As String In vals

            NewColumnList.Add(val)

        Next

    End Sub
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        CreateFieldList()
        BuildColumnList(Fieldlist)


        'Set values in Dataset
        UserRow.FieldName = FieldList
        UserRow.Program = Me.ProgramComboBox.SelectedValue
        UserRow.SearchIn = Me.SearchInComboBox.SelectedItem
        UserRow.Archived = Me.ArchivedComboBox.SelectedItem
        UserRow.StaffID = Me.StaffNameComboBox.SelectedValue
        UserRow.FormName = Calling_Form

        'Save to db
        Me.Validate()
        Me.UserOptionsBindingSource.EndEdit()
        Me.ErvTableAdapterManager.UpdateAll(Me.ErvDataDataSet)
        Me.UserOptionsBindingSource.DataSource = ErvDataDataSet.UserOptions



    End Sub




    Private Sub btnUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUp.Click

        'Make sure our item is not the first one on the list.  

        If lstboxUIFieldNames.SelectedIndex > 0 Then

            Dim I = lstboxUIFieldNames.SelectedIndex - 1

            lstboxUIFieldNames.Items.Insert(I, lstboxUIFieldNames.SelectedItem)

            lstboxUIFieldNames.Items.RemoveAt(lstboxUIFieldNames.SelectedIndex)

            lstboxUIFieldNames.SelectedIndex = I

        End If

    End Sub

    Private Sub btnDn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDn.Click

        'Make sure our item is not the last one on the list.  

        If lstboxUIFieldNames.SelectedIndex < lstboxUIFieldNames.Items.Count - 1 Then

            'Insert places items above the index you supply, since we want  

            'to move it down the list we have to do + 2  

            Dim I = lstboxUIFieldNames.SelectedIndex + 2

            lstboxUIFieldNames.Items.Insert(I, lstboxUIFieldNames.SelectedItem)

            lstboxUIFieldNames.Items.RemoveAt(lstboxUIFieldNames.SelectedIndex)

            lstboxUIFieldNames.SelectedIndex = I - 1

        End If

    End Sub



End Class