Before I sent this post, have you sure I had tried different ways to solve this problem. So, direct to the point, when I try to Insert or Update an Appointment (CustomAppointment), I always receive an syntax error in INSERT INTO or UPDATE statement. The error is raised because DateTime format of Start/End properties from ScheduleView. I'm using a OleDBCommand with Parameters, as you see below.
I already tried pass date/time values surrounded with single quotes, double quotes, sharp (#), and DateTime as you can se in this code. Also I tried OleDbType.DBDate. OleDbType.Date and OleDbType.DBTimeStamp. But always got the same Syntax Error in INSERT INTO.
Could you please help me?
protected override void OnAppointmentCreated(IAppointment appointment)
{
CustomAppointment addedAppointment = appointment as CustomAppointment;
if (addedAppointment != null)
{
OleDbConnection cnInsertAppointment = new OleDbConnection(Properties.Resources.cnDBPhito);
cnInsertAppointment.Open();
OleDbCommand cmdInsertAppointment = new OleDbCommand();
cmdInsertAppointment.Connection = cnInsertAppointment;
cmdInsertAppointment.CommandType = CommandType.Text;
cmdInsertAppointment.CommandText = "insert into Appointments(AppointmentID, Subject, Body, Start, End, IsAllDayEvent, Location, Url, TimeMarker, Importance, Category, RecurrencePattern, IsReturn, MedicalOrder, HealthClubID, PatientID) values(@AppointmentID, @Subject, @Body, @Start, @End, @IsAllDayEvent, @Location, @Url, @TimeMarker, @Importance, @Category, @RecurrencePattern, @IsReturn, @MedicalOrder, @HealthClubID, @PatientID)";
cmdInsertAppointment.Parameters.Add("@AppointmentID", OleDbType.VarChar, 36).Value = addedAppointment.UniqueId;
cmdInsertAppointment.Parameters.Add("@Subject", OleDbType.VarChar, 100).Value = addedAppointment.Subject;
cmdInsertAppointment.Parameters.Add("@Body", OleDbType.VarChar, 400).Value = addedAppointment.Body;
DateTime dtStart = new DateTime(addedAppointment.Start.Year, addedAppointment.Start.Month, addedAppointment.Start.Day, addedAppointment.Start.Hour, addedAppointment.Start.Minute, addedAppointment.Start.Second);
cmdInsertAppointment.Parameters.Add("@Start", OleDbType.DBTimeStamp).Value = dtStart;
DateTime dtEnd = new DateTime(addedAppointment.End.Year, addedAppointment.End.Month, addedAppointment.End.Day, addedAppointment.End.Hour, addedAppointment.End.Minute, addedAppointment.End.Second);
cmdInsertAppointment.Parameters.Add("@End", OleDbType.DBTimeStamp).Value = dtEnd;
cmdInsertAppointment.Parameters.Add("@IsAllDayEvent", OleDbType.Boolean).Value = addedAppointment.IsAllDayEvent;
cmdInsertAppointment.Parameters.Add("@Location", OleDbType.VarChar, 200).Value = addedAppointment.Location;
cmdInsertAppointment.Parameters.Add("@Url", OleDbType.VarChar, 200).Value = addedAppointment.Url;
cmdInsertAppointment.Parameters.Add("@TimerMarker", OleDbType.VarChar, 50).Value = addedAppointment.TimeMarker == null ? String.Empty : addedAppointment.TimeMarker.TimeMarkerName;
cmdInsertAppointment.Parameters.Add("@Importance", OleDbType.VarChar, 50).Value = addedAppointment.Importance == null ? String.Empty : addedAppointment.Importance.ToString();
cmdInsertAppointment.Parameters.Add("@Category", OleDbType.VarChar, 50).Value = addedAppointment.Category == null ? String.Empty : addedAppointment.Category.CategoryName;
if (addedAppointment.RecurrenceRule != null)
{
cmdInsertAppointment.Parameters.Add("@RecurrencePattern", OleDbType.VarChar, 100).Value = RecurrencePatternHelper.RecurrencePatternToString(appointment.RecurrenceRule.Pattern);
}
cmdInsertAppointment.Parameters.Add("@IsReturn", OleDbType.Boolean).Value = addedAppointment.IsReturn;
cmdInsertAppointment.Parameters.Add("@MedicalOrder", OleDbType.Boolean).Value = addedAppointment.MedicalOrder;
cmdInsertAppointment.Parameters.Add("@HealthClubID", OleDbType.Integer).Value = addedAppointment.HealthClubID;
cmdInsertAppointment.Parameters.Add("@PatientID", OleDbType.Integer).Value = addedAppointment.PatientID;
try
{
cmdInsertAppointment.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
cmdInsertAppointment.Dispose();
cnInsertAppointment.Close();
cnInsertAppointment.Dispose();
}
}
I'm using VS2010 + SP1, WPF Application, Target .NET Framework 3.5, Access 2007 Database (mdb) and Telerik WPF Controls Q1 2012.