how to display sub and grand total in gridview footer

[adugenet]

hi all,
I am displaying data in girdview control and I set paging to true.my question is how can i display subtotal at the end of each page and grand total at last page.can someone show me how i can do this or point me to a good tutorial site. thanks in advance

 

[tperri]

Try starting here:

http://www.asp.net/learn/dataaccess/tutorial15vb.aspx?tabid=63

 

[adugenet]

thank you for your response tperri.I do not have a problem to show the total in the gridview footer if i donot have paging set to true.My problem is when i set paging the sub total I get in each page is wrong and I am just wondering how i can display the right sub total in each page and the grad total in the last page.thank you agin. Is this something that can be done in my sql?any clue would be appreciated

 

[tperri]

You could do your own paging and pull back only the records you are displaying and calculate totals that way in your SQL, sure.

Or do your subtotaling in you RowDataBound event. And if you are on your last page, in that same event, iterate through your entire dataset to calucate a grand total.

 

[adugenet]

thank you for your response.I like the second method you suggested to me and can you give me an example how this can be done. thank you for your help.

 

[ca8msm]

What have you tried so far?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jmeckley]

this will put the displayed subtotal in the 1st column footer and the grand total in the 2nd column footer.

private int subTotal = 0;
private int grandTotal = 0;

protected void myGridView_RowDataBound(object sender, RowDataBoundEventArgs e)
{
  if (e.Row.RowIndex > -1)
  {
        myObject data = (myObejct)e.Row.DataItem;
        this.subTotal += data.myColumn;
  }
}

protected void myGridView_DataBound(object sender, EventArgs e)
{
     //get all items from datasource.
     //your data will determine your exact loop
     //this is just sudo code.
     foreach(object obj in myDataSource.Objects)
     {
         grandTotal += obj.myProperty;
     }

     myGridView.FooterRow.Cells[0].Text = "SubTotal: " + this.subTotal.ToString();
     myGridView.FooterRow.Cells[1].Text = "Grand Total: " + this.grandTotal.ToString();
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[adugenet]

thank you Jason Meckley this will help me to keep going and I will let you know the outcome. thanks again

 

[adugenet]

This works for the first page index and when I go to second page the value is completely worng and also if i go back to the first page I get a different number.I do not know why it is not working. I really appreciate it you point to me my error.


Dim LowBid As Decimal = 0
Dim Average As Decimal = 0
Dim EstimateTotal As Decimal = 0
Protected Sub gvEstimate_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowIndex > -1 Then
Dim ItemTotal As Decimal = CType(DataBinder.Eval(e.Row.DataItem, "Estimate"), Decimal)
Dim LowBid As Decimal = CType(DataBinder.Eval(e.Row.DataItem, "Bid"), Decimal)
Dim AverageBid As Decimal = CType(DataBinder.Eval(e.Row.DataItem, "Average"), Decimal)
EstimateTotal += ItemTotal
LowBid += LowBid
Average += AverageBid
End If
If e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(5).Text = "Eng.Estimat:" & EstimateTotal.ToString("c")
e.Row.Cells(6).Text = "Low Bid :" & LowBid.ToString("C")
e.Row.Cells(7).Text = "Average Bid :" & Average.ToString("C")
End If
End Sub

 

[tperri]

You need to reset your totaling variables to 0 when you begin your RowDataBound routine

 

[adugenet]

Well thanks for your help I figured out and now I get the sub totals but once I fix that I am getting another problem..I am using three differnt girdview to display data in one page one is for the letting information and the second gridview I use to display infor about vendors and the third one I use to display items they bid for projects. the third one has paging set to true and when I click that the first two gridview are disappearing and I do not know why ...I am attaching my code and thanks for the help again


Imports System.Data
Imports System.Data.OracleClient
Imports System.Configuration.ConfigurationManager
Partial Class itemRpt_bridge
Inherits System.Web.UI.Page
'The value from these varibles are being used for the there queries below
Dim lettingId As String
Dim contractId As String
Dim callId As String
Dim letNum As String
Dim contNum As String
Dim calNum As String
Dim LowBid As Decimal = 0
Dim Average As Decimal = 0
Dim EstimateTotal As Decimal = 0

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
lettingId = "'" & Request.QueryString("lettingId").ToString & "'"
contractId = "'" & Request.QueryString("contractId").ToString & "'"
callId = "'" & Request.QueryString("callId").ToString & "'"
lblLetting.Text = Request.QueryString("lettingId").ToString
lblContractId.Text = Request.QueryString("contractId").ToString
lblCallNumber.Text = Request.QueryString("callId").ToString
If Not IsPostBack Then
BindLetData()
BiddersData()
ItemsData()
End If
End Sub

Sub BindLetData()
Dim drBindLetData As OracleDataReader
Dim connectionString As String = ConnectionStrings("costEstimating").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()
Dim strSql As String

strSql = "SELECT DISTINCT b.datelet, p.ccnty1, p.cfacssup, p.contid, p.cprojnum, initcap(p.cdescr) cdescr , p.prroute"
strSql = strSql & " FROM letprop l,proposal p, bidlet b "
strSql = strSql & "WHERE(b.letting = L.letting And l.lcontid = p.contid)"
strSql = strSql & "AND l.letstat = 'A'"
strSql = strSql & " AND l.letting = " & lettingId
strSql = strSql & " AND p.contid =" & contractId
strSql = strSql & " AND l.CALL =" & callId

Dim cmdLetting As OracleCommand = New OracleCommand(strSql, oOracleConn)
drBindLetData = cmdLetting.ExecuteReader()
gvlet.DataSource = drBindLetData
gvlet.DataBind()
drBindLetData.Close()
End Sub

Sub BiddersData()
Dim connectionString As String = ConnectionStrings("costEstimating").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()
Dim strSql As String
strSql = "SELECT DISTINCT v.vendor, initcap(v.vnames) vnames,"
strSql = strSql & " TO_CHAR (d.calcbtot, '$99,999,999.99') AS bd,"
strSql = strSql & " (SUM (q.qty * q.price)) AS engest,"
strSql = strSql & " TO_CHAR ((d.calcbtot / l.contamt) * 100, '999.99') AS ovelow,"
strSql = strSql & " TO_CHAR (d.calcbtot / SUM (q.qty * q.price) * 100, '999.99') AS overest"
strSql = strSql & " FROM letprop l, proposal p,propitem q, bidlet b,"
strSql = strSql & " bidtabs c, itemlist i, vendor v, bidders d"
strSql = strSql & " WHERE(l.letting = b.letting)"
strSql = strSql & " AND b.letting = c.letting"
strSql = strSql & " AND p.cspecyr = i.ispecyr"
strSql = strSql & " AND q.prpitem = i.item"
strSql = strSql & " AND p.contid = l.lcontid"
strSql = strSql & " AND q.contid = p.contid"
strSql = strSql & " AND c.vendor = l.avendor"
strSql = strSql & " AND l.CALL = c.CALL"
strSql = strSql & " AND q.lineflag = c.lineflag"
strSql = strSql & " AND q.iplineno = c.iplineno"
strSql = strSql & " AND l.letting = d.letting"
strSql = strSql & " AND c.letting = l.letting"
strSql = strSql & " AND c.CALL = d.CALL"
strSql = strSql & " AND c.letting = d.letting"
strSql = strSql & " AND v.vendor = d.vendor"
strSql = strSql & " AND l.letstat = 'A'"
strSql = strSql & " AND l.letting =" & lettingId
strSql = strSql & " AND p.contid = " & contractId
strSql = strSql & " AND l.CALL = " & callId
strSql = strSql & " GROUP BY v.vendor, v.vnames, d.calcbtot, (d.calcbtot / l.contamt)"
strSql = strSql & " ORDER BY bd"


Dim drc1 As OracleDataReader
Dim cmdLetting1 As OracleCommand = New OracleCommand(strSql, oOracleConn)
drc1 = cmdLetting1.ExecuteReader()
gvvendors.DataSource = drc1
gvvendors.DataBind()
drc1.Close()

End Sub
Sub ItemsData()
Dim strSql As String

strSql = " SELECT DISTINCT q.iplineno, i.item, initcap(i.idescr) idescr , q.qty, i.iunits,"
strSql = strSql & " TO_CHAR ((q.price * q.qty), '$99,999,999.99') AS EngineersEstimate, "
strSql = strSql & " TO_CHAR ((Min (c.bidprice) * q.qty),'$99,999,999.99') AS LowBid , "
strSql = strSql & " To_char(((SELECT SUM(bt.BIDPRICE)/COUNT(bt.VENDOR) "
strSql = strSql & " FROM BIDTABS bt "
strSql = strSql & " WHERE bt.CALL =" & callId
strSql = strSql & " AND bt.LETTING =" & lettingId
strSql = strSql & " AND bt.LINEFLAG= 'L' "
strSql = strSql & " AND bt.iplineno = q.iplineno))* q.qty, '$99,999,999.99') As Average "
strSql = strSql & " FROM letprop l, proposal p, propitem q, bidlet b, bidtabs c, itemlist i, vendor v, bidders d"
strSql = strSql & " WHERE(l.letting = b.letting And b.letting = c.letting And p.cspecyr = i.ispecyr)"
strSql = strSql & " AND q.prpitem = i.item AND p.contid = l.lcontid AND q.contid = p.contid"
strSql = strSql & " AND c.vendor = l.avendor AND l.CALL = c.CALL AND q.lineflag = c.lineflag"
strSql = strSql & " AND q.iplineno = c.iplineno AND l.letting = d.letting AND c.letting = l.letting"
strSql = strSql & " AND c.CALL = d.CALL AND v.vendor = d.vendor AND l.letstat = 'A'"
strSql = strSql & " AND c.lineflag = 'L' AND "
strSql = strSql & " l.letting =" & lettingId
strSql = strSql & " AND p.contid = " & contractId
strSql = strSql & " AND l.CALL = " & callId
strSql = strSql & " GROUP BY q.iplineno, i.item,i.idescr, q.qty, i.iunits, q.price, c.bidprice,c.vendor "
strSql = strSql & " ORDER BY 1"


Dim connectionString As String = ConnectionStrings("costEstimating").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
Dim cmdItemInfo As OracleCommand = New OracleCommand(strSql, oOracleConn)
Dim adItemInfo As New OracleDataAdapter(cmdItemInfo)

Dim dsItemInfo As New DataSet
adItemInfo.Fill(dsItemInfo, "ItemInformation")
gvitems.DataSource = dsItemInfo
gvitems.DataBind()

End Sub
Protected Sub gvitems_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvitems.PageIndexChanging
gvitems.PageIndex = e.NewPageIndex
ItemsData()
End Sub
Protected Sub gvvendors_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvvendors.RowCreated
If e.Row.RowType = DataControlRowType.DataRow Then
Dim RowValue As Decimal
RowValue = Decimal.Remainder(e.Row.RowIndex, 2)
e.Row.Attributes.Add("OnMouseOver", "this.style.backgroundColor = 'lightsteelblue';")
If RowValue = 0 Then
e.Row.Attributes.Add("OnMouseOut", "this.style.backgroundColor = '#F7F6F3';")
Else
e.Row.Attributes.Add("OnMouseOut", "this.style.backgroundColor = 'white';")
End If
End If
End Sub
Protected Sub gvitems_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim RowValue As Decimal
RowValue = Decimal.Remainder(e.Row.RowIndex, 2)
e.Row.Attributes.Add("OnMouseOver", "this.style.backgroundColor = 'lightsteelblue';")
If RowValue = 0 Then
e.Row.Attributes.Add("OnMouseOut", "this.style.backgroundColor = '#F7F6F3';")
Else
e.Row.Attributes.Add("OnMouseOut", "this.style.backgroundColor = 'white';")
End If
End If
End Sub
Protected Sub gvitems_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)

If e.Row.RowIndex > -1 Then
Dim ItemTotal As Decimal = CType(DataBinder.Eval(e.Row.DataItem, "EngineersEstimate"), Decimal)
Dim LowBd As Decimal = CType(DataBinder.Eval(e.Row.DataItem, "LowBid"), Decimal)
Dim AverageBid As Decimal = CType(DataBinder.Eval(e.Row.DataItem, "Average"), Decimal)
EstimateTotal += ItemTotal
LowBid += LowBd
Average += AverageBid
End If
If e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(5).Text = "Eng.Estimat:" & EstimateTotal.ToString("c")
e.Row.Cells(6).Text = "Low Bid :" & LowBid.ToString("C")
e.Row.Cells(7).Text = "Average Bid :" & Average.ToString("C")

e.Row.Font.Bold = True
e.Row.ForeColor = Drawing.Color.DarkKhaki
e.Row.Cells(5).HorizontalAlign = HorizontalAlign.Left
e.Row.Cells(6).HorizontalAlign = HorizontalAlign.Left
e.Row.Cells(7).HorizontalAlign = HorizontalAlign.Left
End If
End Sub

 

[ca8msm]

That's a lot of strings you are creating in memory (faq855-1882).


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[adugenet]

thank you mark for pointing that out to me.I usually use stringbulder...this is the firs app I did created and somehow I forgot to use stringbuilder. Any clue why I am not able to see the first two gridviews when I use paging for the third one? I am not sure this is the right way to take out of IsPostback? thanks agiain for the help

If Not IsPostBack Then
ItemsData()
End If
BindLetData()
BiddersData()

 

[ca8msm]

I'd suggest stepping through the code. You should be able to see which order the events fire in at each stage you'll be able to see if your GridView's are bound or not (if they are not, they'll probably disappear like you are experiencing).

Also, you've left yourself open to SQL Injection attacks so it could be quite easy for someone to delete your entire database.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.