We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Gantt Chart and mysql database Visual Basic .net

Hello,

I'm trying to display in a chart the production tasks. I tried first with stacked bars, but I have some problems because the start and finish  time of task are overlapping sometimes therefore I'm trying to do with Gantt chart. I'm new with Syncfusion tools, but they are great tools! 

I'm using Visual Basic .net for the application. If you can help me with the application make the code for visual basic. I'm using Visual Studio 2015. NetFramework 4.6.1

I have a mysql database and a table called "production_input".

I'm trying do you something like you have in this link: http://aspnetcore.syncfusion.com/gantt/default

Explanation of the table:

Pi_num = order number
htc = item number and will be the name of the TASK
start_time = task start time
start_date = task start date
actual_finish = task finish time
actual_date = task finish date
completition = efficiency of task in % and we will use as completition % of the task like in the asp example
employee_no = employee number and will be the group sort
line = the production line where the employee is working
friday_day_finish_time = the production finish time on Friday that week
Sunday_day_finish_time = the production finish time on Sunday that week
Weekday_day_finish_time = the production finish time on any other day of that week, besides SATURDAY :)

We need the day finish time because we have task that will last over one day and to display it normally we must know what time they finish production on that day. These finish times are not always the same. Sometimes are working overtime 1 hour or 2 hours depending on the Order and deadline.

On the application when we start if is possible to choose a period of days or single day for example: Start Monday 7/8/17 and finish Sunday 13/8/17 then we have to choose the production line or search the employee_no to display for that period of time all the task existing.

I attached also a sample image with the chart to understand better.

In the picture you will see the orange lines are their breaks:
- Monday to Thursday
     - 10:15 - 10:30
     - 12:45 - 13:30
     - 17:30 - 18:00
- Friday 
     - 10:15 - 10:30
     - 12:30 - 13:00
- Sunday
     - 10:15 - 10:30
     - 12:30 - 13:00
     

Any help/suggestion in VB will be great for me !

Thank you,
Andy









Attachment: chart_project_bb403fb1.rar

16 Replies

AA Albu Andi Gabriel August 16, 2017 11:14 AM UTC

Hi,

I'm still researching for this and I found that this example much better for my application:

http://asp.syncfusion.com/demos/web/gantt/ganttworkingtimerange.aspx

But I still have a long way to go.

Thanks,

Andy



DD Dharanidharan Dharmasivam Syncfusion Team August 16, 2017 02:23 PM UTC

Hi Albu, 

Thanks for contacting Syncfusion support. 

We have analyzed your query. We would like to let you know that, we can’t able to get all the features which are available in JS platform gantt control  with the windows forms gantt series in chart control. We have prepared a basic sample to depict the behavior of gantt series in windows forms chart control. For the gantt data source, you need to pass three arguments viz x, start and end values. And for the grid view, we have used the grid grouping control. Find the screenshot below.  
 
 

 Sample for reference can be find from below link. 

Dharani. 




AA Albu Andi Gabriel August 17, 2017 12:16 AM UTC

Hi Dharani,

Thank you for this! If we cannot have all the functions in Windows Forms, lets make it in JS. Like the example:  http://asp.syncfusion.com/demos/web/gantt/ganttworkingtimerange.aspx

We can add Date range picker for start and end schedule dates for the chart and to load from mysql database into the datagrid with all task for the chosen period. Also a filter dropdown menu for Lines and textbox search for Name/Employee Id number.

Thank you for your help!

Andy




DD Dharanidharan Dharmasivam Syncfusion Team August 17, 2017 01:27 PM UTC

Hi Albu 
 
Please find the response for Gantt related queries.  
Query 1: We can add Date range picker for start and end schedule dates for the chart and to load from mysql database into the datagrid with all task for the chosen period.   
Answer: In Gantt, we can define the project start and end date by using “ScheduleStartDate” and “ScheduleEndDate” properties.  
We have prepared a sample as per your requirement, here we have used two date pickers to select the date range. Using these date pickers value we have retrieved the task details from SQL data base and update to the Gantt control, please refer following code snippet.  
[ASPX]  
<body>  
    <form id="form1" runat="server">  
         <ej:Gantt ID="Gantt" runat="server" ... >  
                 //...                
         </ej:Gantt>  
         StartDate: <ej:DatePicker runat="server" ID="datepick"></ej:DatePicker>  
         EndDate: <ej:DatePicker runat="server" ID="EndDatePicker"></ej:DatePicker>  
         <button id="butt">Update</button>  
     <script type="text/javascript">  
            $("#butt").on("click", function (e) {  
                e.preventDefault();  
                var startObj = $("#datepick").ejDatePicker("instance"), tempArgs = {},  
                    endObj = $("#EndDatePicker").ejDatePicker("instance");  
                tempArgs.startDate = startObj.model.value;  
                tempArgs.endDate = endObj.model.value;  
                PageMethods.ChangeScheduleDate(tempArgs,  
                    function (result) {  
                        var ganttObj = $("#Gantt").ejGantt("instance"),  
                            tempStartObj = $("#datepick").ejDatePicker("instance"),  
                            tempEndObj = $("#EndDatePicker").ejDatePicker("instance");  
                        ganttObj.model.scheduleStartDate = tempStartObj.model.value;  
                        ganttObj.model.scheduleEndDate = tempEndObj.model.value;  
                        ganttObj.option("dataSource", result);  
                    });  
            });  
     </script>  
   </form>  
</body>  
  
[ASPX.CS]  
namespace GanttTestSample  
{  
//..  
public class GetDateRange  
    {  
        public DateTime startDate { get; set; }  
        public DateTime endDate { get; set; }  
    }  
//..  
[WebMethod]  
       public static object ChangeScheduleDate(GetDateRange data)  
       {  
             
        //..  
           using (con)  
           {  
               using (var command = con.CreateCommand())  
               {  
                   DateTime start = data.startDate;  
                   DateTime end = data.endDate;  
                   command.CommandText = "SELECT * FROM GanttData WHERE StartDate BETWEEN CONVERT(datetime,'" + start + "') AND CONVERT(datetime,'" + end + "')";  
  
                   using (var reader = command.ExecuteReader())  
                   {  
                       var indexOfCol1 = reader.GetOrdinal("TaskId");  
                       var indexOfCol2 = reader.GetOrdinal("TaskName");  
                       //..  
                       while (reader.Read())  
                       {  
                           GanttSample res = new GanttSample();  
                           TaskData obj = new TaskData();  
                           obj.TaskId = Convert.ToInt32(reader.GetValue(indexOfCol1));  
                           obj.TaskName = reader.GetValue(indexOfCol2).ToString();  
                           //..  
                           list.Add(obj);  
                       }  
                       reader.Close();  
                   }  
               }  
               con.Close();  
           }  
           return list;  
       }  
}  
  
Query 2:  Filter dropdown menu for Lines.  
Answer:  We can’t get your requirement, please share some more details about this query.  
  
Query 3: Textbox search for Name/Employee Id number 
Answer: In Gantt we can perform search action by using the search textbox in Gantt toolbar, it can be enabled by adding ‘search’ toolbar item in ToolbarSetting.ToolbarItems property.  
Please refer following code snippet.  
[ASPX]  
<body>  
    <form id="form1" runat="server">  
         <ej:Gantt ID="Gantt" runat="server" ... >  
                 //...       
<ToolbarSettings ShowToolbar="true" ToolbarItems="add,edit, … ,search" />           
         </ej:Gantt>  
   </form>  
</body>  
  
  
We have prepared a sample with all above mentioned requirement, please find the sample location below.  
  
Please refer below links to know more about column customization in Gantt.  
  
Please let us know if require further assistance on this.  

Dharani. 




AA Albu Andi Gabriel August 18, 2017 01:19 AM UTC

Hi,


This forum is awesome! Thank for your help!

I'm new to ASP.net, but I think you didn't attached the solution file for the sample, if you can please attach also the solution because I have some miss matching dll files if I import only the gantt files.

Can you please help me also getting the connection with mysql database ?

Many thanks!

Andy



SR Suriyaprasanth Ravikumar Syncfusion Team August 18, 2017 12:11 PM UTC

Hi Albu, 
Please find the response below. 
As per your requirement, we have prepared Gantt sample with MySQL database. 
We have created sample as web application project with webforms. We have rendered Gantt with MySQL database data. 
And we have added option to select the date range from two date pickers and updated the Gantt “scheduleStartDate”,”scheduleEndDate” and “dataSource” with selected date range. 
Please refer following code snippet. 
[ASPX] 
<head> 
    <title>Gantt</title> 
    <meta charset="utf-8" /> 
    //...  
    <script src="Scripts/ej.web.all.min.js"></script> 
    <link rel='nofollow' href="Themes/ej.widgets.core.min.css" rel="stylesheet" /> 
    <link rel='nofollow' href="Themes/default-theme/ej.theme.min.css" rel="stylesheet" /> 
</head> 
<body> 
    <form id="form1" runat="server"> 
        //... 
        <div id="Gantt" style="height: 300px; width: 100%;"></div> 
        <input id="datepick" type="text" /> 
        <input id="EndDatePicker" type="text" /> 
        <button id="butt">Change</button> 
    </form> 
    <script type="text/javascript"> 
        $(function () { 
            var dataManager = ej.DataManager({ 
                url: "/Gantt.aspx/GetGanttData", 
                adaptor: "UrlAdaptor" 
            }); 
 
            $("#Gantt").ejGantt({ 
                dataSource: dataManager, 
                //... 
            }); 
            $("#datepick").ejDatePicker({}); 
            $("#EndDatePicker").ejDatePicker({}); 
        }); 
    </script> 
    <script type="text/javascript"> 
 
        $("#butt").on("click", function (e) { 
            e.preventDefault(); 
            var startObj = $("#datepick").ejDatePicker("instance"), tempArgs = {}, 
                endObj = $("#EndDatePicker").ejDatePicker("instance"); 
            tempArgs.startDate = startObj.model.value; 
            tempArgs.endDate = endObj.model.value; 
            PageMethods.ChangeScheduleDate(tempArgs, 
                function (result) { 
                    var ganttObj = $("#Gantt").ejGantt("instance"), 
                        tempStartObj = $("#datepick").ejDatePicker("instance"), 
                        tempEndObj = $("#EndDatePicker").ejDatePicker("instance"); 
                    ganttObj.model.scheduleStartDate = tempStartObj.model.value; 
                    ganttObj.model.scheduleEndDate = tempEndObj.model.value; 
                    ganttObj.option("dataSource", result); 
                }); 
        }); 
    </script> 
</body> 
 
[ASPX.CS] 
public class TaskData 
        { 
            public string Id { get; set; } 
            public string Name { get; set; } 
            //... 
        } 
 
        public class GetDateRange 
        { 
            public DateTime startDate { get; set; } 
            public DateTime endDate { get; set; } 
        } 
 
        [WebMethod] 
        public static object GetGanttData() 
        { 
              
            string con_string = "Server=localhost;Database=tgrid;Uid=root;Pwd=Treegrid"; // Change your MySQL username and password. 
            //... 
            command.CommandText = "SELECT * FROM gantt"; 
            con.Open(); 
            MySqlDataReader dr = command.ExecuteReader(); //execute select query 
            var indexOfCol3 = dr.GetOrdinal("StartDate"); 
            while (dr.Read()) 
            { 
                TaskData obj = new TaskData(); 
                obj.Id = dr["taskID"].ToString(); 
 
                //... 
 
                list.Add(obj); 
            } 
            con.Close(); 
            return list; 
        } 
 
        [WebMethod] 
        public static object ChangeScheduleDate(GetDateRange data) 
        { 
 
            String start = data.startDate.ToString("yyyy/MM/dd"); 
            String end = data.endDate.ToString("yyyy/MM/dd"); 
            //... 
            string con_string = "Server=localhost;Database=tgrid;Uid=root;Pwd=Treegrid"; 
            //... 
            command.CommandText = "SELECT * FROM `gantt` WHERE startDate >= '" + start + "' AND startDate <= '" + end + "'"; 
            con.Open(); 
            MySqlDataReader dr = command.ExecuteReader(); //execute select query 
            while (dr.Read()) 
            { 
                TaskData obj = new TaskData(); 
                obj.Id = dr["taskID"].ToString(); 
                //... 
                list.Add(obj); 
            } 
            con.Close(); 
            return list; 
        } 
 
Please find the sample from below location:  
And Gantt control is available in various web platforms JS, ASP.NET Web forms, AS.NET MVC, PHP, JSP. 
If you need sample with any other framework please let us know. 
Please refer the below link to know more about Gantt controls features. 
 
Thanks, 
Suriyaprasanth R. 



AA Albu Andi Gabriel August 22, 2017 12:32 AM UTC

Hi,

This is great! I already made it run with my database.

How can I show in Gantt these columns from the database?

- employee_no

- employee_name

- employee_Fname 

and to be sort it on load by Start Date and time.

Can I have the hierarchy in Gantt based on employee_no not task id ? 

If we add the columns can we search for the name of employee not the task name?


Thanks in advance!

Andy



SR Suriyaprasanth Ravikumar Syncfusion Team August 22, 2017 08:08 AM UTC

Hi Albu, 
Please find the response below. 
Query 1:  How can I show in Gantt these columns from the database? 
Answer:  In Gantt, we can add custom columns at the load time by using client side event called “load”. 
We have prepared a sample and added custom columns “Employee_Name” and “Employee_FName” in Gantt using load event. 
Please refer the following code snippet. 
[ASPX] 
 
$("#Gantt").ejGantt({ 
                dataSource: dataManager, 
                //.. 
                load: 'load' 
            }); 
 
<script type="text/javascript"> 
 
        function load(args) { 
            var columns = this.getColumns(); 
 
            //.. 
 
            //To bind the custom field 
            columns.splice(2, 0, 
                { 
                    field: "Employee_Name", 
                    headerText: "Employee_Name", 
                    editType: "stringEdit", 
                    mappingName: "Employee_Name", 
                    width: "180px" 
                }); 
            columns.splice(3, 0, 
                { 
                    field: "Employee_Fname", 
                    headerText: "Employee_Fname", 
                    editType: "stringEdit", 
                    mappingName: "Employee_Fname", 
                    width: "180px" 
                }); 
        } 
   </script> 
 
Query 2:  To be sort it on load by Start Date and time. 
Answer:  We have achieved your requirement by using “ORDER BY” SQL query. We have modified the Gantt sample and rendered the records based on “StartDate” field. 
Please refer following code snippet,  
[ASPX.CS] 
 
[WebMethod] 
        public static object GetGanttData() 
        { 
 
            //.. 
 
            command = con.CreateCommand(); 
            command.CommandText = "SELECT * FROM gantt ORDER BY startDate Asc"; 
            con.Open(); 
            MySqlDataReader dr = command.ExecuteReader(); //execute select query 
            while (dr.Read()) 
            { 
                TaskData obj = new TaskData(); 
                obj.Employee_ID = dr["Employee_ID"].ToString(); 
 
                //.. 
 
                list.Add(obj); 
            } 
            con.Close(); 
            return list; 
        } 
 
Query 3:  Can I have the hierarchy in Gantt based on employee_no not task id ? 
Answer:  In Gantt for self-reference data source hierarchy order was populated by using “taskIdMapping” and “parentTaskIdMapping” property values. Please refer below links for more information about this. 
                          https://help.syncfusion.com/api/js/ejgantt#members:parenttaskidmapping  
We have modified the Gantt sample and mapped “Employee_ID” field to the “taskIdMapping” and “TeamID” field to “parentTaskIdMapping” properties. 
And also we have renamed the headerText of taskId column to Employee_ID. 
Please refer following code snippet,  
[ASPX] 
 
$("#Gantt").ejGantt({ 
                dataSource: dataManager, 
                taskIdMapping: "Employee_ID", 
                parentTaskIdMapping: "TeamId", 
           //.. 
                load: 'load' 
 
}); 
<script type="text/javascript"> 
 
        function load(args) { 
            var columns = this.getColumns(); 
 
            //To Rename the Header text of Task Id column to Employee_id. 
            columns[0].width = '180px'; 
            columns[0].headerText = 'Employee_ID'; 
} 
 
Query 4:  If we add the columns can we search for the name of employee not the task name? 
Answer:  In Gantt , search action will be performed with all available fields in Gantt column collection. We can perform search action by provide the search text value in search text box followed by enter key action.  
We can add search text box in Gantt toolbar by using “toolbarSetting.toolbarItems” property. 
Please refer the below links for more information about search action in Gantt. 
Please refer following code snippet. 
[ASPX] 
   $("#Gantt").ejGantt({ 
                dataSource: dataManager, 
                //.. 
 
                toolbarSettings: { 
                    showToolbar: true, 
                    toolbarItems: [ej.Gantt.ToolbarItems.Search] 
                }, 
            }); 
 
We have prepared a sample with all above mentioned requirement, please find the sample location below. 
Thanks, 
Suriyaprasanth R. 



AA Albu Andi Gabriel August 23, 2017 09:17 AM UTC

Hi,

Now the application is working. But before future work, I wanted to deploy the application and to use it in windows 10 IIS. I'm very confused about SQL requirement of database and my application on mysql.

Also I have the error :


The error is only when I try to publish it. 

Can you please help me with step by step tutorial how can I publish it ?

Thank you,

Andy



AA Albu Andi Gabriel August 23, 2017 10:02 AM UTC

I finally succeeded to publish as package and import in the local IIS.

This is the page from Visual Studio: