unit6.pas

< SQL SERVER 2000 案例教程>>,冶金工业出版社,这本书的代码

unit Unit6;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls;

type
  Tform6 = class(TForm)
    Searchstuff: TButton;
    Button2: TButton;
    code: TEdit;
    address: TEdit;
    mobile: TEdit;
    stuffname: TEdit;
    age: TEdit;
    telephone: TEdit;
    mail: TEdit;
    man: TRadioButton;
    woman: TRadioButton;
    partment: TComboBox;
    starttime: TDateTimePicker;
    endtime: TDateTimePicker;
    remark: TMemo;
    job: TComboBox;
    jobstate: TComboBox;
    Checkaddress: TCheckBox;
    Checkcode: TCheckBox;
    Checkdepart: TCheckBox;
    Checkstate: TCheckBox;
    Checktime: TCheckBox;
    Checkremark: TCheckBox;
    Checkage: TCheckBox;
    Checkmobile: TCheckBox;
    Checktel: TCheckBox;
    Checkname: TCheckBox;
    Checkjob: TCheckBox;
    Checksex: TCheckBox;
    Checkmail: TCheckBox;
    Label1: TLabel;
    procedure SearchstuffClick(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  form6: Tform6;

implementation

{$R *.dfm}
uses Unit2;
procedure Tform6.SearchstuffClick(Sender: TObject);
var sign:boolean;
    value:Integer;
    temp:string;
begin
  temp:=Timetostr(starttime.Time);
  showmessage(temp);
  sign:=false;//标志前面是否选择查询条件
  with mydata.query_stuff do
  begin
    close;
    with sql do
    begin
      clear;
      //设定SQL语句，当没有条件选择时表示选择所有数据
      add('select * from 员工资料表');
      if (checkcode.Checked ) then //如果选择查询工号
      begin
        add(' where 员工号=:code');//添加查询条件
        ParamByName('code').AsString:=trim(code.Text);
        //表示已经有其他查询条件，主要是决定后面添加的SQL语是and 还是where
        sign:=true;
      end;
      if checkname.Checked then   //判断是否选择名字查询
      begin
        if sign then//如果已经有其他条件查询，则用and语句
        begin
          add(' and 员工名=:name');
        end else //如果没有,则用where语句
        begin
          add(' where 员工名=:name');
          sign:=true;
        end;
        paramByName('name').AsString:=trim(stuffname.Text);
      end;
      if checksex.Checked then //判断是否使用性别查询
      begin
        if man.Checked then
        begin
          value:=1;
        end else
        begin
          value:=0;
        end;
        if sign then
        begin
          add(' and 性别=:sex');
        end else
        begin
          add(' where 性别=:sex');
          sign:=true;
        end;
        ParamByName('sex').AsInteger:=value;
      end;
      if checkage.Checked then
      begin
        if sign then
        begin
          add(' and 年龄=:age');
        end else
        begin
          add(' where 年龄:=age');
          sign:=true;
        end;
        ParamByName('age').AsString:=trim(age.Text);
      end;
      if checktime.Checked then //判断是否使用合同日期查询
      begin
        if endtime.Date<starttime.Date then
        begin
          showmessage('结束时间不能小于开始时间');
        end else
        begin
          if sign then
          begin
            add(' and ');
          end else
          begin
            add(' where ');
            sign:=true;
          end;
          add('合同开始时间>=:sdate and 合同结束时间<=:edate');
          ParamByName('sdate').AsString:=datetostr(starttime.Date);
          ParamByName('edate').AsString:=datetostr(endtime.Date);
        end;
      end;
      if checkstate.Checked then  //判断是否使用工作状态查询
      begin
        if sign then
        begin
          add(' and 工作状态=:state');
        end else
        begin
          add(' where 工作状态=:state');
          sign:=true;
        end;
        ParamByName('state').AsString:=trim(jobstate.Text);
      end;
      if checkaddress.Checked then //判断是否使用地址查询
      begin
        if sign then
        begin
          add(' and 住址=:address');
        end else
        begin
          add(' where 住扯=:address');
          sign:=true;
        end;
        ParamByName('address').AsString:=trim(address.Text);
      end;
      if checktel.Checked then//判断是否使用电话号码查询
      begin
        if sign then
        begin
          add(' and 联系电话=:telephone');
        end else
        begin
          add(' where 联系电话=:telephone');
          sign:=true;
        end;
        ParamByName('telephone').AsString:=trim(telephone.Text);
      end;
      if checkmobile.Checked then  //判断是否使用手机查询
      begin
        if sign  then
        begin
          add(' and 手机=:mobile');
        end else
        begin
          add(' where 手机=:mobile');
          sign:=true;
        end;
        ParamByName('mobile').AsString:=trim(mobile.Text);
      end;
      if checkmail.Checked then //判断是否使用邮箱查询
      begin
        if sign then
        begin
          add(' and 邮箱=:mail');
        end else
        begin
          add(' where 邮箱=:mail');
          sign:=true;
        end;
        ParamByName('mail').AsString:=trim(mail.Text);
      end;
      //判断是否使用备注查询，备注查询使用模糊查询
      if checkremark.Checked then
      begin
        if  sign  then
        begin
        //模糊查询的语句是 like %condition% ,其中%表示匹配
          add(' and 备注 like :remark');
        end else
        begin
          add(' where 备注 like :remark');
        end;
        //给参数赋值的时候其值加上%号
        ParamByName('remark').AsString:=trim('%'+remark.Text+'%');
      end;
    end;
    open;
  end;
end;

procedure Tform6.Button2Click(Sender: TObject);
begin
  close;
end;

end.