CODEWITHSUNDEEP
c#mysqlsumlabel
Troy Scafuro
Troy Scafuro

Reputation: 13

c# mysql sum query to label

I'm trying to do something I feel is quite simple in the grand scheme of things, however I'm clearly missing something. What I have is a simple database named 'localcollection'. What I would like to do is sum up the dollar amount of a column named 'purprice', and setting it as the text of a label (label4). I've been finding variants of code throughout the last couple days that suggest different ways of achieving this. The majority of my digging suggest that using ExecuteScalar is what I want to do. The code that I've been fumbling with follows.

SqlCeConnection myconn = new SqlCeConnection(Properties.Settings.Default.localbotdbConnectionString);
myconn.Open();

{
    string result = "select sum(purprice) from localcollection";
    SqlCeCommand showresult = new SqlCeCommand(result, myconn);

    label4.Text = showresult.ExecuteScalar().ToString();
    myconn.Close();
}

Others suggest using the SqlCeReader. I'm impartial to either of them, as long as one of them works, and I am clearly missing something (fault of my own). The reader rendition that I was trying to make work is:

SqlCeCommand cmd = new SqlCeCommand("select sum(purprice) from localcollection");
SqlCeDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
    label4.Text = reader.GetString(0);
}
myconn.Close();

Constructive advice appreciated. Thank you

Upvotes: 1

Views: 4250

Answers (6)

Innsomnia
Innsomnia

Reputation: 1

Just add AS after SUM() like the row below:

select sum(purprice) AS purprice from localcollection

And you are good to go.

Upvotes: 0

Loubna H
Loubna H

Reputation: 15

I think your code is ok, but you missed : 

  SqlCeCommand cmd = new SqlCeCommand("select sum(purprice) from localcollection",myconn);

that's all, hope it works

Upvotes: 0

Bearcat9425
Bearcat9425

Reputation: 1600

I would do some modifications to your code because for one thing your are not properly disposing of your objects, also you stated that you have it in the button click method which I would get that out of there and make this its own function.

private string performSQL() 
{
   string result = "select sum(purprice) from localcollection";
   using (SqlCeConnection myconn = new SqlCeConnection("ConnectionString"))
   using (SqlCeCommand showresult = new SqlCeCommand(result, myconn))
   {
      try
      {
          myconn.Open();
          return showresult.ExecuteScalar().ToString();

      }catch(System.Exception ex)
      {
          MessageBox.Show(ex.ToString());
          // or log exception how ever you prefer
      }finally
      {
          //the finally ensures your connection gets closed
          myconn.Close();
      }
   }
   return "";
}

Upvotes: 1

AlexDev
AlexDev

Reputation: 4717

Make sure the name of the column you are trying to add is purprice, and that it is a numeric type. Also make sure it doesn't contain NULL values.

Upvotes: 0

Christian Phillips
Christian Phillips

Reputation: 18749

If you are only looking to return one value from a query, then ExecuteScalar is what you should be using, . The ExecuteReader is better for forward-only reads of multiple records, so it's overkill for your situation

Take a look here for a comparisson What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

Upvotes: 2

bgeveritt
bgeveritt

Reputation: 303

Best practice, use ExecuteScalar when you are returning 1 row and 1 column of data (which your query does.) As a result, go with ExecuteScalar.

Upvotes: 0

Related Questions