Click here to Skip to main content
14,458,932 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 6-Mar-19 9:51am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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:
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.
   
v2
Comments
Member 10696161 6-Mar-19 16:42pm
   
the System.ArgumentOutOfRangeException is called right in the line:
while (reader.Read())
            {                chart1.Series[0].Points.AddXY(reader.GetString("ORDER_NUMBER"), reader.GetDateTime("TOTAL_TIME"));            }

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100