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()