<%@ Page Language="VB" autoeventwireup="false" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Drawing" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs) Handles MyBase.Load
' TODO: Update the ConnectionString and CommandText values for your application
Dim ConnectionString As String = ConfigurationSettings.AppSettings("connectionStringNW")
Dim CommandText As String = "Select CategoryName, ProductName, Cast(UnitPrice as varchar(50)) as unitprice, UnitsinStock from Products INNER JOIN Categories On Products.categoryID = Categories.CategoryID order by Products.categoryID"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet
myCommand.Fill(ds)
'Before DataBind - Add New Rows Here for Each Sub Heading
Dim curCat As String
Dim prevCat As String
Dim row As TableRow
Dim i As Integer = 0
Do While i <= ds.Tables(0).Rows.Count - 1
curCat = ds.Tables(0).Rows(i).Item("CategoryName")
If curCat <> prevCat Then
prevCat = curCat
Dim shRow As DataRow = ds.Tables(0).NewRow
shRow("ProductName") = ds.Tables(0).Rows(i).Item(0)
shRow("UnitPrice") = "SubHead"
ds.Tables(0).Rows.InsertAt(shRow, i)
i += 1
End If
i += 1
Loop
'End Insert SubHeadings
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub
Private Sub DataGrid1_ItemDataBound(sender As Object, e As DataGridItemEventArgs)
Select Case e.Item.ItemType
Case ListItemType.AlternatingItem, ListItemType.Item
If e.Item.Cells(1).Text.Equals("SubHead") Then
'Format the data, and then align the text of each cell to the left.
e.Item.Cells(0).Attributes.Add("align", "Left")
e.Item.Cells(0).ColumnSpan = 3
e.Item.Cells(0).Font.Bold = True
e.Item.Cells.RemoveAt(2)
e.Item.Cells.RemoveAt(1)
e.Item.BackColor = Color.FromArgb(204,204,255)
End If
End Select
End Sub
</script>
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="False" EnableViewState="False" ForeColor="Black" BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1" OnItemDataBound="DataGrid1_ItemDataBound">
<HeaderStyle font-bold="True" forecolor="White" backcolor="#4A3C8C"></HeaderStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="ProductName" HeaderText="Product Name"></asp:BoundColumn>
<asp:BoundColumn DataField="UnitPrice" HeaderText="Unit Price"></asp:BoundColumn>
<asp:BoundColumn DataField="UnitsInStock" HeaderText="Stock Level"></asp:BoundColumn>
</Columns>
</asp:datagrid>
</form>
|