Help with grid and calculations from wins and losses

[kylcrow]

I am creating a small website for a little basketball league my friends and I are starting where I can enter the wins and losses for a player and a grid will display all the data for that player.

I want the grid to display these fields:

First Name, Last Name, Wins, Losses, Total Games, Win %, 50 Games Played (small icon appears for all players with 50 games played), Most Wins (small icon appears for player with the most wins)

I wrote the code to add players into the database, and associate records with them for wins and losses. But I don't know how to or where to put the calculations for the grid. I am pretty new to .net and very new to database work.

I am using c# and SQL with Visual Studio 2010 and SQL Server Management Studio if that matters.

[jbisono]

You need to create a select statement to return all that data, Can you show table structure so we can help you to build the select. after that there are different ways to fill the grid with the data returned by the select statement.

regards.

[kylcrow]

Ok I didn't know If I should only keep the Wins and Losses in the database and do all the calculations based off those fields when loading the grid, or to use those calculations to enter that data into their own db fields. From your reply, I am assuming I should use the later.

My Database Currently:
dbo.tblPlayer
columns: FirstName (varchar50), LastName (varchar50), PlayerId(uniqueidentifier)

dbo.tblRecords
columns: PlayerId(uniqueidentifier), Wins(int), Losses(int)

I have a grid right now that displays the individual records, but not the summed results per player.

SELECT tblPlayer.FirstName, tblPlayer.LastName, tblRecords.Wins, tblRecords.Losses 
FROM tblPlayer 
CROSS JOIN tblRecords

[mail2saion]

you can try with below query:

SELECT tblPlayer.FirstName, tblPlayer.LastName, SUM(tblRecords.Wins), SUM(tblRecords.Losses) 
FROM tblPlayer 
CROSS JOIN tblRecords
GROUP BY FirstName,LastName

[kylcrow]

Ok, that totally makes sense. I am in some need of some technical help though. How can I build the grid using the sql statement. I have been using visual studios grid wizard, but I feel something like this would need to be loaded from the backend .cs file instead of in the html.

[jbisono]

If you want to fill the grid in the backend first import the sqlclient package

using System.Data.SqlClient;

then under the event that trigger the grid filled. do this.

SqlConnection conne = new SqlConnection("Data Source=DbServerName;Initial Catalog=DatabaseName;User ID=UserID;pwd=Password");
String statement = "SELECT tblPlayer.FirstName, tblPlayer.LastName, SUM(tblRecords.Wins), SUM(tblRecords.Losses) FROM tblPlayer CROSS JOIN tblRecords GROUP BY FirstName,LastName";
SqlDataAdapter adapt = new SqlDataAdapter(statement, conne);
DataSet ds = new DataSet();
adapt.Fill(ds);
DataGridId.DataSource = ds;
DataGridId.DataBind();

try that and let us know how you doing.

regards.

[kylcrow]

Wow that was actually really easy. I have the grid being loaded on the back end now, and summing all the records up.

My next task would be figuring out how to show winning percentages, and how to display small images in their own columns for the player with the most wins, and if a player has over 50 wins.

When I load the grid, would I check (Wins+Losses) and if it is greater than or equal to 50, then display an image, if not display a different image. And for the most wins, compare the Wins only, and only display an image for that player.

Or would I keep track of the percentage, Most Wins, and has50games when I save a new record?

This is my grid Load

protected void LoadStatsGrid()
        {
            SqlConnection conne = new SqlConnection(connectionString);
            String statement = "SELECT tblPlayer.FirstName, tblPlayer.LastName, SUM(tblRecords.Wins), SUM(tblRecords.Losses) FROM tblPlayer CROSS JOIN tblRecords GROUP BY FirstName,LastName";
            SqlDataAdapter adapt = new SqlDataAdapter(statement, conne);
            DataSet ds = new DataSet();
            adapt.Fill(ds);
            GridTest.DataSource = ds;
            GridTest.DataBind();
        }

This is my Record Save

protected void btnSubmitRecord_Click(object sender, EventArgs e)
        {
            // Perform user-defined checks.
            if (ddlWins.Text == "NA" || ddlLosses.Text == "NA" || ddlPlayers.Text == "NA")
            {
                lblStatus.Text = "Records require a Player, win, and loss.";
                return;
            }
            // Define ADO.NET objects.
            string insertSQL;
            insertSQL = "INSERT INTO tblRecords (";
            insertSQL += "Wins, Losses, PlayerId)";
            insertSQL += "VALUES (";
            insertSQL += "@WINS, @LOSSES, @PlayerId)";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(insertSQL, con);
            // Add the parameters.
            cmd.Parameters.AddWithValue("@Wins", ddlWins.Text);
            cmd.Parameters.AddWithValue("@Losses", ddlLosses.Text);
            cmd.Parameters.AddWithValue("@PlayerId", ddlPlayers.SelectedValue);

            // Try to open the database and execute the update.
            int added = 0;
            try
            {
                con.Open();
                added = cmd.ExecuteNonQuery();
                Response.Redirect(Request.RawUrl);
                lblStatus.Text = added.ToString() + " record inserted.";
            }
            catch (Exception err)
            {
                lblStatus.Text = "Error inserting record. ";
                lblStatus.Text += err.Message;
            }
            finally
            {
                con.Close();
            }
        }

[mail2saion]

Yes you can check no of wins in row_databound event. Like:

protected void gvEdit_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
RunningTotal += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Amount"));

if (e.Row.RowType == DataControlRowType.Footer)
{
((Label)e.Row.FindControl("lblRTotal")).Text = RunningTotal.ToString();
((Label)e.Row.FindControl("lblGTotal")).Text = ViewState["GrandTotal"].ToString();
}
}

From above code consider label as image control.

[jbisono]

How is your code doing?