exado.pas

delphi的学习资料

unit ExADO;
interface
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, DBTables, Db, StdCtrls, ExtCtrls, Buttons, ADODB, variants;
type
  TfmExADO = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;    Label2: TLabel;    Label3: TLabel;
    Label4: TLabel;    Label5: TLabel;    Label6: TLabel;
    LbH: TLabel;
    LbW: TLabel;
    ckbEdit: TCheckBox;
    btnIns: TBitBtn;
    btnDel: TBitBtn;
    btnStudy: TBitBtn;
    dbg: TDBGrid;
    ds1: TDataSource;
    ADOdSet1: TADODataSet;
    ADOConnect: TADOConnection;
    //------------------
    sg: TStringGrid;
    ADOQry1: TADOQuery;
    ADOqry2: TADOQuery;
    GroupBox1: TGroupBox;
    rbtnNo: TRadioButton;
    rbtnSum: TRadioButton;
    rbtnSex: TRadioButton;
    GroupBox2: TGroupBox;
    rbtnASC: TRadioButton;
    rbtnDESC: TRadioButton;
    GroupBox3: TGroupBox;
    ckbM: TCheckBox;
    ckbF: TCheckBox;
    edtNo1: TEdit;
    edtNo2: TEdit;
    //------------------
    pnStudy: TPanel;
    btnOK: TBitBtn;
    btnCancel: TBitBtn;
    sg2: TStringGrid;
    ADOTb2: TADOTable;
    procedure FormActivate(Sender: TObject);
    procedure dbgColExit(Sender: TObject);
    procedure rbtn_OrdClick(Sender: TObject);
    procedure ckbEditClick(Sender: TObject);
    procedure edtNo_Exit(Sender: TObject);
    procedure edtNo_KeyDown(Sender:TObject; var Key:Word; Shift:TShiftState);
    procedure btnInsClick(Sender: TObject);
    procedure btnDelClick(Sender: TObject);
    procedure dbgKeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure dbgCellClick(Column: TColumn);
    procedure ADOdSet1AfterPost(DataSet: TDataSet);
    procedure sgClick(Sender: TObject);
    procedure sgKeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure btnStudyClick(Sender: TObject);
    procedure pnStudyMouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure pnStudyMouseMove(Sender: TObject; Shift:TShiftState; X,Y:Integer);
    procedure btnOKClick(Sender: TObject);
    procedure btnCancelClick(Sender: TObject);
  private    { Private declarations }
    procedure Condition();  //组织查询条件
    procedure StudyProc(No: string='');  //成绩计算
    procedure dbgRenew();   //dbg 更新
    procedure sgRenew();    //sg 更新
    procedure sgHighLight(No: string);  //找到sg中与dbg对应的行，并高亮显示
  public     { Public declarations }
  end;

var
  fmExADO: TfmExADO;
  sCondit: string;  //查询条件
  x0,y0: integer;   //鼠标右键按下时的鼠标坐标

const sgHead:array[0..5] of string=(' 学 号',' 姓 名', ' 性别','出生年月',
          ' 总成绩',' 备 注');
const sgHead2:array[0..3] of string=(' 学 号',' 科目1',' 科目2', ' 科目3');



////////////////////////////////////////////////////////////////////
implementation
{$R *.DFM}

//==============================================================================
procedure TfmExADO.FormActivate(Sender: TObject);
var i: byte;
begin
  for i:=0 to 5 do
    sg.Cells[i,0]:=sgHead[i];
  for i:=0 to 3 do
    sg2.Cells[i,0]:=sgHead2[i];

  ckbEditClick(Self);     // 有何作用?
  Condition();

  ADOdSet1.Close;
  ADOdSet1.CommandType:=cmdText;
  ADOdSet1.CommandText:='Select * From BASIC';
  ADOdSet1.Filter:=sCondit;  ADOdSet1.Filtered:=True;   //???
  ADOdSet1.Open;

  dbgRenew();
  StudyProc();
  rbtn_OrdClick(Self);  //含sgRenew();
end;

//==============================================================================
procedure TfmExADO.rbtn_OrdClick(Sender: TObject);
begin
  sgRenew();
  dbgCellClick(nil);    // 排序变化后，必须重新定位sg的高亮行
end;

//==============================================================================
procedure TfmExADO.ckbEditClick(Sender: TObject);
begin
  if ckbEdit.Checked then   //原来是Checked, 则......
      dbg.Options:=dbg.Options+[dgEditing]-[dgRowSelect]
  else
    begin
      dbg.Options:=dbg.Options-[dgEditing]+[dgRowSelect];
      pnStudy.Visible:=False;
    end;

  btnIns.Enabled:=ckbEdit.Checked;    //***
  btnDel.Enabled:=ckbEdit.Checked;    //***
  btnStudy.Enabled:=ckbEdit.Checked;  //***
end;

//==============================================================================
procedure TfmExADO.edtNo_KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if not Key in [0..9] then
    Key:=0;
  if Key=13 then
    sg.SetFocus;  //于是，会触发edtNo1Exit
end;

//==============================================================================
procedure TfmExADO.edtNo_Exit(Sender: TObject);
begin
  FormActivate(Self);   // 为了做表格显示更新
end;

//==============================================================================
procedure TfmExADO.btnInsClick(Sender: TObject);
begin
  dbg.DataSource.DataSet.Append;
  sgRenew();      // 此句必要吗？
end;

//==============================================================================
procedure TfmExADO.btnDelClick(Sender: TObject);
begin
  With ADOqry1 do
  begin
    Close;
    SQL.Text:='DELETE FROM Study WHERE Sno='+dbg.DataSource.DataSet.FieldByName('Sno').AsString;
    ExecSQL;
    dbg.DataSource.DataSet.Delete;
    sgRenew();    // 此句必要吗？
  end;
end;

//=============== 光标离开dbg的某列后 ==========================================
procedure TfmExADO.dbgColExit(Sender: TObject);
begin
//  dbgRenew(); //dbg 更新    //******
end;

//================= 提交到数据库后 =============================================
procedure TfmExADO.ADOdSet1AfterPost(DataSet: TDataSet);
var
  NewSno: string;
  RecN: Longword;
begin               //******
{  NewSno:=Trim(dbg.DataSource.DataSet.FieldByName('Sno').AsString);
  try
    With ADOqry1 do
    begin
      Close;
      SQL.Text:='SELECT Sno FROM Study WHERE Sno='+NewSno;
      Open;
      RecN:=RecordCount;
      Close;
      if RecN=0 then
        SQL.Text:='INSERT INTO Study (Sno,SumM) Values('+NewSno+',0)';
        ExecSQL;
    end;
  except
    ADOqry1.Cancel;
  end;
}
  dbgRenew();
  sgRenew();      // 此句必要吗？
end;

//================= sg 更新 ====================================================
procedure TfmExADO.sgRenew();
var i,j: word;
   pp: byte;
   s,sOrd,s2: string;
begin
  if dbg.DataSource.DataSet.RecordCount> 15 then
    sg.Rowcount:=dbg.DataSource.DataSet.RecordCount+2;  //******  或 +1

  for i:=0 to 5 do    //先清空显示
    for j:=1 to sg.Rowcount-1 do
      sg.Cells[i,j]:='';

  ADOqry1.Close;
  s:='SELECT Sno,Name,Sex,Birth,Summ FROM Basic,Study WHERE'+
     ' Basic.Sno=Study.Sno';
  if sCondit<>'' then
    s:=s+' AND '+sCondit;
  s:=s+' Order By ';    //排序
  if rbtnNo.Checked then
    sOrd:='Sno'
  else
    if rbtnSum.Checked then
      sOrd:='Summ'
    else
      sOrd:='Sex,Sno' ;

  if rbtnDESC.Checked then
    begin
      sOrd:=sOrd+' DESC';   //倒排序
      if rbtnSex.Checked then
        begin
          pp:=Pos(',',sOrd);
          sOrd:=Copy(sOrd,1,pp-1)+' DESC'+Copy(sOrd,pp,22);
        end;
    end;
  ADOqry1.SQL.Text:=s+sOrd;  //***
  ADOqry1.Open;

  j:=1;
  while not ADOqry1.Eof do
  begin
    for i:=0 to 4 do
    begin
      s2:=ADOqry1.Fields[i].AsString;  //字段的原始形式
      case i of    //改变字段的显示方式
        1:  if Length(s2)=4 then Insert('  ',S2,3);
        2:  if s2='M' then
              s2:='  男'
            else
              if s2='F' then
                s2:='  女';
        3: s2:=Copy(s2,1,2)+'年'+Copy(s2,3,2)+'月';
        else ;
      end;  // case i
      sg.Cells[i,j]:=s2;
    end;  //for i
    j:=j+1;
    ADOqry1.Next;
  end;
  ADOqry1.Close;
end;

//================ dbg 更新 ====================================================
procedure TfmExADO.dbgRenew();
var i: word;