Display Sum in Label

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

44 Replies - 2442 Views - Last Post: 08 February 2015 - 04:15 PM Rate Topic: -----

#1 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Display Sum in Label

Posted 30 January 2015 - 11:40 AM

Hey everyone, I am stuck on my program right now, what i am trying to do is get the sum of the amount from a access database table and than display it on a label, however i cant seem to get this to happen. This is what I have so far:

OleDbConnection conn = new OleDbConnection();
                conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Church.accdb";
                conn.Open();
                Form1 frm1 = new Form1();
                //frm1.Show();
                //string tableName = frm1.tableName;
                //string time = "4/5/1991";
                string fullName = memberscmb.Text;
                DateTime start_date = startCal.SelectionRange.Start;
                DateTime end_date = endCal.SelectionRange.Start;
                string[] word = fullName.Split(' ');

                string fName = word[0];
                string lName = word[1];
                string slName = word[2];
                string tName = fName + "_" + lName + "_" + slName + "_Finance";

                string my_querry = string.Format("Select * From {0} WHERE Submitted Between @startDate and @endDate", tName);
                OleDbCommand cmd = new OleDbCommand(my_querry, conn);
                cmd.Parameters.AddWithValue("@startDate", start_date);
                cmd.Parameters.AddWithValue("@endDate", end_date);
                cmd.CommandType = CommandType.Text;
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataTable allMembers = new DataTable();
                da.Fill(allMembers);
                dataGridView1.DataSource = allMembers;

                string query = string.Format("SELECT SUM(Amount) FROM @tName");
                
                OleDbCommand cmd2 = new OleDbCommand(query, conn);
                cmd2.Parameters.AddWithValue("@tName", tName);
                cmd2.CommandType = CommandType.Text;
                OleDbDataAdapter da2 = new OleDbDataAdapter(cmd2);
                DataTable amounts = new DataTable();
                da2.Fill(amounts);
                //{
                    amountLbl.Text = amounts.ToString();
                //}

                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Data Added");
                    conn.Close();
                }
                catch (OleDbException ex)
                {
                    MessageBox.Show(ex.Message);
                    conn.Close();
                }



If someone could help me out as to what I am doing wrong i would appreciate it!!

Is This A Good Question/Topic? 0
  • +

Replies To: Display Sum in Label

#2 tlhIn`toq  Icon User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6517
  • View blog
  • Posts: 14,405
  • Joined: 02-June 10

Re: Display Sum in Label

Posted 30 January 2015 - 11:55 AM

Are you getting an error? We can't help if you don't tell us what's broken or what's actually happening. "It doesn't work" gives us nothing to go on.
Also, why are you making a new Form1 instance?

Also, I would urge you to fix up this practice of using your textboxes like they are properties. See item 1 of this list of common rookie coder bad habits to avoid:


Looking at your code you have some bad practices in play that need your immediate attention.
These will form bad habits quickly and should be worked on. This is part of what I mean by "learn foundation concepts first".

Problem 1: Use of GUI controls as your variables.
You have numerous places where you are doing things like: SomeMethodCall(TextBox43.Text);
Your textboxes and other GUI elements are not meant to be your variables to hold values. They exist only to be a way for the user to interact with the data held in the logic code *behind* the GUI. You should have properties bound to the GUI. When the user changes something in a textbox it updates the property in your code. When the property gets a new value, the GUI is updated. But your logic always uses the property not the GUI element.
There is a tutorial on properties linked in my signature block.

Problem 2: This bit about button1 and textbox14 is just horrible. If you are working in old 1980's WinForms for Win98 and WinXP programs then name your control as soon as you make it before doing anything else with it. txtbxFirstName is so much easier to maintain than textbox32. Same with "frmConnectionSettings" instead of "form2" or "wndCustomerDetails" than "window4". If you are making programs for current OSes like Win7/8 then you should be working in WPF where the controls don't need to be named 90% of the time, but the same practice of giving meaningful names should still be followed in the few cases where they are needed.

Problem 3: Don't put meaningful operational code in GUI control handlers. You have a lot of places where the work is being done in the button event handler instead if in a dedicated method. Instead you want that handler to just direct action to the first methods, not actually perform the actions.
btnSave_Click(object sender, eventargs e)
{
   SaveSettings(); // Call the save method
   SavePlayers();
   SaveWindowGeometry();
   // Do NOT put all the save code here
}

This way you can call the same save methods independently from code without having to either duplicate code (evil practice) or without calling the button handler. If you are using WinForms then you have a .Click event handler. If you are writting in WPF then don't call the .Click event but instead create Commands and activate those. The .Click handler in WPF is kind of a carry-over to give WinForms coders a comfortable migration path but is not considered the way a modern profession WPF coder works.

Problem 4:
Eye sore code.
Take the extra 2 minutes to clean up your code to make it easy to read, easy to follow, and most important, easy to manage.
  • Kill all those dead blank lines at the ends of methods.
  • Wrap groups of methods that go together in #region blocks so you can find all your sending code and receiving code easily for example.
  • Get your lines aligned. Control-K, Control-D will do that for you (Think of it as Klean Document)


Problem 5: Decent naming
Methods like private void ClientSession(object sender, eventargs e); don't follow established naming style guides.
A method performs an action. By convention for decades methods are named as verbs to show what they do. Nouns like ClientSession would be presumed to be a property by any other coder looking at your code. private void CreateClientSession(object sender, eventargs e); on the other hand shows that it does something (a method) not that it is something (a variable).

I'm not going to go into a long critique of your code. And I don't want you to feel like I'm just out to pick on you. I'm just pointing out a couple of many foundation concepts you didn't pickup up in the early stages of learning C#. You can't leap-frog the learning process by trying to build a program in your first month. Nobody can. Its not just you. Nobody in the first month of a new discipline has enough knowledge to jump in and start designing finished solutions: Not carpentry, not automotive design, not software engineering. C# is a foreign language with its own syntax. You aren't going to say "I know no Russian. I'm going to write a Russian mystery novel as my vehicle to learn the language." Same here with programming.

I urge you to take a step back. Pick up a "Learn C# in 30 days" type book and work your way through it cover to cover; don't use it a like a reference dictionary when you get stuck. There are several in a thread pinned to head of the C# forum. Plus there are countless C# beginner tutorial series around just like the C# Learning Series - tutorials here on DIC. There are also numerous "My First C# Program" tutorials like this one that will help you with the design and OOP concepts that are vital to good programming.

Was This Post Helpful? 0
  • +
  • -

#3 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 6009
  • View blog
  • Posts: 20,649
  • Joined: 05-May 12

Re: Display Sum in Label

Posted 30 January 2015 - 05:50 PM

Yes, what are you doing wrong is assuming that doing a ToString() on a DataTable will return a string representation of the contents of the table.
Was This Post Helpful? 0
  • +
  • -

#4 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: Display Sum in Label

Posted 31 January 2015 - 05:20 PM

sorry i apologize for that, the error I am getting is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in query. Incomplete query clause.

Also thank you I will clean up my code, however right now I am just making sure these little things work before I make everything official. :)
Was This Post Helpful? 0
  • +
  • -

#5 tlhIn`toq  Icon User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6517
  • View blog
  • Posts: 14,405
  • Joined: 02-June 10

Re: Display Sum in Label

Posted 31 January 2015 - 06:46 PM

So... Now that you have the error you know where to look... At your query. The error says the query is incomplete. So there ya go.

If you put a breakpoint on line 22 you can look at the complete query. Does it look right? Are the dates formatted correctly? Because you're not doing anything special to make sure they are, so that means they are going to fall back to the default for the PC. What if the PC is set up to do 31-jan15... or 01/31/2015? As soon as you have that / character its going to take it as an escapte character, right? Don't you need to format characters like that to something acceptable for a query?
Was This Post Helpful? 0
  • +
  • -

#6 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: Display Sum in Label

Posted 31 January 2015 - 08:57 PM

The error is falling somewhere between line 28 - 37. It has something to do with the way the sql statment is being sent over
Was This Post Helpful? 0
  • +
  • -

#7 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 6009
  • View blog
  • Posts: 20,649
  • Joined: 05-May 12

Re: Display Sum in Label

Posted 31 January 2015 - 09:09 PM

Compare your line 18 vs. your line 28. Why are you passing the table name in as a command parameter for the query on line 28, while on line 18, you were using string formatting to insert the table name into the query?

And on a much higher level, something is very wrong with your database design if you are creating one table each for every account. Somebody must have been sleeping through their database design class.
Was This Post Helpful? 0
  • +
  • -

#8 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: Display Sum in Label

Posted 31 January 2015 - 09:21 PM

what should it be than? sorry im still pretty new at this :dontgetit:
Was This Post Helpful? 0
  • +
  • -

#9 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 6009
  • View blog
  • Posts: 20,649
  • Joined: 05-May 12

Re: Display Sum in Label

Posted 31 January 2015 - 09:33 PM

If you are asking that, this is turning out to be more of SQL question, rather than a C# question.

You could do a simple test... which of the two approaches works?
Was This Post Helpful? 0
  • +
  • -

#10 tlhIn`toq  Icon User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6517
  • View blog
  • Posts: 14,405
  • Joined: 02-June 10

Re: Display Sum in Label

Posted 01 February 2015 - 06:09 AM

View PostBrandon222, on 31 January 2015 - 10:21 PM, said:

what should it be than? sorry im still pretty new at this :dontgetit:/>


The most best answer to that is: Read the SQL documentation. It sounds like you need to stop copy/pasting code & queries and start reading for UNDERSTANDING.
Was This Post Helpful? 0
  • +
  • -

#11 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5102
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Display Sum in Label

Posted 02 February 2015 - 12:03 PM

I can't teach a relational databases course in a comment (or at all, really), but the general idea is that you have one table that holds account information (each row is an account) with some auto-generated ID column (an int/bigint or uniqueidentifier are the most common and best), and another table/tables that contain actual data; you would "join" them together with queries.
Was This Post Helpful? 0
  • +
  • -

#12 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: Display Sum in Label

Posted 02 February 2015 - 02:26 PM

There is no copy and paste here, I know what I am doing in terms of SQL, the command is correct the problem I having is displaying that information in a label. I can input it into a grid box but not into a label. That is where I am having the problem. I do not need an explanation on how SQL works, or what is Sum command I know that. So I am sorry if I did not explain that properly, but that is where I am stuck!
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6492
  • View blog
  • Posts: 26,267
  • Joined: 12-December 12

Re: Display Sum in Label

Posted 02 February 2015 - 02:44 PM

You cannot supply a table-name as a parameter, that is why (or a main reason why) the first code section worked (using string.Format) but the second section doesn't.
Was This Post Helpful? 3
  • +
  • -

#14 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6492
  • View blog
  • Posts: 26,267
  • Joined: 12-December 12

Re: Display Sum in Label

Posted 02 February 2015 - 02:57 PM

I'm a little curious about these:
    DateTime start_date = startCal.SelectionRange.Start;
    DateTime end_date = endCal.SelectionRange.Start;

I assume they are MonthCalendars. It seems a little odd that you would use Start for the start_date but not End for the end_date. Thought I'd mention it in case there is an issue here.

It's your decision of course, but, personally, I think it would take a little effort to get two MonthCalendars to play well together.
Was This Post Helpful? 1
  • +
  • -

#15 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 6009
  • View blog
  • Posts: 20,649
  • Joined: 05-May 12

Re: Display Sum in Label

Posted 02 February 2015 - 03:27 PM

View PostBrandon222, on 02 February 2015 - 04:26 PM, said:

I know what I am doing in terms of SQL, the command is correct the problem I having is displaying that information in a label. I can input it into a grid box but not into a label. That is where I am having the problem. I do not need an explanation on how SQL works, or what is Sum command I know that. So I am sorry if I did not explain that properly, but that is where I am stuck!


If that were true, then you wouldn't have an error you reported in post #4:

View PostBrandon222, on 31 January 2015 - 07:20 PM, said:

..., the error I am getting is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in query. Incomplete query clause.

Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3