<%@ 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 CASE WHEN (Grouping(CategoryName)=1) THEN 'MainTotal' ELSE categoryname END AS CategoryName, "
CommandText += " CASE WHEN (Grouping(ProductName)=1) THEN 'SubTotal' ELSE Productname END AS ProductName,"
CommandText += " Sum(UnitPrice) as unitprice, "
CommandText += " Sum(UnitsinStock) as UnitsinStock "
CommandText += " from Products INNER JOIN Categories On Products.categoryID = Categories.CategoryID"
CommandText += " Group By Categoryname, ProductName WITh ROLLUP "
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 i As Integer = 0
Do While i <= ds.Tables(0).Rows.Count - 1
curCat = ds.Tables(0).Rows(i).Item(0)
If curCat <> prevCat Then
prevCat = curCat
Dim shRow As DataRow = ds.Tables(0).NewRow
shRow(1) = ds.Tables(0).Rows(i).Item(0)
'Change ItemDataBound marker to Negative Number
shRow(2) = -1
ds.Tables(0).Rows.InsertAt(shRow, i)
i += 1
End If
i += 1
Loop
'End Insert SubHeadings
'Change the Text in the Last Column so it can be formated in ItemDataBound
ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1).Item(1) = "Total"
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("-1") Then
'Format the SubHeading Columns
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
If e.Item.Cells(0).Text.Equals("Total") Then
'Format the Main total column
e.Item.Cells(0).Attributes.Add("align", "Left")
e.Item.Cells(0).Font.Bold = True
e.Item.Cells(1).Font.Bold = True
e.Item.Cells(2).Font.Bold = True
e.Item.BackColor = Color.FromArgb(204,153,255)
End If
If e.Item.Cells(0).Text.Equals("SubTotal") Then
'Format the subtotal columns.
e.Item.Cells(0).Attributes.Add("align", "Left")
e.Item.Cells(0).Text = "Sub Totals"
e.Item.Cells(0).Font.Bold = True
e.Item.Cells(1).Font.Bold = True
e.Item.Cells(2).Font.Bold = True
e.Item.Cells(0).Font.Italic = True
e.Item.Cells(1).Font.Italic = True
e.Item.Cells(2).Font.Italic = True
End If
End Select
End Sub
</script>
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" OnItemDataBound="DataGrid1_ItemDataBound" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False" AutoGenerateColumns="False">
<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>
|