How to select visible columns in Datagridview bound to DataTable

Question

I have these fields on Customer DataTable: ID,title,Name,Addrs,email,Fax and this code to bind the DataGridView:

Dim sql As String = "SELECT * FROM Customers"
Dim daHeader As New SqlDataAdapter(sql, Conn)
daHeader.Fill(dsNota, "Customers")
dgvHeader.DataSource = dsNota.Tables("Customers")

How do I view title,name,addrs data in DataGridView without changing the SQL string to:

"SELECT title,Name,Addrs FROM Customer"

You should avoid `SELECT *` syntax. Always specify the columns you need in every query. — Victor Zakharov, May 23 '13 at 14:04
in my case,,there is a column which does not need to displayed,,but thanks for your suggestion.. — Lucky, May 25 '13 at 01:22

score 2 · Accepted Answer · edited May 23 '17 at 12:14

So if you don't want to modify your query string (as @Neolisk noticed this is generally a bad practice to use Select * but this is another debat), and so you get more columns than what you want to display:

Solution1 (Ideal if there are a lot of columns in datatable and you want to display just some of them)

You need to set AutoGenerateColumns property to false. Default is True, so DataGridView will create a column for all columns in the datatable.
Then, you add a DatagridiviewColumn for each column you want to display.
To avoid to have to add a celltemplate for the DatagriviewColumn (see this) you would prefer to add a strongly typed column (DataGridViewTextBoxColumn for example in order to display String values). In order to bind the column with the source, you set the DataPropertyName property, that needs to match with the ColumnName of the column inDataTable.

So code would be:

dgvheader.AutoGenerateColumns = False
dgvHeader.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Title", .DataPropertyName = "title"}) 
dgvHeader.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Name", .DataPropertyName = "Name"}) 
dgvHeader.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Adresse", .DataPropertyName = "Addrs"}) 
dgvHeader.DataSource = dsNota.Tables("Customers")

Solution2 (Ideal if there are a lot of columns in datatable, and you want to Hide just some of them, and so you want to keep the benefit of AutoGenerateColumns)

Set AutoGenerateColumns property to true (or do nothing since default is True)

Hook the DataGridView.DataBindingComplete Event in order to hide some columns autogenerated:

Private Sub dataGridView1_DataBindingComplete(ByVal sender As Object, ByVal e As DataGridViewBindingCompleteEventArgs) Handles dgvHeader.DataBindingComplete   
    With dgvHeader
        .Columns("Fax").Visible = False
    End With
End Sub

Thanks Chris,,I'll try your suggestion,, – Lucky May 25 '13 at 01:25 — Lucky, May 25 '13 at 01:25

score 0 · Answer 2 · answered May 23 '13 at 13:12

0

If the gridview autogeneratecolumns attribute is set to true change it to false and then do as Raimond suggested. Example:

<asp:GridView ID="gvSearchResults" runat="server" AutoGenerateColumns="False">
        <Columns>
                <asp:BoundField DataField="title" HeaderText="Title" />
        </Columns>
</asp:GridView>

answered May 23 '13 at 13:12

Ashley Webb

68
1
8

2

The question does not have a reference to ASP. I would assume WinForms based on class name `DataGridView`. – Victor Zakharov May 23 '13 at 13:59

score 0 · Answer 3 · answered Jun 29 '18 at 14:02

I know this post goes way back, but I had the same problem in C# and this is how I solved it which works really well.

1 - Build a DataSet with your SQL query

     private void LoadDS()
     {
         // this method gets data from my database
         // DS is a DataSet in the properties of my form
         DS = LoadData();
     }

2 - Get the DataView filtered the way you want

For this I use the RowFilter property of the DataView. I created a GetFiltersToString() method that formats every filter control I have in a string that matches the RowFilter syntax (more information about the syntax here, and msdn definition of RowFilter here)

    public void RefreshDGV()
    { 
        // Get the corresponding dataview
        DV = new DataView(DS.Tables[0], rowFilter, "SORTINGCOLUMN Desc", DataViewRowState.CurrentRows);
        // Display it in a datagridview
        DGV.DataSource = DV;
    }

I find that this solution allows user to change the filtering much more easily.

score -1 · Answer 4 · answered May 22 '13 at 09:52

-1

You have to explicitly add the columns to your grid.

answered May 22 '13 at 09:52

Raimond Kuipers

1,146
10
18

I think this should give the information: http://code.msdn.microsoft.com/CSASPNETGridView-5b16ce70 – Raimond Kuipers May 22 '13 at 10:28

How to select visible columns in Datagridview bound to DataTable

4 Answers4

Linked