ASP HELP

Discussion in 'ASP .Net Datagrid Control' started by Kaushik Mandal, May 23, 2005.

  1. 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>


    *** Sent via Developersdex http://www.developersdex.com ***
     
    Kaushik Mandal, May 23, 2005
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?dHJlbGxvdzQyMg==?=

    HELP! HELP! HELP! Opening Web Application Project Error

    =?Utf-8?B?dHJlbGxvdzQyMg==?=, Feb 20, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    817
    =?Utf-8?B?dHJlbGxvdzQyMg==?=
    Feb 20, 2004
  2. Harvey
    Replies:
    0
    Views:
    798
    Harvey
    Jul 16, 2004
  3. Harvey
    Replies:
    1
    Views:
    906
    Daniel
    Jul 16, 2004
  4. =?Utf-8?B?S2ltb24gSWZhbnRpZGlz?=

    HELP - HELP - HELP

    =?Utf-8?B?S2ltb24gSWZhbnRpZGlz?=, Mar 9, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    728
    Eliyahu Goldin
    Mar 9, 2006
  5. Buster

    Help, Help, Help

    Buster, Oct 4, 2003, in forum: Java
    Replies:
    3
    Views:
    509
    Saager
    Oct 30, 2003
Loading...

Share This Page