Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Datagrid Control > ASP HELP

Reply
Thread Tools

ASP HELP

 
 
Kaushik Mandal
Guest
Posts: n/a
 
      05-23-2005


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("connectionStrin gNW")
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.userpa ssword,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.ve ndor_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>


*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help Help Help Pentax S5i Help needed (Please) The Martian Digital Photography 14 06-20-2008 07:56 AM
global filter to redirect asp (old asp!) pages on asp.net site Daves ASP .Net 2 05-31-2006 08:33 AM
2.0: asp:Menu, asp:XmlDataSource, asp:PlaceHolder R.A.M. ASP .Net 1 03-29-2006 07:55 AM
[ASP.NET1.1]Should I kill the ASP.NET worker process after recompilingmy ASP.NET webservice? Andrea Raimondi ASP .Net 1 02-06-2006 08:10 AM
ASP.Net cookie -> ASP -> ASP.Net Ben ASP .Net 3 05-28-2004 03:35 PM



Advertisments