ASP HELP

[Kaushik Mandal]

Hi

I am trying to make a ASP.NET report where I would like to show the Sum
Total and the Grand total on the reports . I am trying to do this using
SQL server ROLL UP function . But it does not work . Could any one plase
help me ?

Here is my code.


<%@ Language="VB" autoeventwireup="false" %>


<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Drawing" %>
<script >



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 trandate, "
CommandText += " CASE WHEN (Grouping(vendor_mach_num)=1) THEN
'SubTotal' ELSE vendor_mach_num END AS vendor_mach_num,"
CommandText += " Sum(remotemonitoring.price) as Tcost, "
commandText +=" sum(dbo.remotemonitoring.amount) as
amount,bin,vendmastnew.username,vendmastnew.userpassword,vendmastnew.vna
me,"
CommandText += "From dbo.remotemonitoring inner join
dbo.remotemonitoringmachines on dbo.remotemonitoring.eport =
dbo.remotemonitoringmachines.eport inner join vendmastnew on
dbo.vendmastnew.vendno=remotemonitoringmachines.vendor_num
where dbo.vendmastnew.vendno is NOT NULL and
dbo.remotemonitoringmachines.amount > 0 "
CommandText += " Group By trandate,vendor_mach_num,bin 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="Blue"
EnableViewState="False" AutoGenerateColumns="False">
<HeaderStyle font-bold="True" forecolor="White"
backcolor="#A4BEEA"HeaderStyle>
<ItemStyle backcolor="#A4BEEA"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="trandate"
HeaderText="Date"></asp:BoundColumn>
<asp:BoundColumn DataField="vendor_mach_num"

<asp:BoundColumn DataField="tcost"
HeaderText="total cost"></asp:BoundColumn>




</Columns>
</asp:datagrid>
</form>