Display sum of times in datagrid footer - variable issue?

Hi,

This is probably simple but I'm obviously overlooking something. I have a datagrid displaying data pulled from SQL. One fo the DG fields is called "duration" and shows the result of time1 - time2 which is done in the SQL statement. This all works fine but now I am trying to show the totals in a DG footer using the following code:

 Protected Sub showTotals(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            count1 += DataBinder.Eval(e.Item.DataItem, "...item1..")
            count2 += DataBinder.Eval(e.Item.DataItem, "...item2..")
            count3 += DataBinder.Eval(e.Item.DataItem, "...item3..")
            count4 += DataBinder.Eval(e.Item.DataItem, "...item4..")
            durationSum += DataBinder.Eval(e.Item.DataItem, "duration")
        ElseIf e.Item.ItemType = ListItemType.Footer Then
            e.Item.Cells(10).Text = "Totals:"
            e.Item.Cells(11).Text = count1
            e.Item.Cells(12).Text = count2
            e.Item.Cells(13).Text = count3
            e.Item.Cells(14).Text = count4
            e.Item.Cells(15).Text = durationSum
        End If
    End Sub

I cannot figure out how to Dim the variable "durationSum" - I either get garbage when I use "String" or I get an error when I try to use "Date" because I don't know what to assign as a default value (Dim durationSum As Date = ??)

Again, I'm sure this is simple but it's Monday and I can't get my head around it - any thoughts? Thanks as always...

0
ewallig
3/3/2008 5:43:11 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

16 Replies
481 Views

Similar Articles

[PageSpeed] 12

If the time you are showing is like 2hours 30 min like that you can convert it into mins and add all the rows values,

when displaying in footer, divide that sum with 60(Total mins per hour) and display that.


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
3/4/2008 2:45:23 PM

You need to search for the control(s) that display date values in Row Data Bound Event and convert their text to date using Convert.ToDateTime(StringValueToConvert) and then add.

Again search for the footer display controls and then assign them.


Thanks
Gavini
0
gavini
3/4/2008 3:35:55 PM

Hi,

 Thanks for the help so far - gavini, can you provide some additional detail and maybe some example code based on my first post? Of particular interest to me is how to Dim the "durationSum" variable - I currently have it as "Integer" and as a result I keep keeping a cast error if I try to change the data type of datetime using Convert.ToDateTime(durationSum) - any advice on this?

 Thanks a bunch...

0
ewallig
3/4/2008 5:46:55 PM

The problem with Date and Time values you cannot calculate the sum and differece values directly, you need to extract individual parts of the datetime string and calculate values. 

What are you exactly trying to show in the footer...if you are trying to sum the date difference between each in the list, you need to use DateAndTime.DateDiff(Interval, Date1, Date2) which returns a long int.

'Interval' above could be any like day, hr, min, month, year etc.
Example:

 Dim day As Int64 'To calculate days
 Dim h As Int64 'To calculate hrs

           day = DateAndTime.DateDiff(DateInterval.Day, d1, d2)
           day+=DateAndTime.DateDiff(DateInterval.Day, d2, d3

           h= DateAndTime.DateDiff(DateInterval.Hour, d1, d2)                                                             

           h+=DateAndTime.DateDiff(DateInterval.Hour, d2, d3)

       ' where d1 and d2, d3 are date values from the grid

       ' You can search for the display controls (probably labels) which display dates from each row and assign the above DateDiff function for all the date parts you are trying to get the sum like days, months, years, hrs etc.

I hope this helps you.Smile


Thanks
Gavini
0
gavini
3/4/2008 6:35:38 PM

I have this in a SQL SELECT statement "...,DateDiff(n, time1, time2) As dSum,...

The results from the SQL query populate a datagrid which has a BoundColumn for "dSum". This appears to be displaying the correct difference between the two times in minutes - if time1 is 13:00 and time2 is 13:55 the "dSum" value is "55" and if I can sum the total in the footer but it always shows in minutes (ex. row1: dSum=60, row2: dSum=60, row3: dSum=55 = 175). I can neither convert this to HH:mm in the code-behind or within the datagrid (using the "DataFormatString" option) - it errors in the code-behind and all I get on the page if I do it in the DG is "HH:mm" for the output of dSum.

Any thoughts?

Thanks for your help...

0
ewallig
3/4/2008 6:51:51 PM

 ok now I got what you are trying to do...can you tell what is the format you are trying to get..if other than HH:mm ?


Thanks
Gavini
0
gavini
3/4/2008 7:47:14 PM

 for HH:mm format use: Val \ 60 & ":" & Value Mod 60

where Val is the Sum value 


Thanks
Gavini
0
gavini
3/4/2008 8:02:43 PM

If I leave off the "DataFormatString={0:HH:mm}" option in the DG, then I get the value in what appears to be minutes (and it all adds up correctly). If I add the option, then all I get is "HH:mm". Any attempt at formatting the data in the code-behind results in a cast error or something similar. Here's the related code - I removed everything that didn't apply for brevity's sake:

(In code-behind)

 Protected Sub BindData(ByVal ID)
        Dim varT As String = ID
        Dim objConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("AppConnString").ConnectionString)
        objConn.Open()
        Dim sSQL As String = "SELECT ...time1,time2, DateDiff(n,time1,time2) As dSum,....;"
        Dim objCommand As New SqlCommand(sSQL, objConn)
        BindData.DataSource = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
        BindData.DataBind()
        objConn.Close()
end sub


    Dim dSum As Integer = 0

    Protected Sub showTotals(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            ....
            dSum += DataBinder.Eval(e.Item.DataItem, "dSum")
        ElseIf e.Item.ItemType = ListItemType.Footer Then
            e.Item.Cells(10).Text = "<strong>Totals:</strong>"
            e.Item.Cells(11).Text = dSum
            ...

End If
    End Sub

 (on page)

<asp:BoundColumn DataField="time1" DataFormatString="{0:HH:mm}" HeaderText="time1" />
<asp:BoundColumn DataField="time2" DataFormatString="{0:HH:mm}" HeaderText="time2" />
<asp:BoundColumn DataField="dSum" HeaderText="Duration" />

Note that the "time1" and "time2" columns accept the formatting w/o problems (as expected) but the "dSum" column simply returns "HH:mm" when that "DataFormatString=" is there which I guess makes sense since the values are not datetime. I think that part of the problem is the datatype but I really don't know how to work around this - hope this clears it up some.

Thanks again....Tongue Tied

 

0
ewallig
3/4/2008 8:05:59 PM

Thanks! That worked pretty well for the footer total - any idea how to do it for the rows within the DG?

Again, thanks a bunch, been banging my head on my desk for most of the day Big Smile

0
ewallig
3/4/2008 8:22:54 PM

 Cool...mark the post as answered..can you explain the format in which you are expecting the display? I thought you already got this one..!!


Thanks
Gavini
0
gavini
3/4/2008 8:46:22 PM

The DG DataFormatString worked when I had the dSum value datatyped as "String" but then it wouldn't add (just concatenated into a bizarre number). With the dSum datatyped as Integer, the {0:HH:mm} simply returns the characters "HH:mm" and not minutes converted into hours and minutes. This makes sense to me as the resulting dSum value is no longer really a properly-formated date but simply the number of minutes. Ideally, I would be able to display dSum in each row in 0:00 format (1:00, 2:23, etc) for the hours and minutes elapsed between the time1 and time2 values (seconds not important in this case).

Not super critical at the moment; it does display it as number of minutes but it is something that I'm sure I'll need to do eventually so if you have any ideas, I'd appreciate it. I will mark the question as answered anyway since the main problem is solved.

Thanks again...

0
ewallig
3/4/2008 9:03:15 PM

 What you have to do is in Row Data Bound event of the grid, search for the label used to display the value and format it as done for the footer value.

Its better to add a template field with label control and search for this label ID. (If you haven't used template field before now you have to change the footer display code too.)

 


Thanks
Gavini
0
gavini
3/4/2008 9:19:45 PM

uuuhhhh....not to sound too stupid but do you have any code examples - it's not something I've done before.

Thanks again...

0
ewallig
3/4/2008 9:25:31 PM
Add a template column for the datagrid to display time values from database: 

<asp:TemplateColumn HeaderText="Name">
    <ItemTemplate>
        <asp:Label runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Val") %>'> </asp:Label>
    </ItemTemplate>
</asp:TemplateColumn>

 

 In code-behind in ItemDataBound event of the data grid:

 Private Sub DataGrid1_ItemDataBound(ByVal sender AsObject, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgContacts.ItemDataBound

    If e.Item.ItemType = ListItemType.ItemOrElse e.Item.ItemType = ListItemType.AlternatingItem Then
       Dim i As Integer = CType(DataBinder.Eval(e.Item.DataItem, "Val"), Integer) 'Repeats this for all rows

        Dim s As String
        s =i \ 60 & ":" & i Mod 60

        e.Item.Cells(x).Text = s      
    End If
 End Sub
    
Thanks
Gavini
0
gavini
3/5/2008 3:57:10 PM

I stand before you in awe - you are a coding genius!

 Thanks a bunch, works like a charm Big Smile

0
ewallig
3/5/2008 4:15:51 PM

thanx...anytime


Thanks
Gavini
0
gavini
3/5/2008 6:32:31 PM
Reply:

Similar Artilces:

How to Displaying the Sum of a Column in the Datagrid Footer
Hi all. Im need Displaying the Sum of a Column in the Datagrid Footer, but the property AutoGenerateColumns is True. ¿How to do This? Thanks to all. Mauricio Villagrán Try this: <asp:label id="lblClaimTotal" runat="server" ></asp:label> <asp:datagrid id="DataGrid1" runat="server" AllowPaging="False" DataKeyField="ID" OnItemDataBound="grid_ItemDataBound" > and in the code behind page 'Routine to highlight selected grid row. And to show total of claim amount colum...

How to Display Sum Total in the Footer of the GridView Control
I found an answer for this question. "How to Display Sum Total in the Footer of the GridView Control" But this resource was written in aspx source. I wanted to convert this to "Code Behind" style but it's not working out. Please help me. [Original working source]  aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Dim TotalUnitPrice As Decimal = 0.0 Function GetUnitPrice(ByVal Price As Decimal) As Decimal TotalUnitPrice += Price ...

DataGrid etc does not display when using VS.NET server controls
This thing is driving me nuts! I know you should be able to do it with code but I want to be able to drag a dataadapter onto a page, set up the dataset from that and bind it to a datagrid without writing a line of code. Everytime I do it whether I am using a remote SQL Server or a local MSDE for the datasouce all I get is a blank page. The source contains the form and the __VIEWSTATE and that is it. If I add in the pageload codebehind datagrid1.databind() then I get the column headings and that is all. Any ideas? Regards, DaveB You always have to call DataBind, that's for sure....

How to Display Sum Total in the Footer of the GridView Control using boundfield, templatefield
 This vb code compiles find but give me runtime error. Please help me!    Dim TotalUnitPrice As Decimal = 0.0 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim col1 As BoundField = New BoundField() Dim col2 As BoundField = New BoundField() Dim col3 As TemplateField = New TemplateField() col1.DataField = "Productid" col1.HeaderText = "Product Id" col1.SortExpression = "Productid" col2.DataField = "ProductName" col2.HeaderText = "Product N...

How to place a DataGrid Control with in a DataGrid Control
Hi, I want to place a DataGrid control in a DataGrid control can any one plz tell me the procedure to place the control Thanks in Advance Krisamigo <!---<asp:DataGrid ID=dg_List runat=server><Columns><asp:TemplateColumn><ItemTemplate><asp:DataGrid ID=dg_List2 Runat=server></asp:DataGrid> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> ---> Hardik Joshi...

Compute the execution time and display time for a process or a web control
Hi all, Can I compute the time needed for a process to complete in asp.net?Can I compute the time needed for a datagrid to be loaded and displayed (rendered)?  I have done the first part by this code: Dim dTime As Double = 0.0 Dim dEnd As Double = 0.0 Dim dStart As Double = System.Environment.TickCount' Your code, lets say that I'm executing and SQL statement and binding data to datagrid dEnd = System.Environment.TickCount dTime = dEnd - dStart   this code will allow me to see how long a process need to be executed. But How To know the time need for the data grid...

Finding Controls from User Control issue. C#.NET 1.1
Okay, I have a user control that opperates on a DataGrid. I have a Page which has a DataGrid my userControll on it. My User Control needs to operate on that Datagrid (and hold it's formation through post-backs, not in the viewstate). So the only thing I can think of is to find the DataGrid by it's name, which is set in the html as the public property string DataGridName: public string DataGridName         {             set             {     &n...

Datagrid footer control..
Can someone tell me how to include a totals in the footer of a datagrid.. My problem is i have a grid that shows various totals say in a department,say like the total,totalcontractors,totalfulltime ,partitme...soon.. for various departments. and finally in the footer i need to include a totals field for every single row ... Is there a way i can just add totals for every data bound column in the grid.. thanks .net learner When I had to do this I added a simple datalist after the datagrid so it looked like the footer. .aspx code Shown Below: <asp:datalist id="...

Issues with datagrid control.
Heys guys. I hope this is the right place for this. My first time on these forums. Ok this is what I am working on. The company I work for has asked me to create an equipment designer webpage to help in building quotes for customers. Building the designer page itself was a snap after over coming a few small irrelevant issues. The part im having trouble with is populating the main quote page with everything that gets designed. Basically its a type of shopping cart but doesnt need anything fancy.   Ok thats a little background, now I will tell ya how the site works so far (which...

displaying a control under datagrid
Hello,I am trying to display a label under a datagrid. I'm not using paging for the datagrid, so it could be of any length. How would I position a label so it is always displayed under a datagrid and not covered by the datagrid if it has many rows?Thanks. This is usually caused by having your page set to GridLayout (the default if using VS.NET 2002/2003), which uses absolute positioning.  Change your page to FlowLayout and you should be good to go.Marciehttp://www.datagridgirl.comNEW! http://www.gridviewgirl.com Because of some other things I have on the page, I need the p...

Issue using control created in VS.net 2003 in VS.net 2002
I have a friend that has an issue using my asp.net C# custom control in Visual Studio.net 2002 that I have compiled under Visual Studio.net 2003. I never did test it with that version, but I was under the impression that it should still work as long as you don't use any features specific to the 1.1 framework (which I really don't think I did). My guess now is that this is correct, but only when using 2002 controls in 2003. I have searched the web, these forums, and MSDN for the last 30 minutes with no real answer. The guy that runs MetaBuilders (http://www.metabuilders.com/Faq.aspx) tell...

AutoComplete control display problem when used in a user control; and this usercontrol is appearing multiple time on form
Hi, I am using AutoComplete AjaxControlToolKit extender 1.0.20229.15278 on .NET 2.0 framework. I have a address user control which is used multiple places in single ASPX page. The  Problem I see is the first control shows the Autocomplte popup but another control does not show Autocomplete popup.  I have couple of guess - The BehaviorID is identical on control causing the trouble  - extender control not registered properly for second instance  Here is "the" code I am using for autocomplte extender  <ajaxTool...

Datagrid(.net 1.1) to GridView(.net 2.0) and using the RowUpdating event how to extract the values from the controls.
Ok  This is the settings: I use a Viewgrid with only itemtemplate colums for example. <asp:TemplateField HeaderText="Test"><ItemTemplate><asp:Label id="LBL_Test" text='<%# DataBinder.Eval(Container.DataItem, "Test1") %>' runat="server" meta:resourcekey="LBL_TestResource1"></asp:Label></font></ItemTemplate> <EditItemTemplate><asp:TextBox ID="HDN_Test" Text='<%# DataBinder.Eval(Container.DataItem, "Test1") %>' Runat="server" meta:resourcekey="HDN_TestResource1" /></EditItemTemplate></asp:TemplateField>  The ...

Data presentation control in another Data presentation control
 Hello,I want to make a menu with datalist or gridview control.Basically,the menu will be like this,Categories     Sub Categories.For example, for COMPUTING category there will be NOTEBOOKS DESKTOPS etc.There will be a image for CATEGORIES and linkbuttons with Subcategories.If I put a datalist inside a datalist and a link button inside the second datalist visually it satisfies my need.However,CommandArguments come from database to linkbutton.Andwhen one of these command buttons is cliked it must go to proper page.I dont know how to wrie an event_handler ,specifically, for...

Changing field names (display name) for a web interface and how to create a scroll bar for a datagrid or any control with a scroll bar to display data
Hello,  Can any1 please help me. i was wondering if any1 can tell  me how i can change  field names for a data grid only for the display on the web interface and also how can i put scroll bars on my data grid. Thank you.       Hi misbahmurtza, You can set field names in HeaderText attribute in any column or template. If you want to create a scroll bar, the easiest way is to set style for the <div> which contains your datagrid. Here is a sample: <div style="vertical-align: top; height:100px; width:100%; overflow:auto;"> Make sure ...

Web resources about - Display sum of times in datagrid footer - variable issue? - asp.net.presentation-controls

Want to preview our new DataGrid for Xamarin.Forms?
tl;dr Zumero.DataGrid is a Xamarin.Forms control for displaying data in rows and columns. If you would be interested in testing and previewing ...

Mad King DataGrid
This post is about scrolling in WPF and the egocentric DataGrid control. I’ll give the project background but if all you’re interested in is ...

Stack Overflow
Stack Exchange log in - careers - chat - meta - about - faq Questions Tags Users Badges Unanswered Ask Question Top Questions interesting 295 ...

Microsoft Silverlight - Wikipedia, the free encyclopedia
Microsoft Silverlight is an application framework for writing and running rich Internet applications , with features and purposes similar to ...

Flash Builder Plug-in for SAP Netweaver Gateway - Adobe Labs
Search Navigation Labs Home Wiki Home Technologies Alchemy Blueprint Camera Profiles and DNG Profile Editor Cirrus ColdFusion 10 DNG Codec Fireworks ...

Casaba Security » Products » X5s Testing Tool For Finding XSS Vulnerabilities
Security analysis, consulting, development and testing partners who understand your business strategy.

CodeGuru - Microsoft developers related ideas, articles, tips, tricks, comments, downloads, and so much ...
CodeGuru is where developers can come to share ideas, articles, questions, answers, tips, tricks, comments, downloads, and so much more related ...

Caliburn Micro: WPF, Silverlight, WP7 and WinRT/Metro made easy.
A small, yet powerful framework designed for WPF, Silverlight and WP7. It implements a variety of UI patterns for solving real-world problems. ...

Quick Answers - CodeProject
Questions - Free source code and tutorials for Software developers and Architects.; Updated: 11 Jul 2012

license-discuss@opensource.org: 11636: first draft of license proliferation committee report
FYI,the first draft report of the OSI's License Proliferation Committee.To join the OSI's license proliferation discuss email list, send anemail ...

Resources last updated: 1/21/2016 10:28:50 PM