uniCalendarPanel save appointment in database

mierlp · January 20, 2019

Hi

Retieving the data form the database (mysql) and show it on the uniCalendarPanel works.

I use a form for entering new appointment of change the existing appointment.

When i save it i got exception ...see attachment

I use this code to save.

Quote

dmClient.Appointment.Append;
dmClient.Appointment.fieldbyname('Subject').Value := UniDBEdit_showNameBirthday.Text;
dmClient.Appointment.fieldbyname('StartDateTime').Value := UniDateTimePicker_Start.DateTime;
dmClient.Appointment.fieldbyname('EndDateTime').Value := UniDateTimePicker_End.DateTime;

dmClient.Appointment.Post;

// Data uit database in calander laden
FormModuleClientAppointment.LoadCalendarData;

LoadCalendarData is a procedure to load the data from table and assing to uniCalendarPanel

Quote

procedure TFormModuleClientAppointment.LoadCalendarData;
var
E : TUniCalendarEvent;
begin
UniCalendarPanel.Events.Clear;

with dmClient.Appointment do begin

While not eof do begin

E:=UniCalendarPanel.Events.Add;
E.CalendarId := 1;
E.Title := dmClient.Appointment.FieldByName('Subject').AsString;
E.StartDate := dmClient.Appointment.FieldByName('StartDateTime').AsDateTime;
E.EndDate := dmClient.Appointment.FieldByName('EndDateTime').AsDateTime;
E.IsAllDay := False;

// Next record
Next;
end;
end;
end;

- Is there a good example for loading and saving into database ?

- what causes this execption ?

Regards Peter

Sherzod · January 21, 2019

Hi,

What DataSet are you using?

Table, Query... ?

What is the structure of your Table?

mierlp · January 21, 2019

Hi

I use DevArt components and for this a TQuery.

The sql statement is only: Select * from Appointment

The structure is simple, see attachment.

After the exception i check the database and the records is saved.

But something cause the issue. There ar NO TQuery.Events defined

mierlp · January 23, 2019

Any suggestions / tips ?

GerhardV · January 23, 2019

Peter does not make sense to me either.

Silly question, when you call LoadCalendarData, shouldn't you do a dmClient.Appointment.First at the beginning before stepping through the dataset? Or when do you open the query?

GerhardV · January 23, 2019

I think this maybe your problem:

https://stackoverflow.com/questions/10762380/mysql-error-in-your-sql-syntax-near-key

You have a column named Key and Key is a reserved word and should be specified with the identifier quote which is a backtick ( ` ) in MySQL. Try do a select without the * but actually specify the column names and then quote Key like this:

select AppointmentID,..., `Key`,... from Appointment.

Edit: By the way I used the following in a Google search: "mysqlexception #42000 near 'Key as _8"

January 23, 2019

//---------------------------------------------------------------------------
void __fastcall TUniFormEventAdd::UniBitBtn1Click(TObject *Sender)
{
//添加
TUniCalendarEvent *e;

if(UniEdit1->Text.IsEmpty())
   {
   ShowMessageN("日程主题不能为空！");
   UniEdit1->SetFocus();
   return;
   }
if(UniMemo1->Text.Length()>500)
   {
   ShowMessageN("日程描述限制500个字！");
   UniMemo1->SetFocus();
   return;
   }
if(!UniCheckBox1->Checked && UniDateTimePicker1->DateTime>=UniDateTimePicker2->DateTime)
   {
   ShowMessageN("开始时间不能大于等于结束时间！");
   return;
   }

e=MainForm()->UniCalendarPanel1->Events->Add();

e->CalendarId=UniRadioGroup2->ItemIndex+1;//色调
e->Description=UniRadioGroup1->Items->Strings[UniRadioGroup1->ItemIndex];
e->Title="["+e->Description+"]"+UniEdit1->Text;
e->Notes=UniMemo1->Text;
e->Url=FormatDateTime("yyyyMMdd hh:nn:ss",Now());
e->Reminder="";
e->StartDate=UniDateTimePicker1->DateTime;
e->EndDate=UniDateTimePicker2->DateTime;
if(UniCheckBox1->Checked)
e->IsAllDay=true;
else
e->IsAllDay=false;

//记录数据库
UniQuery1->Close();
UniQuery1->SQL->Clear();
mySql="insert into work_plan values(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12)";
UniQuery1->SQL->Add(mySql);
UniQuery1->ParamByName("p1")->Value=e->EventId;
UniQuery1->ParamByName("p2")->Value=e->CalendarId;
UniQuery1->ParamByName("p3")->Value=e->Title;
UniQuery1->ParamByName("p4")->Value=e->Notes;
UniQuery1->ParamByName("p5")->Value=e->Description;
UniQuery1->ParamByName("p6")->Value=e->Reminder;
UniQuery1->ParamByName("p7")->Value=e->Url;
UniQuery1->ParamByName("p8")->Value=e->StartDate;
UniQuery1->ParamByName("p9")->Value=e->EndDate;
UniQuery1->ParamByName("p10")->Value=e->IsAllDay;
UniQuery1->ParamByName("p11")->Value=UniMainModule()->loginUserNo;
UniQuery1->ParamByName("p12")->Value=UniMainModule()->loginUserName;
UniQuery1->ExecSQL();
if(UniQuery1->RowsAffected>0)
   {
   this->Close();
   //ShowMessage("添加成功！");
   }
else
   {
   ShowMessage("添加失败！");
   }
}
//---------------------------------------------------------------------------

void __fastcall TMainForm::UniCalendarPanel1DayClick(TUniCalendarPanel *Sender, TDateTime ADate,
bool Allday)
{
//添加事件
/*如果使用RangeSelect事件，则不能使用DayClick事件，否则将两次激活事件*/

}
//---------------------------------------------------------------------------

void __fastcall TMainForm::UniCalendarPanel1EventClick(TUniCalendarPanel *Sender,
int AEventId, TUniCalendarEvent *AEvent)
{
//事件编辑
UniMainModule()->clickFlag="n";

UniMainModule()->eCalendarId=AEvent->CalendarId;
UniMainModule()->eEventId=AEventId;
UniMainModule()->eStartDate=AEvent->StartDate;
UniMainModule()->eEndDate=AEvent->EndDate;
UniMainModule()->eIsAllDay=AEvent->IsAllDay;
UniMainModule()->eTitle=AEvent->Title.SubString(5,AEvent->Title.Length()-4);
UniMainModule()->eNotes=AEvent->Notes;
UniMainModule()->eDescription=AEvent->Description;
UniMainModule()->eReminder=AEvent->Reminder;
UniMainModule()->eUrl=AEvent->Url;

TUniFormEventEdit *formEventEdit=new TUniFormEventEdit(UniApplication);
formEventEdit->ShowModal();

if(UniMainModule()->clickFlag=="e")
   {
   //更新数据库
   UniQuery5->Close();
   UniQuery5->SQL->Clear();
   mySql="update work_plan set e_id=:p1,c_id=:p2,e_title=:p3,e_notes=:p4,e_desc=:p5,e_reminder=:p6,";
   mySql+="e_startdate=:p8,e_enddate=:p9,e_allday=:p10 where e_url=:w1 and user_no=:w2";
   UniQuery5->SQL->Add(mySql);
   UniQuery5->ParamByName("p1")->Value=UniMainModule()->eEventId;
   UniQuery5->ParamByName("p2")->Value=UniMainModule()->eCalendarId;
   UniQuery5->ParamByName("p3")->Value=UniMainModule()->eTitle;
   UniQuery5->ParamByName("p4")->Value=UniMainModule()->eNotes;
   UniQuery5->ParamByName("p5")->Value=UniMainModule()->eDescription;
   UniQuery5->ParamByName("p6")->Value=UniMainModule()->eReminder;
   //UniQuery5->ParamByName("p7")->Value=UniMainModule()->eUrl;
   UniQuery5->ParamByName("p8")->Value=UniMainModule()->eStartDate;
   UniQuery5->ParamByName("p9")->Value=UniMainModule()->eEndDate;
   UniQuery5->ParamByName("p10")->Value=UniMainModule()->eIsAllDay;
   //UniQuery5->ParamByName("p11")->Value=UniMainModule()->loginUserNo;
   //UniQuery5->ParamByName("p12")->Value=UniMainModule()->loginUserName;
   UniQuery5->ParamByName("w1")->Value=UniMainModule()->eUrl;
   UniQuery5->ParamByName("w2")->Value=UniMainModule()->loginUserNo;
   UniQuery5->ExecSQL();
   if(UniQuery5->RowsAffected>0)
       {
       AEvent->EventId=UniMainModule()->eEventId;
       AEvent->CalendarId=UniMainModule()->eCalendarId;
       AEvent->Title=UniMainModule()->eTitle;
       AEvent->Notes=UniMainModule()->eNotes;
       AEvent->Description=UniMainModule()->eDescription;
       AEvent->Reminder=UniMainModule()->eReminder;
       AEvent->StartDate=UniMainModule()->eStartDate;
       AEvent->EndDate=UniMainModule()->eEndDate;
       AEvent->IsAllDay=UniMainModule()->eIsAllDay;
       AEvent->Url=UniMainModule()->eUrl;
       //ShowMessage("修改成功！");
       }
   else
       {
       ShowMessage("修改失败！");
       }
   }
else if(UniMainModule()->clickFlag=="d")
   {
   //删除
   UniQuery5->Close();
   UniQuery5->SQL->Clear();
   mySql="delete from work_plan where e_url=:p1 and user_no=:p2";
   UniQuery5->SQL->Add(mySql);
   UniQuery5->ParamByName("p1")->Value=UniMainModule()->eUrl;
   UniQuery5->ParamByName("p2")->Value=UniMainModule()->loginUserNo;
   UniQuery5->ExecSQL();
   if(UniQuery5->RowsAffected>0)
       {
       AEvent->Free();
       //ShowMessage("删除成功！");
       }
   else
       {
       ShowMessage("删除失败！");
       }
   }
}
//---------------------------------------------------------------------------

void __fastcall TMainForm::UniCalendarPanel1RangeSelect(TUniCalendarPanel *Sender,
TDateTime AStartDate, TDateTime AEndDate)
{
//添加时间段事件
/*如果使用RangeSelect事件，则不能使用DayClick事件，否则将两次激活事件*/
UniMainModule()->eStartDate=AStartDate;
UniMainModule()->eEndDate=AEndDate;

TUniFormEventAdd *formEventAdd=new TUniFormEventAdd(UniApplication);
formEventAdd->ShowModal();
}
//---------------------------------------------------------------------------

January 23, 2019