Control Which Columns are Displayed in a DataGrid...

Date: November 27, 2002

This ASP.Net (VB) program allows you to select many, or all of the columns from a database table, but display only certain, specified columns in a DataGrid. It does this by setting the DataGrid AutoGenerateColumns property to false and then using the Columns Collection to specify the columns to be displayed in the DataGrid. This technique also allows you to specify "friendly" column names rather than the names contained in the database table. In this particular case (Customers table from the Northwind database) the table names are pretty good ("CompanyName", "ContactName", etc.). But you may run into colum names like "tbl_col1" which may be rather useless when viewed by a user of one of your programs.

The first code shown below is the .aspx page showing the technique for specifying the colums to be displayed as well as the column headings to be used in the DataGrid. A code-behind page shown later does the work of connecting to the database and executing the sql SELECT statement. In the asp:DataGrid code you will see that the AutoGenerateColumns property has been set to False. This prevents the DataGrid from displaying column names and requires you to do it yourself. This is accomplished by using the Columns Collection. In this code you specify the DataField for each column you want displayed in the grid, as well as the HeaderText property which contains the exact column heading text you want the grid to display for each selected DataField.

<%
' Program: DataGridColumns.aspx
' By: John Kilgo
' Date: November 27, 2002
' CodeBehind: DataGridColumns.aspx.vb
' Purpose: Selects all columns from a table, but display only selected columns in a DataGrid
%>

<%@ Page Inherits="DataGridColumns" Src="DataGridColumns.aspx.vb" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>DataGridColumns</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<BODY MS_POSITIONING="GridLayout">
<form id="Form1" runat="server">
<asp:DataGrid id="dataGrid"
              AutoGenerateColumns="False"
              runat="server">
  <Columns>
    <asp:BoundColumn HeaderText="Company Name" DataField="CompanyName" />
    <asp:BoundColumn HeaderText="Contact Name" DataField="ContactName" />
    <asp:BoundColumn HeaderText="Contact Title" DataField="ContactTitle" />
  </Columns>
</asp:DataGrid>
</form>
</BODY>
</HTML>

Next is the codebehind page which connects to the database and does a SELECT * from the Customers table of the Northwind database. This code is straight forward and has nothing to do with the displaying only certain columns in the DataGrid. All of that was handled by the .aspx page above.

Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient

Public Class DataGridColumns : Inherits Page
  Protected dataGrid As DataGrid

  Protected Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
    Dim sqlConn As SqlConnection
    Dim sqlCmd As SqlCommand
    Try
      sqlConn = New SqlConnection( _
        "Server=localhost;uid=sa;pwd=admin;database=northwind")
      sqlCmd = New SqlCommand("SELECT * FROM Customers WHERE " _
               & "(CompanyName LIKE 'A%') OR (CompanyName LIKE 'B%')", sqlConn)
      sqlConn.Open()
      dataGrid.DataSource = sqlCmd.ExecuteReader()
      dataGrid.DataBind()
    Catch ex As Exception
      Response.Write(ex.ToString & "<br>")
    Finally
      sqlConn.Close()
    End Try
  End Sub

End Class

Conclusion: With this program you have seen how to use Bound Columns and the Columns Collection to specify the exact columns you want displayed in a DataGrid, as well as how to specify the column heading text.

You may run this program by clicking Here. You may download this program by clicking > Here.