Total Value for Field

[scota]

I am new to ASP.NET, C#. I have the following code which will not
display the total Credits for all the records. It is printing "Total
Credits: 0" instead of adding the credits. What am I missing?
Credits is the 4th column in the datagrid and the 4th field in the SQL
view. Credits data type is float.

Thank you.

protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection objConnection;
SqlCommand objCommand;
String strConn;

strConn =
ConfigurationManager.ConnectionStrings["web_CMEConnectionString"].ToString();
objConnection = new SqlConnection(strConn);
objConnection.Open();

string strSQL = "SELECT * FROM vw_PINTopic WHERE PIN = " +
txtPIN.Text.ToString() + " AND [DATE] between '" +
calStart.SelectedDate + "' and '" + calEnd.SelectedDate + "'";
Trace.Write(strSQL);
objCommand = new SqlCommand(strSQL, objConnection);
objCommand.CommandType = CommandType.Text;

SqlDataReader objReader = objCommand.ExecuteReader();

if (objReader.HasRows)
{
dgResults.DataSource = objReader;
dgResults.DataBind();
dgResults.Visible = true;

double sumTotal = 0;

using (objReader)
{
while (objReader.Read())
{
sumTotal += objReader.GetDouble(3);
}
lblPIN.Text = "Total Credits: " + sumTotal;
}
}
else
{
dgResults.Visible = false;
lblPIN.Text = txtPIN.Text + " has no courses within this
timeframe.";
}
objReader.Close();
objConnection.Close();
}

 

[John Saunders]

I am new to ASP.NET, C#. I have the following code which will not
display the total Credits for all the records. It is printing "Total
Credits: 0" instead of adding the credits. What am I missing?
Credits is the 4th column in the datagrid and the 4th field in the SQL
view. Credits data type is float.

You would get "0" if there were no rows, or if all values were zero. I would
add instrumentation to determine which is true. Trace.WriteLine is helpful
here.

John

 

[Marina Levit [MVP]]

When you bind the reader to the grid, the entire datareader is read through
until the end. So by the time your while loop begins, you are already at the
end of the result set - no rows left to read.

You can either rerun the query (not advisable to rerun as is, but you can
rerun and do a COUNT so you make the SQL engine do the adding for you), or
you a datatable, which brings all the rows into memory. Since they will all
be in memory, you can bind to the grid, then just loop throught the rows and
do the calculation. The DataTable also has a Compute method, which I believe
you can use to compute the sum so you don't have to actually loop through
it.

 

[scota]

I figured out how to do the total in the footer of the datagrid, so now
my asps.cs code looks like this:

public partial class _Default : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
txtPIN.Focus();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection objConnection;
SqlCommand objCommand;
String strConn;

strConn =
ConfigurationManager.ConnectionStrings["web_CMEConnectionString"].ToString();
objConnection = new SqlConnection(strConn);
objConnection.Open();

string strSQL = "SELECT * FROM vw_PINTopic WHERE PIN = " +
txtPIN.Text.ToString() + " AND [DATE] between '" +
calStart.SelectedDate + "' and '" + calEnd.SelectedDate + "'";
Trace.Write(strSQL);
objCommand = new SqlCommand(strSQL, objConnection);
objCommand.CommandType = CommandType.Text;

SqlDataReader objReader = objCommand.ExecuteReader();

dgResults.DataSource = objReader;
dgResults.DataBind();
objConnection.Close();
}
Single runningSum = 0;

public void KeepRunningSum(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item ||
e.Item.ItemType == ListItemType.AlternatingItem)
runningSum +=
Convert.ToSingle(DataBinder.Eval(e.Item.DataItem, "Credits"));
else if (e.Item.ItemType == ListItemType.Footer)
e.Item.Cells[2].Text = "<b>Total:</b> ";
e.Item.Cells[3].Text = String.Format("{0:#.#}", runningSum);

}
}

It prints Total: but not the total value. It's printing the value of
the last entry. I'm doing this on the click event of the submit button
because of the parameters in the query and not on the page load. What
am I missing this time?

 

[scota]

Finally, it works. Curly braces were missing in the KeepRunningSum
routine:

public void KeepRunningSum(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item ||
e.Item.ItemType == ListItemType.AlternatingItem)
runningSum +=
Convert.ToSingle(DataBinder.Eval(e.Item.DataItem, "Credits"));
else if (e.Item.ItemType == ListItemType.Footer)
{
e.Item.Cells[2].Text = "<b>TOTAL CREDITS:</b> ";
e.Item.Cells[3].Text = String.Format("{0:#.#}",
runningSum);
}
}