C# create chart from datagridview from database

Question

0.00/5 (No votes)

See more:

I'm trying create column chart from datagridview which downloads DataSet from database. Is datagridview 2. I've written in public class void loaddata(). As for as chart is concert i wrote by chart() public void class. I want display:

XvalueMember : ORDER_NUMBER
YvalueMember: TOTAL_TIME

There is my code:

GenerateCharts.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Windows.Forms.DataVisualization.Charting;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Text.RegularExpressions;

namespace ControlBase
{
    public partial class GenerateCharts : Form
    {
        public GenerateCharts()
        {
            InitializeComponent();
        }
 public void loaddata()
        {
            try
            {
                MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");
 MySqlDataAdapter adapter2 = new MySqlDataAdapter("SELECT w.FNAME, w.LNAME, z.ORDER_DESC AS 'ORDER DESCRIPTION', o.ORDER_NUMBER AS 'ORDER NUMBER', SEC_TO_TIME(SUM(TIME_TO_SEC(s.BEGIN_DATE) - TIME_TO_SEC(s.END_DATE))) AS 'TOTAL TIME OF ORDER' FROM projekt1.status_order s INNER JOIN projekt1.workers p ON s.ID_WORKER = w.ID_WORKER INNER JOIN projekt1.orders z ON s.ID_ORDER = o.ID_ORDER WHERE p.ID_WORKER ='" + int.Parse(textBox1.Text)+ "' AND BEGIN_DATE >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND END_DATE <= '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' GROUP BY s.ID_ORDER;", connection);
            connection.Open();

                DataSet ds2 = new DataSet();
                adapter2.Fill(ds2, "status_order");
                dataGridView2.DataSource = ds2.Tables["status_order"];
            connection.Close();
            }
            catch (Exception ex)
            {
            MessageBox.Show(ex.Message);
            }
        }

        public void chart()
        {

            MySqlConnection connection = new MySqlConnection();
            connection.ConnectionString = "datasource=localhost;port=3306;username=root;password=";
            connection.Open();

            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT w.FNAME, w.LNAME, z.ORDER_DESC AS 'ORDER DESCRIPTION', o.ORDER_NUMBER AS 'ORDER NUMBER', SEC_TO_TIME(SUM(TIME_TO_SEC(s.BEGIN_DATE) - TIME_TO_SEC(s.END_DATE))) AS 'TOTAL_TIME' FROM projekt1.status_order s INNER JOIN projekt1.workers p ON s.ID_WORKER = w.ID_WORKER INNER JOIN projekt1.orders z ON s.ID_ORDER = o.ID_ORDER WHERE p.ID_WORKER ='" + int.Parse(textBox1.Text)+ "' AND BEGIN_DATE >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND END_DATE <= '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' GROUP BY s.ID_ORDER;";

            MySqlDataReader reader;

            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                chart1.Series[0].Points.AddXY(reader.GetString("ORDER_NUMBER"), reader.GetDateTime("TOTAL_TIME"));
            }

        }
private void button1_Click(object sender, EventArgs e)
        {
            chart1.Series.Clear();
            chart();
            loaddata();

        }
   }
}

What I have tried:

I've tried by writing in public class chart():

public void chart()
        {

            MySqlConnection connection = new MySqlConnection();
            connection.ConnectionString = "datasource=localhost;port=3306;username=root;password=";
            connection.Open();

            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT w.FNAME, w.LNAME, z.ORDER_DESC AS 'ORDER DESCRIPTION', o.ORDER_NUMBER AS 'ORDER NUMBER', SEC_TO_TIME(SUM(TIME_TO_SEC(s.BEGIN_DATE) - TIME_TO_SEC(s.END_DATE))) AS 'TOTAL_TIME' FROM projekt1.status_order s INNER JOIN projekt1.workers p ON s.ID_WORKER = w.ID_WORKER INNER JOIN projekt1.orders z ON s.ID_ORDER = o.ID_ORDER WHERE p.ID_WORKER ='" + int.Parse(textBox1.Text)+ "' AND BEGIN_DATE >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND END_DATE <= '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' GROUP BY s.ID_ORDER;";

            MySqlDataReader reader;

            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                chart1.Series[0].Points.AddXY(reader.GetString("ORDER_NUMBER"), reader.GetDateTime("TOTAL_TIME"));
            }

        }

But After launching this app and trying to generate a column i have error:

System.ArgumentOutOfRangeException: "The index was out of range. It must have a non-negative value and less than the size of the collection. Parameter name: index "

I've tried by getting data from datagridview2 too but it haven't worked. Can someone explain how to change that code? Thx for any help.

Posted 6-Mar-19 8:33am

Member 10696161

Updated 6-Mar-19 8:51am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2019-03-06T08:52:00

The very first thing you must do is performa parameterized query. What that means is creating an array of SqlParameter objects, and passing them to the SqlCommand object. Finally, you can replace all of the hard coded values with the appropriate parameter names, something like this:

C#

SqlParameter[] parameters = new SqlParameter[]
{
    new SqlParameter("@variable1", datetimepicker1),
    new SqlParameter("@variable2", datetimepicker2),
};

string query = "select * from mytable where somefield = @variable1 and someotherfield = @variable2";

using (SqlConnection conn = new SqlConnection(myconnectionstring))
{
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddRange(parameters);
        .... the rest of your code
    }  
}

The code above is for a real database, so you'll have to adapt it to your hippy-dippy my-sql stuff.

Then if your code is still failing, come back and TELL US WHERE IT'S FAILING, because we're not freakin' mind readers.