Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sum in sql

Posted on 2013-11-13
16
Medium Priority
?
267 Views
Last Modified: 2013-11-20
Hello,
I need some help with coding in C#.
I'm trying to pull out a sum value from an access database. I'm pretty sure I have the sql statement correct cause I tried it in access itself. but when I ran it in visual studio I get an error msg. I know there is something wrong with my code, I just don't know how to correct it.
All I want it to do is go into the my access database and add up (sum) the numbers in the 'TotalDown' column and just return the number. I then will display that number in a textbox.
Please see attached.
Please help.
Capture.JPG
0
Comment
Question by:MrMay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39645051
I would name the actual column:

eg:  

select sum(totalDown) totalDown from ...

What do you get if you do this as a string?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39645121
Hi,

string str = "SELECT sum(TotalDown) FROM Tasks WHERE TaskType=3 AND ScheduleStart between # " + dateTimePicker1.Text + "# AND # " + dateTimePicker2.Text

Did you have a final # character when you ran this SQL statement within an MS-Access query?

i.e.

SELECT
sum(TotalDown)
FROM
Tasks
WHERE
TaskType=3
AND
ScheduleStart between #(date here, preferably in [DD-MMM-YYYY] format)# AND #(another [DD-MMM-YYYY] format date here)#

BFN,

fp.
0
 

Author Comment

by:MrMay
ID: 39645127
I'm sure the sql statment is correct. I've use it in another portion of my code, where I just use count to get the number of rows, but now I'm using the same sql statement to get the sum of the numbers in the column... I just don't know how to display the result.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39645192
Does that mean the last # character is within the string, but we just cannot see it in your JPeG image?
0
 

Author Comment

by:MrMay
ID: 39645207
re took the pic... please see attached.
Capture.JPG
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39645289
Thanks.  Just a 'yes' response would have done though! :)

What is the value of dateTimePicker1.Text and dateTimePicker2.Text?
0
 

Author Comment

by:MrMay
ID: 39645302
just randon.... I'm doing Oct 10 2013 to nov 12, 2013.
0
 

Author Comment

by:MrMay
ID: 39645303
any date that I choose, I get the same error msg.
I think it has something to do with the fact that in the code... its row.count... and I'm doing a sum not a count.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39645437
Hi
1. Did you check the value in debug mode using quick watch ?  I mean that in the right side of (int)
2. If it stills seems a number,  just give a try by apply trim before casing. Suspecting Any space which makes it as a string which fails in casting?

Raj
From Mobile
0
 

Author Comment

by:MrMay
ID: 39645468
Raj.. i don't understand....
0
 

Author Comment

by:MrMay
ID: 39645588
Let me try to explain this again a bit more clearer.
Let's say I have an access table called Students, that looks like so.

Name                         Age    
Mike                           5
Kevin                          2
Kelly                           10


Now, I want to display the sum of the Age column.   So the result would be 17.
My query statment would be.... select sum(Age) from Students.
Now, I want to display the result (the sum which is 17) in a textbox when a user presses a button.    How can I write the c# that will do this?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39645607
1. Need to check the value of
ds.Tables["Tasks"].Rows[ds.Tables["Tasks"].Rows.Count - 1][0]
which is causing this error on casting to int
For this once you get the crash, try to find out the value using Quick watch feature of Visual Studio
or place mouse cursor over 'ds' which will popup option to view the contents of that dataset.

2. This one I meant as below
(int)ds.Tables["Tasks"].Rows[ds.Tables["Tasks"].Rows.Count - 1][0].trim();
not sure about this

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39645643
I suggest to change your C# code by using ExecuteScalar which will be suitable to return single value.
<competing site link removed - GaryC123>
0
 

Author Comment

by:MrMay
ID: 39645693
Hey Raj... I looked at that code in the link that u send....
I don't have the GetConnection. method like its in that example.
  cmd1.Connection = GetConnection();
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 1500 total points
ID: 39645952
OleDbCommand cmd1 = new OleDbCommand();
//this is your object 2nd after the try
cmd1.Connection = connection;
cmd1.CommandText = str;

connection.Open();
newNum1 = cmd1.ExecuteScalar();
connection.Close();
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39646927
GetConnection(); returns a connection. Instead of that you use a connection object directly


string connection = "your connection string";
OleDbConnection conn = new OleDbConnection(connection);
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = conn ;
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
var result = cmd1.ExecuteScalar();

Open in new window

0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question