search envelope-o feed check
Home Unanswered Active Tags New Question
user comment-o

Events are not binding to Scheduler grid

Asked by Kathy
9 years ago.

Hi Dan,
Instead of using "resources" as you didon the default.aspx.I need to replace it with Technicians which is dynamically populated from a SQL view. I am able to bring that in but what happens is the events are not linked. I am working from your Large.aspx so that my users can see two different views, one based on site location and the other based on technicians.
Thanks in advance to your quick reply.
Kathy

Below is the code behind that I used from the Large.aspx.cs:
<code>
public partial class Scheduler_Large : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
// prevent invalid ViewState errors in Firefox
if (Request.Browser.Browser == "Firefox") Response.Cache.SetNoStore();


DayPilotScheduler2.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
//DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler1.StartDate.Year, DayPilotScheduler1.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler1.StartDate.AddMonths(1).Year, DayPilotScheduler1.StartDate.AddMonths(1).Month);
DayPilotScheduler2.Days = 360;
DayPilotScheduler2.Separators.Add(DateTime.Now, Color.Red);

if (!IsPostBack)
{
loadResources();
// scroll to this month
DateTime firstOfMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DayPilotScheduler2.SetScrollX(firstOfMonth);

DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
}
}
private void loadResources()
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["_____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT DISTINCT [id], [name] FROM [TechPlanner].[dbo].[PSIVSEmployee] Where [JobTitle] IN ('CEH901', 'CEH902', 'CEH903', 'CEH904', 'CEH905', 'CEH906', 'CHH903', 'CHH904', 'CHH905', 'CMH901', 'CMH903', 'CS1007', 'CS404', 'CS405', 'IV735', 'IV913', 'IV914', 'IV922', 'IV923', 'IV925', 'IV926', 'IV927', 'IVH10', 'IVH20', 'IVH30', 'IVH40', 'IVH50')";
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
string name = (string)r["name"];
decimal id = Convert.ToDecimal(r["id"]);
string ID = Convert.ToString(id);

DayPilotScheduler2.Resources.Add(name, ID);
}
conn.Close();
}

private DataTable dbGetEvents(DateTime start, int days)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT T.id, T.[name], AT.[EventID], AT.eventstart, AT.eventend, E.name FROM PSIVSEmployee T LEFT JOIN AssignedTechs AT ON AT.employeeid = T.id LEFT JOIN EVENT E ON AT.[EventID] = E.[ID] Where T.[JobTitle] IN ('CEH901', 'CEH902', 'CEH903', 'CEH904', 'CEH905', 'CEH906', 'CHH903', 'CHH904', 'CHH905', 'CMH901', 'CMH903', 'CS1007', 'CS404', 'CS405', 'IV735', 'IV913', 'IV914', 'IV922', 'IV923', 'IV925', 'IV926', 'IV927', 'IVH10', 'IVH20', 'IVH30', 'IVH40', 'IVH50') AND AT.[EventID] IS NOT NULL AND NOT ((AT.eventend <= @start) OR (AT.eventstart >= @end))";
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", start.AddDays(days));
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
private void dbUpdateEvent(string id, DateTime start, DateTime end, string resource)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [event] SET [eventstart] = @start, [eventend] = @end, [resource_id] = @resource WHERE [id] = @id";
cmd.Parameters.AddWithValue("id", id);
cmd.Parameters.AddWithValue("start", start);
cmd.Parameters.AddWithValue("end", end);
cmd.Parameters.AddWithValue("resource", resource);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void DayPilotScheduler2_EventMove(object sender, DayPilot.Web.Ui.Events.EventMoveEventArgs e)
{
string id = e.Value;
DateTime start = e.NewStart;
DateTime end = e.NewEnd;
string resource = e.NewResource;
dbUpdateEvent(id, start, end, resource);
DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
DayPilotScheduler2.Update();

}
protected void DayPilotScheduler2_EventResize(object sender, DayPilot.Web.Ui.Events.EventResizeEventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["___"].ConnectionString);
cmd.Connection = sqlConn;
string id = e.Value;
StringBuilder sb = new StringBuilder();

try
{
sqlConn.Open();

//Update Event Record
sb.Append("Update event ");
sb.Append("Set ");
sb.Append("eventstart = '" + e.NewStart + "', ");
sb.Append("eventend = '" + e.NewEnd + "' ");
sb.Append("Where id = '" + e.Value + "'");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();

sb.Length = 0; //reset StringBuilder
}

catch
{
throw;
}

finally
{
if (sqlConn != null)
{
sqlConn.Close();
}
}
DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
DayPilotScheduler2.Update();
}
protected void DayPilotScheduler2_TimeRangeSelected(object sender, DayPilot.Web.Ui.Events.TimeRangeSelectedEventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["_____"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO [event]([eventstart], [eventend]) VALUES (@start, @end)";

cmd.Parameters.AddWithValue("@start", e.Start);
cmd.Parameters.AddWithValue("@end", e.End);

try
{
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}

}
protected void DayPilotScheduler2_BeforeEventRender(object sender, DayPilot.Web.Ui.Events.BeforeEventRenderEventArgs e)
{
if (e.Value == "11")
{
e.ContextMenuClientName = "cmSpecial";
e.EventMoveEnabled = false;
e.EventResizeEnabled = false;
e.EventClickEnabled = false;
e.DurationBarColor = "red";
//e.BackgroundColor = "lightyellow";
}
e.BackgroundImage = "../Media/gradient_dps.jpg";
e.BackgroundRepeat = "repeat-x";
}

protected void DayPilotScheduler2_EventMenuClick(object sender, DayPilot.Web.Ui.Events.EventMenuClickEventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["_____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT [id], [name] FROM [resource]";
switch (e.Command)
{
case "Delete":

conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();

adapter.Fill(dt);

DataColumn[] keys = new DataColumn[1];
keys[0] = dt.Columns["id"];
dt.PrimaryKey = keys;

DataRow r = dt.Rows.Find(e.Value);
if (r != null)
{
dt.Rows.Remove(r);
dt.AcceptChanges();
}
DayPilotScheduler2.Update();
conn.Close();
break;
}

DayPilotScheduler2.Update();
}
protected void DayPilotScheduler2_BeforeResHeaderRender(object sender, DayPilot.Web.Ui.Events.BeforeHeaderRenderEventArgs e)
{
if (e.IsCorner)
{
if (DayPilotScheduler2.IsExport)
{
e.InnerHTML = DayPilotScheduler2.StartDate.Year.ToString();
}
else
{
e.InnerHTML = String.Format("<div style='padding:5px; font-weight: bold; font-size:22px; text-align:center'>{0}</div>", DayPilotScheduler2.StartDate.Year);
}
}
}
protected void DayPilotBubble1_RenderContent(object sender, RenderEventArgs e)
{
if (e is RenderResourceBubbleEventArgs)
{
RenderResourceBubbleEventArgs re = e as RenderResourceBubbleEventArgs;
e.InnerHTML = "<b>Resource header details</b><br />Value: " + re.ResourceId;
}
else if (e is RenderCellBubbleEventArgs)
{
RenderCellBubbleEventArgs re = e as RenderCellBubbleEventArgs;
e.InnerHTML = "<b>Cell details</b><br />Resource:" + re.ResourceId + "<br />From:" + re.Start + "<br />To: " + re.End;
}
}

protected void DayPilotScheduler2_Command(object sender, DayPilot.Web.Ui.Events.CommandEventArgs e)
{
switch (e.Command)
{
case "next":
DayPilotScheduler2.StartDate = DayPilotScheduler2.StartDate.AddMonths(2);
DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler2.StartDate.Year, DayPilotScheduler2.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler2.StartDate.AddMonths(1).Year, DayPilotScheduler2.StartDate.AddMonths(1).Month);
break;
case "previous":
DayPilotScheduler2.StartDate = DayPilotScheduler2.StartDate.AddMonths(-2);
DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler2.StartDate.Year, DayPilotScheduler2.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler2.StartDate.AddMonths(1).Year, DayPilotScheduler2.StartDate.AddMonths(1).Month);
break;
case "this":
DayPilotScheduler2.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler2.StartDate.Year, DayPilotScheduler2.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler2.StartDate.AddMonths(1).Year, DayPilotScheduler2.StartDate.AddMonths(1).Month);
break;
case "refresh":
case "filter":
loadResources();
string filter = (string)e.Data;
DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
DayPilotScheduler2.Update(CallBackUpdateType.Full);
return; // note that the binding done inside the case section here
default:
throw new Exception("Unknown command.");
}

DataBind();
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}

protected void DayPilotScheduler2_EventClick(object sender, DayPilot.Web.Ui.Events.EventClickEventArgs e)
{
DataBind();
DayPilotScheduler2.Update(String.Format("Event {0} clicked.", e.Value));

}
protected void DayPilotScheduler2_TimeRangeMenuClick(object sender, DayPilot.Web.Ui.Events.TimeRangeMenuClickEventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TechPlanner"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO [event] ([id], [eventstart], [eventend], [resource_id], [FullName]) VALUES(@start, @end, @id, @column, @technician)";

if (e.Command == "Insert")
{
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);

DataRow r = dt.NewRow();
r["start"] = e.Start;
r["end"] = e.End;
r["id"] = Guid.NewGuid().ToString();
r["name"] = "New event";
r["column"] = e.ResourceId;
r["technician"] = e.Data;

dt.Rows.Add(r);
dt.AcceptChanges();

DayPilotScheduler2.Update();
conn.Close();
}
}

protected void DayPilotScheduler2_BeforeCellRender(object sender, DayPilot.Web.Ui.Events.BeforeCellRenderEventArgs e)
{
/*
if (e.Start == new DateTime(2009, 6, 1))
{
e.BackgroundColor = "red";
}*/
}
protected void DayPilotScheduler2_ResourceHeaderMenuClick(object sender, DayPilot.Web.Ui.Events.Scheduler.ResourceHeaderMenuClickEventArgs e)
{

switch (e.Command)
{
case "Insert":
e.Resource.Children.Add(" ", Guid.NewGuid().ToString());
e.Resource.Expanded = true;
break;
case "DeleteChildren":
e.Resource.Children.Clear();
break;
case "Delete":
DayPilotScheduler2.Resources.RemoveFromTree(e.Resource);
break;
}
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}
protected void DayPilotScheduler2_ResourceHeaderClick(object sender, DayPilot.Web.Ui.Events.Scheduler.ResourceHeaderClickEventArgs e)
{
DayPilotScheduler2.Resources.RemoveFromTree(e.Resource);
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}
protected void ButtonExport_Click(object sender, EventArgs e)
{
DataBind();

Response.Clear();
Response.ContentType = "image/png";
Response.AddHeader("content-disposition", "attachment;filename=print.png");
MemoryStream img = DayPilotScheduler2.Export(ImageFormat.Png);
img.WriteTo(Response.OutputStream);
Response.End();

}
protected void DayPilotScheduler1_Refresh(object sender, DayPilot.Web.Ui.Events.RefreshEventArgs e)
{
DayPilotScheduler2.StartDate = e.StartDate;
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}
protected void DayPilotBubble1_RenderEventBubble(object sender, RenderEventBubbleEventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<b>{0}</b><br />", e.Text);
sb.AppendFormat("Start: {0}<br />", e.Start);
sb.AppendFormat("End: {0}<br />", e.End);
sb.AppendFormat("<br />");
sb.AppendFormat("<b>Double-click the event to enter inline edit mode.</b>");

e.InnerHTML = sb.ToString();

}
}

</code>

Comment posted by Dan Letecky
9 years ago.

Kathy, I've created a short troubleshooting guide in the KB:

kb.daypilot.org/101644/events-not-showing-up-in-the-scheduler/

Please let me know if it didn't help you with your problem.

Comment posted by Kathy
9 years ago.

Hi Dan,
I understand what you posted. I know what I have to figure out but it would have been nice to be able to do it the way I would need it. Thank you for the knowledge base.

Comment posted by Dan Letecky
9 years ago.

Kathy,

I don't see what's set in DataResourceField (it seems that it should be "id"). Provided that it's set correctly, you should also check what the SQL in getEvents() actually returns:

"SELECT T.id, T.[name], AT.[EventID], AT.eventstart, AT.eventend, E.name FROM PSIVSEmployee T LEFT JOIN AssignedTechs AT ON AT.employeeid = T.id LEFT JOIN EVENT E ON AT.[EventID] = E.[ID] Where T.[JobTitle] IN ('CEH901', 'CEH902', 'CEH903', 'CEH904', 'CEH905', 'CEH906', 'CHH903', 'CHH904', 'CHH905', 'CMH901', 'CMH903', 'CS1007', 'CS404', 'CS405', 'IV735', 'IV913', 'IV914', 'IV922', 'IV923', 'IV925', 'IV926', 'IV927', 'IVH10', 'IVH20', 'IVH30', 'IVH40', 'IVH50') AND AT.[EventID] IS NOT NULL AND NOT ((AT.eventend <= @start) OR (AT.eventstart >= @end))";

If everything seems to be fine, please send the output HTML to support@daypilot.org and I will check it.


Comment posted by Kathy
9 years ago.

Hi Dan,

Code and the results from the view source are on its way via email. Again thank you very much for taking the time in troublshooting my problem.

This question is more than 6 months old and has been closed. Please create a new question if you have anything to add.