AVG QUERY in c#

Question

Good morning, I'm developing a code that can get me out of my database in sql, the AVG of a certain column. The problem is that I'm not getting it, I think the problem is the query but I do not know how to solve it. I need help, thank you.

Here is the code:

String connectionString =
       "Data Source=localhost;" +
       "Initial Catalog=DB_SACC;" +
       "User id=sa;" +
       "Password=1234;";

        SqlConnection connection = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand();

        string textt = " USE [DB_SACC] SELECT AVG (Total_Divida) FROM t_pagamentos";

        cmd.CommandText = textt;

        connection.Open();

        cmd.Connection = connection;

        cmd.CommandType = CommandType.Text;

        cmd.ExecuteNonQuery();

        if (textt == null)
        {
            MessageBox.Show("nothing");
        }
        else
        {
            TextBox3.Text = textt;
        }

Hint: you are executing a query with `cmd.ExecuteNonQuery()` — Tamás Szabó, Apr 06 '17 at 08:38
Use `ExecuteScalar()` store its out put in some object and then access it — Jaydip Jadhav, Apr 06 '17 at 08:40

Yawar Murtaza · Answer 1 · 2017-04-07T09:12:11.763

Use cmd.ExecuteScalar() method instead:

decimal average = (decimal) cmd.ExecuteScalar();

cmd.ExecuteNonQuery(); only returns the number of rows effected where as what you want is to read the result set of SELECT statement.

I would also get rid of USE [DB_SACC] from your SELECT statement since you are defining the database name in your connection string.

EDIT

Your code should look like this:

string textt = "SELECT AVG (Total_Divida) FROM t_pagamentos";
cmd.CommandText = textt;
connection.Open();
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
decimal average = (decimal) cmd.ExecuteScalar();
if (textt == null)
{
    MessageBox.Show("nothing");
}
else
{
    TextBox3.Text = average.ToString();
}

EDIT 2:

try
{
    string textt = "SELECT AVG (Total_Divida) FROM t_pagamentos";
    cmd.CommandText = textt;
    connection.Open();
    cmd.Connection = connection;
    cmd.CommandType = CommandType.Text;

    decimal average = (decimal)cmd.ExecuteScalar();
    TextBox3.Text = average.ToString();
}
catch(Exception ex)
{
     // log your exception here...
     MessageBox.Show("nothing");
}

EDIT 3:

In the light of your recent comments try this

string connectionString = "Data Source=localhost; Initial Catalog=DB_SACC; User id=sa Password=1234;";
        decimal? average;
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    string textt = "SELECT AVG (Total_Divida) AS 'AVG_DIVIDA' FROM t_pagamentos";
                    cmd.CommandText = textt;
                    connection.Open();
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;

                    using (DataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            average = decimal.parse(reader["AVG_DIVIDA"].ToString());
                            break;
                        }
                    }
                }
            }


            TextBox3.Text = average.HasValue ? average.ToString() : "Unknown error occurred";

        }
        catch (Exception ex)
        {
            MessageBox.Show("Unable to retrieve the average, reason: " + ex.Message);
        }

Note: Using DataReader to get just single value from database is not preferred. I am proposing this because of the error you mentioned in the comments.

If you are getting any SQL Exception then try to run this statement on SQL Server as stand alone test.

USE DB_SACC
GO

SELECT AVG (Total_Divida) AS 'AVG_DIVIDA' FROM t_pagamentos
GO

If you still encounter any error while executing T-SQL Statement then please post that as another question.

I had a problem with the code. Is giving the following error: column not found. help please — Diogo Gomes, Apr 07 '17 at 07:39
Could you run the select statement on the sql server and see if it works? — Yawar Murtaza, Apr 07 '17 at 08:09
it gives this error : Msg 208, Level 16, State 1, Line 1 Invalid object name 't_pagamentos' — Diogo Gomes, Apr 07 '17 at 08:11
Thats your table which looks like sql server is unable to find. Try running this before the select statement — Yawar Murtaza, Apr 07 '17 at 08:13
Now the query is well-scanned in the database, but I continue with the error despite having changed the query in my code — Diogo Gomes, Apr 07 '17 at 08:20
What do you mean by "well-scanned"? How could you continue with error? It could have knock-on effect on your program. I have updated my answer with EDIT 3 for you. — Yawar Murtaza, Apr 07 '17 at 09:14

fubo · Answer 2 · 2017-04-06T08:48:22.447

use ExecuteScalar if you request a single value from your database - ExecuteNonQuery returns just the number of affected rows which is used in update / insert statements
USE [DB_SACC] is not required in your query since you define the "Initial Catalog=DB_SACC;"
add using to avoid open connections

Code:

string connectionString =  "Data Source=localhost;Initial Catalog=DB_SACC;User id=sa;Password=1234;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string textt = "SELECT AVG (Total_Divida) FROM t_pagamentos";
    using (SqlCommand cmd = new SqlCommand(textt, connection))
    {
        connection.Open();
        var result =  cmd.ExecuteScalar(); //write the result into a variable

        if (result == null)
        {
            MessageBox.Show("nothing");
        }
        else
        {
            TextBox3.Text = result.ToString();
        }
    }
}

I had a problem with the code. Is giving the following error: column not found. help please — Diogo Gomes, Apr 07 '17 at 07:46

AVG QUERY in c#

2 Answers2