Jump to content

uniCalendarPanel save appointment in database


mierlp

Recommended Posts

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

bds_2019-01-20_23-00-12.png

Link to comment
Share on other sites

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

 

 

navicat_2019-01-21_10-12-50.png

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

 

//---------------------------------------------------------------------------
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();
}
//---------------------------------------------------------------------------

Link to comment
Share on other sites

 

//---------------------------------------------------------------------------
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();
}
//---------------------------------------------------------------------------

Link to comment
Share on other sites

Hi Gerhard

Thanks...that did the trick indeed. I did a google search but I think not

good enough..anyway...now i can go on. Yes..this is a test case and i have

couple of things to modify

 

@55143681

Thanks for the examples, i'm going to see what i can use from the example.

 

Thanks guys

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...