formcoursemanage.pas

Delphi数据库编程

unit FormCourseManage;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DBCtrls, Mask, Grids, DBGrids, ExtCtrls, DB, DBTables;

type
  TfrmCourseManage = class(TForm)
    dbnCourse: TDBNavigator;
    btnQuit: TButton;
    dbgCourse: TDBGrid;
    gpbCourse: TGroupBox;
    Label1: TLabel;
    dbecourseID: TDBEdit;
    Label2: TLabel;
    dbeCourseName: TDBEdit;
    Label3: TLabel;
    dbeCredit: TDBEdit;
    Label4: TLabel;
    dbePeriod: TDBEdit;
    Label5: TLabel;
    dbeTeacher: TDBEdit;
    Label6: TLabel;
    dbmDesc: TDBMemo;
    dsCourse: TDataSource;
    tblCourse: TTable;
    qryValid: TQuery;
    procedure btnQuitClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure tblCourseAfterScroll(DataSet: TDataSet);
    procedure dbnCourseClick(Sender: TObject; Button: TNavigateBtn);
    procedure tblCourseBeforeDelete(DataSet: TDataSet);
    procedure tblCourseBeforePost(DataSet: TDataSet);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    OldCourseID: string;
    function Selected(var str: string; CourseID: string): Boolean;
    procedure ToggleMode;
  public
    { Public declarations }
  end;

var
  frmCourseManage: TfrmCourseManage;

implementation

{$R *.dfm}

procedure TfrmCourseManage.FormShow(Sender: TObject);
begin
  tblCourse.Active := true;
  ToggleMode;
end;

procedure TfrmCourseManage.btnQuitClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmCourseManage.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  tblCourse.Active := false;
end;

procedure TfrmCourseManage.tblCourseAfterScroll(DataSet: TDataSet);
begin
  ToggleMode;
end;

procedure TfrmCourseManage.dbnCourseClick(Sender: TObject; Button: TNavigateBtn);
begin
  ToggleMode;
  OldCourseID := dbeCourseID.Text;
end;

procedure TfrmCourseManage.ToggleMode;
  procedure EnableInput(Enabled: Boolean);
  begin
    dbgCourse.Enabled := not Enabled;
    dbeCourseID.Enabled := Enabled;
    dbeCourseName.Enabled := Enabled;
    dbeTeacher.Enabled := Enabled;
    dbeCredit.Enabled := Enabled;
    dbePeriod.Enabled := Enabled;
    dbmDesc.Enabled := Enabled;
  end;
begin
  case tblCourse.State of
    dsInsert, dsEdit:
      begin
        EnableInput(true);
        dbnCourse.VisibleButtons := [nbPost, nbCancel];
        dbeCourseID.SetFocus;
      end;
  else
    EnableInput(false);
    dbnCourse.VisibleButtons := [nbFirst, nbPrior, nbNext, nbLast,
      nbInsert, nbDelete, nbEdit, nbPost, nbCancel, nbRefresh];
  end;
end;

function TfrmCourseManage.Selected(var str: string; CourseID: string): Boolean;
begin
  Result := false;
  qryValid.Active := false; // 关闭数据查询组件
  qryValid.SQL.Clear; // 清楚原有的SQL语句
  qryValid.SQL.Add('SELECT * FROM CourseSelect, Class'); // 构造新的SQL语句
  qryValid.SQL.Add(' WHERE CourseSelect.ClassID=Class.ClassID');
  qryValid.SQL.Add('   AND CourseID=''' + CourseID + ''';');
  qryValid.Active := true; // 执行查询，打开数据集
  if not qryValid.Eof then
  begin
    repeat
      str := str + Format('%10s - %s'#13, [qryValid['ClassID'], qryValid['ClassName']]);
      qryValid.Next; // 记录指针后移
    until qryValid.Eof;
    Result := true;
  end;
  qryValid.Active := false; // 关闭数据查询组件
end;

procedure TfrmCourseManage.tblCourseBeforeDelete(DataSet: TDataSet);
var
  strMsg: string;
begin
  strMsg := Format('不能删除课程“%s”, 下面的班级：'#13, [tblCourse['CourseName']]);
  if Selected(strMsg, tblCourse['CourseID']) then
  begin
    strMsg := strMsg + '选修了该课程。';
    ShowMessage(strMsg);
    Abort;
  end;
  strMsg := Format('确定要删除课程"%s"吗？该课程相关信息如下：'#13, [tblCourse['CourseName']]);
  strMsg := strMsg + Format('  课程编号：%s'#13, [tblCourse['CourseID']]);
  strMsg := strMsg + Format('  课程名称：%s'#13, [tblCourse['CourseName']]);
  strMsg := strMsg + Format('  任课老师：%s'#13, [tblCourse['Teacher']]);
  if MessageDlg(strMsg, mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Abort;
end;

procedure TfrmCourseManage.tblCourseBeforePost(DataSet: TDataSet);
var
  strMsg: string;

  procedure CheckField(FieldName, FieldDesc: string; dbText: TCustomEdit; NotEmpty: Boolean);
  begin
    dbText.Text := Trim(dbText.Text);
    dbText.Text := StringReplace(dbText.Text, '''', '', [rfReplaceAll]);
    dbText.Text := StringReplace(dbText.Text, ' ', '', [rfReplaceAll]);
    if NotEmpty and (dbText.Text = '') then
    begin
      ShowMessageFmt('“%s”不能为空，请输入足够的信息！', [FieldDesc]);
      dbText.SetFocus;
      Abort;
    end
    else
      tblCourse.FieldByName(FieldName).AsString := dbText.Text;
  end;

  function ValidID(var str: string): Boolean;
  begin
    Result := true;
    qryValid.Active := false;
    qryValid.SQL.Clear;
    qryValid.SQL.Add('SELECT * FROM Course');
    qryValid.SQL.Add(' WHERE CourseID=''' + tblCourse['CourseID'] + ''';');
    qryValid.Active := true;
    if not qryValid.Eof then
    begin
      str := Format('课程编号“%s”已经存在，请重新输入!'#13, [qryValid['CourseID']]);
      str := str + Format('  课程编号：%s'#13, [qryValid['CourseID']]);
      str := str + Format('  课程名称：%s'#13, [qryValid['CourseName']]);
      str := str + Format('  任课老师：%s'#13, [qryValid['Teacher']]);
      dbeCourseID.SetFocus;
      Result := false;
    end;
    qryValid.Active := true;
  end;
begin
  CheckField('CourseID', '课程编号', dbeCourseID, true);
  CheckField('CourseName', '课程名称', dbeCourseName, true);
  CheckField('Teacher', '任课老师', dbeTeacher, true);
  CheckField('Credit', '学分', dbeCredit, true);
  CheckField('Period', '学时数', dbePeriod, true);
  CheckField('Description', '说明', dbmDesc, false);

  if dbeCourseID.Modified then
  begin
    if not ValidID(strMsg) then
    begin
      ShowMessage(strMsg);
      Abort;
    end;

    if tblCourse.State = dsEdit then
    begin
      strMsg := '是否修改确定要修改课程编号，下面的班级：'#13;
      if Selected(strMsg, OldCourseID) then
      begin
        strMsg := strMsg + '选修了该课程。点击“确定”按钮将级联更新'#13;
        strMsg := strMsg + '数据库中该课程相关记录中的编号。';
        if MessageDlg(strMsg, mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
          Abort
        else
        begin
          qryValid.Active := false;
          qryValid.SQL.Clear;
          qryValid.SQL.Add('UPDATE CourseSelect');
          qryValid.SQL.Add(' SET CourseID=''' + tblCourse['CourseID'] + '''');
          qryValid.SQL.Add(' WHERE CourseID=''' + OldCourseID + ''';');
          qryValid.ExecSQL;
          qryValid.SQL.Clear;
          qryValid.SQL.Add('UPDATE Score');
          qryValid.SQL.Add(' SET CourseID=''' + tblCourse['CourseID'] + '''');
          qryValid.SQL.Add(' WHERE CourseID=''' + OldCourseID + ''';');
          qryValid.ExecSQL;
        end;
      end;
    end;
  end;
end;

end.