MrMay
asked on
Sum in sql
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
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
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.
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.
ASKER
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.
Does that mean the last # character is within the string, but we just cannot see it in your JPeG image?
ASKER
re took the pic... please see attached.
Capture.JPG
Capture.JPG
Thanks. Just a 'yes' response would have done though! :)
What is the value of dateTimePicker1.Text and dateTimePicker2.Text?
What is the value of dateTimePicker1.Text and dateTimePicker2.Text?
ASKER
just randon.... I'm doing Oct 10 2013 to nov 12, 2013.
ASKER
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.
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.
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
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
ASKER
Raj.. i don't understand....
ASKER
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?
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?
1. Need to check the value of
ds.Tables["Tasks"].Rows[ds .Tables["T asks"].Row s.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"].Ro ws[ds.Tabl es["Tasks" ].Rows.Cou nt - 1][0].trim();
not sure about this
Raj
ds.Tables["Tasks"].Rows[ds
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"].Ro
not sure about this
Raj
I suggest to change your C# code by using ExecuteScalar which will be suitable to return single value.
<competing site link removed - GaryC123>
<competing site link removed - GaryC123>
ASKER
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();
I don't have the GetConnection. method like its in that example.
cmd1.Connection = GetConnection();
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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();
eg:
select sum(totalDown) totalDown from ...
What do you get if you do this as a string?