How to display a query in a textbox in C# with connection of SQL

Question

Hi I was working in C# I want to a button for calculate average of some numbers.

Here is my code:
private void button5_Click(object sender, EventArgs e)
    {
        connect.Open();
        string avg= "SELECT AVG(number) FROM info WHERE number = @number";

        SqlCommand command= new SqlCommand(avg, connect);
        SqlDataAdapter da = new SqlDataAdapter(command);

        textBox8.Text = ; //I dont know what should I write here.

        connect.Close();
I want to button to display the average of numbers in a textbox. How should I do? Can you help me please?

EDIT

I fixed the problem:

private void button5_Click(object sender, EventArgs e)
        {
            connect.Open();
            SqlCommand command= new SqlCommand("SELECT AVG(number) FROM info", connect);
            SqlDataReader dr = command.ExecuteReader();
            SqlDataAdapter da = new SqlDataAdapter(command);
            if (dr.Read())
            {
                textBox8.Text = dr[0].ToString();
            }
            connect.Close();
        }
    }
}

Steve · Accepted Answer · 2022-01-19 22:46:32Z

2

The code lacks not only the setting of the query result but also other parts necessary to execute the query.

private void button5_Click(object sender, EventArgs e)
{
    connect.Open();
    string avg= "SELECT AVG(number) FROM info WHERE number = @number";

    SqlCommand command= new SqlCommand(avg, connect);
    // You need to add a parameter for @number and set its value 
    command.Parameters.Add(@number, SqlDbType.Int).Value = valueForNumber;

    // Now you need to execute the command to get back the average value
    object result = command.ExecuteScalar();

    // Finally you set the textbox with a conversion in string of the returned value
    // but also check if there is really a return value
    if(result != null)
       textBox8.Text = result.ToString(); 
    else
       textBox8.Text = "Number Not found!";        
    connect.Close();
}

But there are other problems with that code.
When you deal with Disposable objects you should have a red flag in mind. Disposable objects "usually" contains resources that should be freed as soon as possible.
The connection and the command are this kind of objects. In the original code it seems that the connection is a global object. This should be avoided with code like this

private void button5_Click(object sender, EventArgs e)
{
    using(SqlConnection cnn = new SqlConnection(getTheConnectionString())
    {
        connect.Open();
        string avg= "SELECT AVG(number) FROM info WHERE number = @number";

        using(SqlCommand command= new SqlCommand(avg, cnn))
        {
          // You need to add a parameter for @number and set its value 
          command.Parameters.Add("@number", SqlDbType.Int).Value = valueForNumber;

          // Now you need to execute the command to get back the average value
          object result = command.ExecuteScalar();
   
          // Finally you set the textbox with a conversion in string of the returned value
          // but also check if there is really a return value
          if(result != null)
             textBox8.Text = result.ToString(); 
          else
             textBox8.Text = "Number Not found!";
       } // End using => SqlCommand disposed...
    } // End using => SqlConnection closed and disposed
}

The using blocks keep track of disposable objects and ensure that a proper dispose of these objects happens when the code exits the using block even if an exception occurs inside the blocks.

edited Jan 19, 2022 at 22:46

answered Jan 16, 2022 at 23:03

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

surrealduck Over a year ago

Hi, I wrote as you said but I get a error like this in Int32 : ''object' does not contain an Int32 definition and no accessible extension method 'Int32' was found that accepts a first argument of type 'object' (could be missing a using directive or assembly reference?)' I did not use Int32 before. I'm new in C# ;/.

Steve Over a year ago

Sorry for the delay. I have fixed the code manually typed above. Review the line that adds the parameter please.

surrealduck Over a year ago

Hi @Steve Thank you for the help! I fixed the problem. I edited the question. Thank you again!

Collectives™ on Stack Overflow

How to display a query in a textbox in C# with connection of SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related