1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Troy Scafuro
  • 13
  • 1
  • 6

6 Answers6

2

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?

Community
  • 1
  • 1
Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
  • Using the first method (ExecutScalar) as a button click action, the line label4.Text = showresult.ExecuteScalar().ToString(); is highlighted with the following "The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = sum ]" – Troy Scafuro Jul 24 '13 at 13:16
  • You should really wrap this up in a method call, GetLocalCollectionPrice which returns the value, and set the text of the label by calling the method, you can abstract further, but just a simple step; e.g. label4.Text = GetLocalCollectionPrice(); in the codebehind. – Christian Phillips Jul 24 '13 at 13:21
  • Is this query valid in the DB 'select sum(purprice) from localcollection' – Christian Phillips Jul 24 '13 at 13:35
0

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.

bgeveritt
  • 303
  • 5
  • 18
0

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.

AlexDev
  • 4,049
  • 31
  • 36
0

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 "";
}
Bearcat9425
  • 1,580
  • 1
  • 11
  • 12
  • The end goal would be to take it out of the button, but for testing purposes I put it in one. Let me do some testing and I will be back with results. Thank you all for you suggestions – Troy Scafuro Jul 24 '13 at 13:38
  • I see I still would make use of the using statements otherwise your not going to dispose of your objects and they will hold onto resources. – Bearcat9425 Jul 24 '13 at 13:40
  • If you're going to call it PerformSQL, then it should take the SQL as a param PerformSQL(string sqlToRun) -> string result = PerformSQL("select sum(purprice) from localcollection"); – Christian Phillips Jul 24 '13 at 13:56
  • The above solution by Bearcat9425 did work. Thank you. But also thank you in regard to the other comments as well, as I did take all of them into consideration. Thank you – Troy Scafuro Jul 24 '13 at 14:32
  • Naming is a simple solution the ultimate goal was to take it out of the button click function and make sure it was properly obtaining the information as well as disposing of resources properly. But yes if you wanted to "ambiguoufy" it you could have it take a SQL string query as a parameter. Otherwise to make it specific you could name it , GetSumPurprice(). – Bearcat9425 Jul 24 '13 at 14:41
0

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

Loubna H
  • 15
  • 7
0

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

select sum(purprice) AS purprice from localcollection

And you are good to go.

Innsomnia
  • 1