Populate Google TimeLine chart from Database with 24 hours time format in ASP.Net

Question

muhammad...

SOLVED CLOSED ACTIVE ANSWERED
User: muhammad12
Posted: on Jun 09, 2024 11:14 PM
Forum: ASP.Net Web Forms
Answer: 1
Views: 702
Sample Code: Download

Hi dharmendr sir,

I have a code of google timeline its working perfect for 12 hours for example if starttime is 3:00 and end time is 3;30 its making graph but of start time is 15;00 and end time is like 16:00 then its giving error.

please help me sir.

my database column datatype of both start and end time is varchar(50)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Mergegraph.aspx.cs" Inherits="GANTTCHART.Mergegraph" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <!-- Include Google Font link -->
    <link href="https://fonts.googleapis.com/css2?family=Sofia+Sans+Condensed:wght@400;700&display=swap" rel="stylesheet" />
    <style>
        /* Style to center the input form */
        #registration-form-container {
            display: flex;
            justify-content: center;
            align-items: flex-start; /* Align form to the top */
            overflow: hidden;
            margin-top: 0px; /* Adjust top margin as needed */
        }

        #registration-form {
            display: inline-block; /* Ensure it only takes the necessary width */
            padding: 20px;
            border: 1px solid #ccc;
            background-color: #f2f2f2;
            border-radius: 8px;
            overflow: hidden;
            font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
        }

        /* Style to make the "Clockify report!" title more prominent */
        legend {
            font-weight: bold;
            font-size: 20px;
            margin-bottom: 20px;
            font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
        }

        /* Style to place input elements in one row */
        .input-group {
            display: inline-block;
            margin-right: 10px; /* Adjust as needed */
        }

        /* Style to adjust the width of input elements */
        .input {
            width: 150px; /* Adjust as needed */
            padding: 5px;
            border: 1px solid #ccc;
            border-radius: 4px;
            box-sizing: border-box;
            font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
        }

        /* Style for labels */
        .label {
            display: block;
            margin-bottom: 5px;
            color: black;
            font-size: 14px;
            font-weight: bold;
            font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
        }

        /* Style for buttons */
        .btn {
            background-color: #4CAF50;
            color: white;
            padding: 10px 20px;
            border: none;
            font-weight: bold;
            border-radius: 4px;
            cursor: pointer;
            font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
        }

            /* Hover effect for buttons */
            .btn:hover {
                background-color: #45a049;
            }

        /* Style to make sure the timeline adjusts according to device width */
        #departmentChartsContainer {
            white-space: nowrap;
            margin-top: 20px;
            font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
            overflow-x: hidden; /* Hide horizontal scrollbar */

            overflow-y: hidden;
            width: 100%; /* Fix the width of the container */
            display: block;
        }

        .sentence {
            display: flex;
            align-items: center;
            margin-top: 10px;
        }

            .sentence .label {
                margin-right: 5px;
            }

        #departmentChartsContainer {
            white-space: nowrap;
            margin: 20px auto; /* Center the container horizontally */
            font-family: 'Sofia Sans Condensed', sans-serif;
            overflow-x: hidden;
            overflow-y: hidden;
            width: 80%; /* Set the width to 80% */
            display: block;
        }
    </style>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script>
     google.charts.load("current", { packages: ["timeline"] });
        function drawChart(data, chartDivId) {
            var container = document.getElementById(chartDivId);
            var chart = new google.visualization.Timeline(container);

            var dataTable = new google.visualization.DataTable();
            dataTable.addColumn({ type: 'string', id: 'Role' });
            dataTable.addColumn({ type: 'string', id: 'Name' });
            dataTable.addColumn({ type: 'date', id: 'Start' });
            dataTable.addColumn({ type: 'date', id: 'End' });

            for (var departmentName in data) {
                if (data.hasOwnProperty(departmentName)) {
                    data[departmentName].forEach(function (event) {
                        var start = new Date('2000/01/01 ' + event[2]); // Assuming the date component is irrelevant
                        var end = new Date('2000/01/01 ' + event[3]); // Assuming the date component is irrelevant
                        dataTable.addRows([[departmentName, event[1], start, end]]);
                    });
                }
            }

            var options = {
                timeline: {
                    groupByRowLabel: false,
                    colorByRowLabel: true, // Display each event on a new line
                    rowLabelStyle: { fontName: 'Sofia Sans Condensed', fontSize: 14, color: '#000000', bold: true },
                    barLabelStyle: { fontSize: 14, bold: true, fontName: 'Sofia Sans Condensed', color: '#000000 ' },
                    avoidOverlappingGridLines: true
                },
                alternatingRowStyle: false,
                backgroundColor: '#ffd',
                height: 2000
            };

            chart.draw(dataTable, options);
        }

        $(function () {
            $("#btnView").click(function () {
                var flightNo = $("#flightNo").val();
                var flightDate = $("#flightDate").val();
                var flightType = $("#flightType").val();
                $.ajax({
                    type: "POST",
                    url: "Mergegraph.aspx/GetChartData",
                    data: JSON.stringify({ flightNo: flightNo, flightDate: flightDate, flightType: flightType }),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        drawChart(response.d, "departmentChartsContainer");
                    },
                    error: function (response) {
                        alert(response.responseText);
                    }
                });
                return false;
            });
        });
    </script >
</head >
    <body>
        <form id="form1" runat="server">
        <div id="registration-form-container">
            <div id="registration-form">
                <legend>Clockify report!</legend>
                <div class="row">
                    <div class="input-group">
                        <asp:Label ID="Label1" runat="server" Text="Flight No" CssClass="label"></asp:Label>
                        <asp:TextBox ID="flightNo" runat="server" CssClass="input"></asp:TextBox>
                    </div>
                    <div class="input-group">
                        <asp:Label ID="Label2" runat="server" Text="Flight Date " CssClass="label"></asp:Label>
                        <asp:TextBox ID="flightDate" runat="server" CssClass="input" type="date"></asp:TextBox>
                    </div>
                    <div class="input-group">
                        <asp:Label ID="Label3" runat="server" Text="Flight Type" CssClass="label"></asp:Label>
                        <asp:DropDownList ID="flightType" runat="server" CssClass="input" Style="color: Black; font-size: 20px;">
                            <asp:ListItem Text="Arrival" />
                            <asp:ListItem Text="Departure" />
                        </asp:DropDownList>
                    </div>

                    <!-- Add other input elements here -->
                    <div class="input-group">
                        <asp:Button ID="btnView" runat="server" Text="Submit" class="btn" />
                    </div>
                </div>
                <div class="row sentence" style="display: none;">
                    <asp:Label ID="Label4" runat="server" Text="This Flight Started From: " CssClass="label" Style="color: red;"></asp:Label>
                    <asp:Label ID="StartTime" runat="server" Text="10:00" CssClass="label" Style="font-weight: bold;"></asp:Label>
                    <asp:Label ID="Label5" runat="server" Text=" And Ended at: " CssClass="label" Style="color: red;"></asp:Label>
                    <asp:Label ID="EndTime" runat="server" Text="12:00" CssClass="label" Style="font-weight: bold;"></asp:Label>
                </div>
            </div>
        </div>
        <!-- Container for department Gantt charts -->
        <div id="departmentChartsContainer"></div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;

namespace GANTTCHART
{
    public partial class Mergegraph : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        [WebMethod]
        public static Dictionary<string, List<object[]>> GetChartData(string flightNo, string flightDate, string flightType)
        {
            string conString = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            string query = "SELECT Department.DeptName, ActionID, eventname, starttime, CASE WHEN ISNULL(endtime, '') = '' THEN CONVERT(varchar(5), DATEADD(MINUTE, 1, CONVERT(time, starttime))) ELSE endtime END AS endtime FROM events INNER JOIN Assign ON Assign.eventid = events.eventid INNER JOIN Department ON Department.DeptID = Assign.DeptID INNER JOIN Action ON Assign.AssignID = Action.AssignID INNER JOIN flights ON flights.flightid = Action.flightid WHERE flights.flightno = @flightNo AND CONVERT(date, flights.flightdate) = @flightDate AND events.eventtype = @flightType";

            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@flightNo", flightNo);
                    string flightDateStr = flightDate;
                    DateTime selectedDate = DateTime.Parse(flightDateStr);
                    cmd.Parameters.AddWithValue("@flightDate", selectedDate);
                    cmd.Parameters.AddWithValue("@flightType", flightType);

                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    Dictionary<string, List<object[]>> departmentChartData = new Dictionary<string, List<object[]>>();

                    while (reader.Read())
                    {
                        string departmentName = reader["DeptName"].ToString();
                        if (!departmentChartData.ContainsKey(departmentName))
                        {
                            departmentChartData[departmentName] = new List<object[]>();
                        }

                        departmentChartData[departmentName].Add(new object[]
                        {
                     reader["eventname"],
                     reader["eventname"] ,
                      reader["starttime"].ToString(),
                       reader["endtime"].ToString()
                        });
                    }
                    return departmentChartData;
                }
            }
        }
    }
}

Error:

"Message":"Conversion failed when converting date and/or time from character string.","StackTrace":" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean\u0026 dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean\u0026 moreRows)\r\n at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean\u0026 more)\r\n at System.Data.SqlClient.SqlDataReader.Read()\r\n at GANTTCHART.Mergegraph.GetChartData(String flightNo, String flightDate, String flightType) in C:\\Users\\DELL\\source\\repos\\GANTTCHART\\GANTTCHART\\Mergegraph.aspx.cs:line 42","ExceptionType":"System.Data.SqlClient.SqlException"}

Populate Google TimeLine chart from Database with 24 hours time format in ASP.Net

Company

Explore

Follow us

Contact Us