Formatting ASP.NET DataGrid's Data

Question:

The data in database is as follows:

Id	Name	Color	HireDate
1	Tom	Pink	9/15/2001 3:30:00 AM
2	Jerry	Blue	9/15/1991 3:30:00 AM

Question:

• How change DataGrid row color according to color in database .
• Format of the Date should be mm/dd/yyyy

Solution:

Step 1:

In webform1.aspx drag drop a Datagrid.

<asp:DataGrid id="DataGrid1" DataKeyField="ID" runat="server"></asp:DataGrid>

Simple binding of theData to datagrid as given below

Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Sub BindData()
Dim ds As New DataSet
Dim sqlStmt As String = "SELECT * FROM ColorTable"
Dim conString As String = "server=localhost;database=Northwind;uid=sa;pwd=;"
Dim myda As SqlDataAdapter = New SqlDataAdapter(sqlStmt, conString)
myda.Fill(ds, "Table")
Dim dv As New DataView(ds.Tables(0))
DataGrid1.DataSource = dv
DataGrid1.DataBind()
End Sub

Step 2:

To display the data in the Datagrid based on Database values

Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, _ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
'Create a DataView Based on the DataSource of DataGrid
Dim dv As DataView = DataGrid1.DataSource
Dim dc As DataColumnCollection = dv.Table.Columns
'Check for ItemType
If e.Item.ItemType = ListItemType.Item Or _e.Item.ItemType = ListItemType.AlternatingItem Then
'Declare string variable
'Assign the relevant data to a variable
Dim fieldcolor As String
fieldcolor = DataBinder.Eval(e.Item.DataItem, "color")
'To convert the value of Type String to System.Drawing.Color
e.Item.BackColor = System.Drawing.Color.FromName(fieldcolor)
'Declare DateTime variable
'Assign the relevant data to a variable
'To display DateTime field in the format "MM/dd/yyyy"
Dim fieldDate As DateTime
fieldDate = Convert.ToDateTime _(DataBinder.Eval(e.Item.DataItem, "HireDate", "{0:MM/dd/yyyy}"))
'To display the value in the proper cell of DataGrid
e.Item.Cells(dc.IndexOf(dc("HireDate"))).Text = fieldDate
End If
End Sub

The other ways to format Date in MM/dd/yyyy format are:

a) e.Item.Cells(dc.IndexOf(dc("HireDate"))).Text = fieldDate.ToString("d")
b) e.Item.Cells(dc.IndexOf(dc("HireDate"))).Text = fieldDate.ToShortDateString()