How to get sum of numbers using SQL query in ASP.NET C#?

Question

I am beginner in ASP.NET Web Development.Now I want to find sum of marks using SQL sum query. but It finds -1 as sum after query execution. Here is my code:

public double GetTotalScore(string regNo)
{
    SqlConnection connection=new SqlConnection(ConnectionString);
    string query = "select sum(Score) from SaveResult where RegNo='" + regNo + "' group by RegNo";
    SqlCommand command = new SqlCommand(query, connection);
    connection.Open();
    double total = command.ExecuteNonQuery();
    connection.Close();
    return total;
}

How could i get sum using sql query?

Is there a single line of code that is asp.ne specific - or do you add random tags for technologies you just can not identify because you do not bother to do so? That is not asp.net, it is ado.net. It may be in a web application, it still is ado.net. — TomTom
– TomTom, Commented May 9, 2016 at 17:47
Just a note on the -1 bit. If you look at the docs you'll see the return value to ExecuteNonQuery is "The number of rows affected." In the remarks section it explains For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. and then later For all other types of statements, the return value is -1. Since a SELECT isn't a UPDATE, INSERT or DELETE it is a "other type of statement" — Conrad Frix
– Conrad Frix, Commented May 9, 2016 at 17:55

granadaCoder · Accepted Answer · 2016-05-09 20:44:40Z

4

ExecuteNonQuery returns the number of rows affected.

ExecuteScalar is the method you want. It returns a single-value (a "scalar") to you.

public double GetTotalScore(string regNo)
    {
        SqlConnection connection=new SqlConnection(ConnectionString);

        string query = "select sum(Score) from SaveResult where RegNo='" + regNo + "' group by RegNo";
        SqlCommand command = new SqlCommand(query, connection);
        connection.Open();
        double total =  (double)cmd.ExecuteScalar();
        connection.Close();
        return total;
    }

You still have a problem.

   string query = "select sum(Score) from SaveResult where RegNo='" + regNo + "' group by RegNo";

You need to change to parameterized queries.

   string query = "select sum(Score) from SaveResult where RegNo='@MyParameter' group by RegNo";


        SqlParameter param  = new SqlParameter();
        param.ParameterName = "@MyParameter";
        param.Value         = "myvalue";


        cmd.Parameters.Add(param);

/* now call the ExecuteScalar */

See

http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson06

edited May 9, 2016 at 20:44

answered May 9, 2016 at 17:47

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TomTom Over a year ago

Not an answer. Answers have to explain.

Conrad Frix Over a year ago

what no reference to parameterized queries?

Suji Over a year ago

beware of injection RegNo='" + regNo + "'

granadaCoder Over a year ago

Rowdy bunch today.

MethodMan Over a year ago

does the OP expect whom ever answers to provide links as well as answers to every example.. I think that this answer provides an excellent starting point and if the OP needs more this is what GOOGLE and or other search engines are out there for.. +1 btw

Ziaul Kabir Fahad · Accepted Answer · 2016-05-10 06:28:48Z

0

public string GetTotalScore(string regNo)
    {
        SqlConnection connection=new SqlConnection(ConnectionString);
        string query = "select sum(Score) from SaveResult where RegNo='"+regNo+"' group by RegNo";
        SqlCommand command = new SqlCommand(query, connection);  
        connection.Open();
        object total = command.ExecuteScalar();
        connection.Close();
        return Convert.ToString(total);
    }    
}

It works,to getting sum using ExecuteScalar.

answered May 10, 2016 at 6:28

Ziaul Kabir Fahad

2303 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to get sum of numbers using SQL query in ASP.NET C#?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related