CODEWITHSUNDEEP
c#sqlasp.net.netsql-server
Ziaul Kabir Fahad
Ziaul Kabir Fahad

Reputation: 230

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

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?

Upvotes: 0

Views: 7128

Answers (2)

Ziaul Kabir Fahad
Ziaul Kabir Fahad

Reputation: 230

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.

Upvotes: 0

granadaCoder
granadaCoder

Reputation: 27874

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

Upvotes: 4

Related Questions