locked
Filter date range from calendar to display in ajax chart reading from database RRS feed

  • Question

  • User1391648435 posted

    I recently able to work out how to filter date range from calendar to display in asp chart from database, but i tried converting to using ajax and i am struggling to convert.
    Tried googling and stuff but it doesn't really show me much of it.
    I hope you guys might be able to help me out.

    This is the code behind for my chart

    protected void BindChart1()
    {
    string cs = ConfigurationManager.ConnectionStrings["impgardenConnectionString"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(cs))
    {
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    con.Open();
    using (MySqlCommand com = new MySqlCommand("SELECT Id,Temperature,ReadDateTime FROM smartterranium WHERE ReadDateTime BETWEEN @ViewDateFrom and @ViewDateTo ", con))
    {
    com.Parameters.AddWithValue("@ViewDateFrom", tbViewDateFrom.Text);
    com.Parameters.AddWithValue("@ViewDateTo", tbViewDateTo.Text);
    
    using (MySqlDataReader reader = com.ExecuteReader())
    {
    while (reader.Read())
    {
    LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries { LineColor = "#3dc0f4", Name = "Suppliers" });
    
    }
    }
    }
    }
    }

    This is the ajax datepicker.

     <asp:Label ID="lblDateFrom" runat="server" Text="View Date From:"></asp:Label>&nbsp;&nbsp;&nbsp;
             <asp:TextBox ID="tbViewDateFrom" runat="server" AutoPostBack="True"></asp:TextBox>
               <ajaxToolkit:CalendarExtender ID="CalExtViewDateFrom" runat="server"  BehaviorID="CalExtViewDateFrom" TargetControlID="tbViewDateFrom" Format="dd/MM/yy" />
    
                  <asp:Label ID="lblDateTo" runat="server" Text="View Date To:"></asp:Label>&nbsp;&nbsp;&nbsp;
             <asp:TextBox ID="tbViewDateTo" runat="server" AutoPostBack="True"></asp:TextBox>
               <ajaxToolkit:CalendarExtender ID="CalExtViewDateTo" runat="server"  BehaviorID="CalExtViewDateTo" TargetControlID="tbViewDateTo" Format="dd/MM/yy" />
                <asp:Button ID="btnSubmit" runat="server" Text="Filter" OnClick="btnSubmit_Click" />

    This is the ajax calendar i want to use

    ajaxToolkit:LineChart ID="LineChart1" runat="server"
              ChartTitleColor="#0E426C" CategoryAxisLineColor="#D08AD9"
              ValueAxisLineColor="#D08AD9" BaseLineColor="#A156AB"
              ChartTitle="Temperature"
              ChartHeight="300" ChartWidth="450" ChartType="Basic">
          </ajaxToolkit:LineChart>
                

    Thanks alot!

    Tuesday, March 28, 2017 4:17 AM

All replies

  • User-707554951 posted

    Hi JovanYong.

    Based on your description and code. I make a sample.it woks well on my side. you could refer to it:

    TemperatureTable in database as below:

    Then I use the following code:

    <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>
    
     <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
            <asp:Label ID="lblDateFrom" runat="server" Text="View Date From:"></asp:Label>&nbsp;&nbsp;&nbsp;
             <asp:TextBox ID="tbViewDateFrom" runat="server" EnableViewState="false" ></asp:TextBox>
               <ajaxToolkit:CalendarExtender ID="CalExtViewDateFrom" runat="server"  BehaviorID="CalExtViewDateFrom" TargetControlID="tbViewDateFrom" Format="dd/MM/yy" />
    
                  <asp:Label ID="lblDateTo" runat="server" Text="View Date To:"></asp:Label>&nbsp;&nbsp;&nbsp;
             <asp:TextBox ID="tbViewDateTo" runat="server"  EnableViewState="false"></asp:TextBox>
               <ajaxToolkit:CalendarExtender ID="CalExtViewDateTo" runat="server"  BehaviorID="CalExtViewDateTo" TargetControlID="tbViewDateTo" Format="dd/MM/yy" />
                <asp:Button ID="btnSubmit" runat="server" Text="Filter"  OnClick="btnSubmit_Click" />
         <br />
            <ajaxToolkit:LineChart ID="LineChart1" runat="server"
              ChartTitleColor="#0E426C" CategoryAxisLineColor="#D08AD9"
              ValueAxisLineColor="#D08AD9" BaseLineColor="#A156AB"
              ChartTitle="Temperature"
              ChartHeight="300" ChartWidth="350" ChartType="Basic" Visible="false">
          </ajaxToolkit:LineChart>
    
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>

    CodeBehind:

     protected void btnSubmit_Click(object sender, EventArgs e)
            {
                BindChart1();
            }
            protected void BindChart1()
            {
                string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                string query = "SELECT Id,Temperature,ReadDateTime FROM TemperatureTable WHERE ReadDateTime BETWEEN @ViewDateFrom AND @ViewDateTo";
    
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Parameters.AddWithValue("@ViewDateFrom", Convert.ToDateTime(tbViewDateFrom.Text, new CultureInfo("en-GB")));
                        cmd.Parameters.AddWithValue("@ViewDateTo", Convert.ToDateTime(tbViewDateTo.Text, new CultureInfo("en-GB")));
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                                string[] x = new string[dt.Rows.Count];
                                decimal[] y = new decimal[dt.Rows.Count];
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {
                                    x[i] = dt.Rows[i][0].ToString();
                                    y[i] = Convert.ToInt32(dt.Rows[i][1]);
                                }
                                LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries { Name = "Suppliers", Data = y,LineColor= "#3dc0f4" });
                                LineChart1.CategoriesAxis = string.Join(",", x);
                                LineChart1.Visible = true;
                            }
                        }
                    }
                }
            }
    

    Output screenshot as below:

    For more information, please refer to the following links:

    https://www.aspsnippets.com/Articles/ASPNet-AJAX-Line-Chart-Control-Multiple-Series-Populate-from-Database-example.aspx

    http://www.aspforums.net/Threads/420994/Filter-GridView-records-using-Start-and-End-date-in-ASPNet/

    Best regards

    Cathy

    Wednesday, March 29, 2017 9:21 AM