unit2.pas

sql server 2000 数据库与应用

    else if (trim(edit13.Text)<>'') and (trim(edit14.Text)='') then
      s1:=s1+' where 学号 like '+quotedstr('%'+trim(edit13.Text)+'%')
    else if (trim(edit13.Text)='') and (trim(edit14.Text)<>'') then
      s1:=s1+' where 姓名 like '+quotedstr('%'+trim(edit14.Text)+'%');
    Adoquery3.sql.add(s1);
    Adoquery3.Open;
  except
    on E: Exception do
      messagedlg(E.Message,mterror,[mbok],0);
  end;

end;

procedure Tform2.Button5Click(Sender: TObject);
var
  s1,bjbh,kcbh:string;
  i:integer;
begin
  DBgrid1.Font.Color:=clteal;
  if (trim(edit25.Text)='') or (trim(edit26.Text)='') or (trim(combobox1.Text)='')then
    application.MessageBox('数据不完整！','提示',mb_ok)
  else
    try
      //根据多个条件进行精确查询
      s1:=trim(ComboBox1.Text);
      i:=pos('/',s1);
      if i>0 then
        kcbh:=copy(s1,1,i-1)
      else
        kcbh:='';
      s1:=trim(edit26.Text);
      i:=pos('/',s1);
      if i>0 then
        bjbh:=copy(s1,1,i-1)
      else
        bjbh:='';
      adoquery3.close;
      adoquery3.sql.clear;
      s1:='select * from 成绩视图'
        +' where 班级编号='+quotedstr(bjbh)
        +' and 课程编号='+quotedstr(kcbh);
      if trim(edit13.Text)<>'' then
        s1:=s1+' and 学号='+quotedstr(trim(edit13.Text));
      if trim(edit14.Text)<>'' then
        s1:=s1+' and 姓名='+quotedstr(trim(edit14.Text));
      Adoquery3.sql.add(s1);
      Adoquery3.Open;
    except
      on E: Exception do
        messagedlg(E.Message,mterror,[mbok],0);
    end;
end;

procedure Tform2.Button7Click(Sender: TObject);
begin
  edit13.Text:='';
  edit14.Text:='';
end;

procedure Tform2.PageControl1Change(Sender: TObject);
var
  s1,s2,bjbh,kcbh:string;
  i:integer;
begin
  case pagecontrol1.TabIndex of
  1:begin
      s1:=trim(edit24.Text);
      i:=pos('/',s1);
      if i>0 then
        bjbh:=copy(s1,1,i-1)
     else
       bjbh:='';
       s1:='select * from 学生基本信息表';
       s2:=' order by 学号';
       if (bjbh<>'') then
         s1:=s1+' where 班级编号='+quotedstr(bjbh)+s2
       else
         s1:=s1+s2;
       adoquery3.Close;
       adoquery3.SQL.Clear;
       adoquery3.SQL.Add(s1);
       adoquery3.Open;
    end;
  0,2:begin
    adoquery3.Close;
    adoquery3.SQL.Clear;
      end;
  3:begin
      edit18.Text:='';
      edit19.Text:='';
      edit20.Text:='';
      s1:=trim(edit28.Text);
      i:=pos('/',s1);
      if i>0 then
        bjbh:=copy(s1,1,i-1)
     else
       bjbh:='';
      s1:=trim(combobox2.Text);
      i:=pos('/',s1);
      if i>0 then
        kcbh:=copy(s1,1,i-1)
     else
       kcbh:='';
     s1:='select * from 成绩视图';
     s2:=' order by 学号';
     if (bjbh<>'') and (kcbh<>'') then
       s1:=s1+' where 班级编号='+quotedstr(bjbh)
         +' and 课程编号='+quotedstr(kcbh)+s2
     else if (bjbh<>'') and (kcbh='') then
       s1:=s1+' where 班级编号='+quotedstr(bjbh)+s2
     else if (bjbh='') and (kcbh<>'') then
       s1:=s1+' where 课程编号='+quotedstr(kcbh)+s2
     else if (bjbh='') and (kcbh<>'') then
       s1:=s1+s2;
     adoquery3.Close;
     adoquery3.SQL.Clear;
     adoquery3.SQL.Add(s1);
     adoquery3.Open;
    end;
  end;
end;

procedure Tform2.DBGrid1CellClick(Column: TColumn);
begin
case pagecontrol1.TabIndex of
1:begin
  if not ADOQuery3.fieldbyname('学号').IsNull then
    edit3.Text:=trim(ADOQuery3.fieldbyname('学号').Value)
  else
    edit3.Text:='';
  if not ADOQuery3.fieldbyname('姓名').IsNull then
    edit4.Text:=trim(ADOQuery3.fieldbyname('姓名').Value)
  else
    edit4.Text:='';
  if not ADOQuery3.fieldbyname('性别').IsNull then
    combobox5.Text:=trim(ADOQuery3.fieldbyname('性别').Value)
  else
    combobox5.Text:='';
  if not ADOQuery3.fieldbyname('族别').IsNull then
    edit5.Text:=trim(ADOQuery3.fieldbyname('族别').Value)
  else
    edit5.Text:='';
  if not ADOQuery3.fieldbyname('出生日期').IsNull then
    edit6.Text:=trim(ADOQuery3.fieldbyname('出生日期').Value)
  else
    edit6.Text:='';
  if not ADOQuery3.fieldbyname('政治面貌').IsNull then
    edit7.Text:=trim(ADOQuery3.fieldbyname('政治面貌').Value)
  else
    edit7.Text:='';
  if not ADOQuery3.fieldbyname('邮政编码').IsNull then
    edit8.Text:=trim(ADOQuery3.fieldbyname('邮政编码').Value)
  else
    edit8.Text:='';
  if not ADOQuery3.fieldbyname('家庭住址').IsNull then
    edit9.Text:=trim(ADOQuery3.fieldbyname('家庭住址').Value)
  else
    edit9.Text:='';
  if not ADOQuery3.fieldbyname('联系电话').IsNull then
    edit10.Text:=trim(ADOQuery3.fieldbyname('联系电话').Value)
  else
    edit10.Text:='';
  end;
3:begin
  if not ADOQuery3.fieldbyname('学号').IsNull then
    edit18.Text:=trim(ADOQuery3.fieldbyname('学号').Value)
  else
    edit18.Text:='';
  if not ADOQuery3.fieldbyname('姓名').IsNull then
    edit19.Text:=trim(ADOQuery3.fieldbyname('姓名').Value)
  else
    edit19.Text:='';
  if not ADOQuery3.fieldbyname('成绩').IsNull then
    edit20.Text:=trim(ADOQuery3.fieldbyname('成绩').Value)
  else
    edit20.Text:='';
  end;
end;
end;

procedure Tform2.DBGrid1KeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (key=VK_UP) or (key=VK_DOWN) then
 begin
  case pagecontrol1.TabIndex of
  1:begin
    if not ADOQuery3.fieldbyname('学号').IsNull then
      edit3.Text:=trim(ADOQuery3.fieldbyname('学号').Value)
    else
      edit3.Text:='';
    if not ADOQuery3.fieldbyname('姓名').IsNull then
      edit4.Text:=trim(ADOQuery3.fieldbyname('姓名').Value)
    else
      edit4.Text:='';
    if not ADOQuery3.fieldbyname('性别').IsNull then
      combobox5.Text:=trim(ADOQuery3.fieldbyname('性别').Value)
    else
      combobox5.Text:='';
    if not ADOQuery3.fieldbyname('族别').IsNull then
      edit5.Text:=trim(ADOQuery3.fieldbyname('族别').Value)
    else
      edit5.Text:='';
    if not ADOQuery3.fieldbyname('出生日期').IsNull then
      edit6.Text:=trim(ADOQuery3.fieldbyname('出生日期').Value)
    else
      edit6.Text:='';
    if not ADOQuery3.fieldbyname('政治面貌').IsNull then
      edit7.Text:=trim(ADOQuery3.fieldbyname('政治面貌').Value)
    else
      edit7.Text:='';
    if not ADOQuery3.fieldbyname('邮政编码').IsNull then
      edit8.Text:=trim(ADOQuery3.fieldbyname('邮政编码').Value)
    else
      edit8.Text:='';
    if not ADOQuery3.fieldbyname('家庭住址').IsNull then
      edit9.Text:=trim(ADOQuery3.fieldbyname('家庭住址').Value)
    else
      edit9.Text:='';
    if not ADOQuery3.fieldbyname('联系电话').IsNull then
      edit10.Text:=trim(ADOQuery3.fieldbyname('联系电话').Value)
    else
      edit10.Text:='';
    end;
  3:begin
    if not ADOQuery3.fieldbyname('学号').IsNull then
      edit18.Text:=trim(ADOQuery3.fieldbyname('学号').Value)
    else
      edit18.Text:='';
    if not ADOQuery3.fieldbyname('姓名').IsNull then
      edit19.Text:=trim(ADOQuery3.fieldbyname('姓名').Value)
    else
      edit19.Text:='';
    if not ADOQuery3.fieldbyname('成绩').IsNull then
      edit20.Text:=trim(ADOQuery3.fieldbyname('成绩').Value)
    else
      edit20.Text:='';
    end;
  end;
 end;
end;

procedure Tform2.Button4Click(Sender: TObject);
var
  bjbh,s1: string;
  i:integer;
begin
  if (trim(edit3.Text)='') or (trim(edit4.Text)='') then
    application.MessageBox('必须指定学号和姓名！','提示',mb_ok)
  else
  begin
    try
      s1:=trim(edit24.Text);
      i:=pos('/',s1);
      if i>0 then
        bjbh:=copy(s1,1,i-1)
      else
         bjbh:='';
      adoquery1.close;
      adoquery1.sql.clear;
      s1:='update 学生基本信息表 set 姓名='+quotedstr(trim(edit4.Text))
        +',性别='+quotedstr(trim(combobox5.Text))
        +',族别='+quotedstr(trim(edit5.Text))
       // +',出生日期='+strtodatetime(Edit6.Text)
        +',出生日期=:出生日期'
        +',政治面貌='+quotedstr(trim(edit7.Text))
        +',邮政编码='+quotedstr(trim(edit8.Text))
        +',家庭住址='+quotedstr(trim(edit9.Text))
        +',联系电话='+quotedstr(trim(edit10.Text))
        +',班级编号='+quotedstr(bjbh)
        +' where 学号='+quotedstr(trim(edit3.Text));
      Adoquery1.sql.add(s1);
      Adoquery1.parameters.parambyname('出生日期').value:=strtodatetime(Edit6.Text);
      Adoquery1.ExecSQL;
      adoquery3.Requery();
      application.MessageBox('学生基本信息修改成功！','提示',mb_ok);
    except
      on E: Exception do
        messagedlg(E.Message,mterror,[mbok],0);
    end;
  end;
end;

procedure Tform2.Button3Click(Sender: TObject);
begin
  if (trim(edit3.Text)='') or (trim(edit4.Text)='') then
    application.MessageBox('未选择要删除的纪录！','提示',mb_ok)
  else if application.MessageBox('你确实要删除当前纪录吗？','警告!',mb_okcancel)=idok then
    begin
      adoquery1.close;
      adoquery1.sql.clear;
      Adoquery1.sql.add('delete from 学生基本信息表 where 学号='+quotedstr(trim(edit3.Text))
        +' and 姓名='+quotedstr(trim(edit4.Text)));
      Adoquery1.ExecSQL;
      adoquery3.Requery();
      //或者使用下面的1条语句替代上面5条语句
      //adoquery3.Delete;
      application.MessageBox('学生记录删除成功！','提示',mb_ok);
    end;

end;

procedure Tform2.ComboBox2Change(Sender: TObject);
var
  s1,s2,bjbh,kcbh:string;
  i:integer;
begin
  s1:=trim(edit28.Text);
  i:=pos('/',s1);
  if i>0 then
    bjbh:=copy(s1,1,i-1)
  else
    bjbh:='';
  s1:=trim(combobox2.Text);
  i:=pos('/',s1);
  if i>0 then
    kcbh:=copy(s1,1,i-1)
  else
    kcbh:='';
  s1:='select * from 成绩视图';
  s2:=' order by 学号';
  if (bjbh<>'') and (kcbh<>'') then
    s1:=s1+' where 班级编号='+quotedstr(bjbh)
      +' and 课程编号='+quotedstr(kcbh)+s2
  else if (bjbh<>'') and (kcbh='') then
    s1:=s1+' where 班级编号='+quotedstr(bjbh)+s2
  else if (bjbh='') and (kcbh<>'') then
    s1:=s1+' where 课程编号='+quotedstr(kcbh)+s2
  else if (bjbh='') and (kcbh<>'') then
    s1:=s1+s2;
  adoquery3.Close;
  adoquery3.SQL.Clear;
  adoquery3.SQL.Add(s1);
  adoquery3.Open;
end;

procedure Tform2.Button9Click(Sender: TObject);
var
  xh,xm,kcbh,s1: string;
  i:integer;
  cj:variant;
begin
  s1:=trim(combobox2.Text);
  i:=pos('/',s1);
  if i>0 then
    kcbh:=copy(s1,1,i-1)
  else
    kcbh:='';
  xh:=trim(edit18.Text);
  xm:=trim(edit19.Text);
  cj:=strtofloat(Edit20.Text);
  if (xh='') or (xm='') or (kcbh='') then
    application.MessageBox('必须指定学号、姓名和课程编号！','提示',mb_ok)
  else
  begin
    try
      adoquery1.close;
      adoquery1.sql.clear;
      s1:='update 成绩表 set 成绩=:cj'
        +' where 学号='+quotedstr(trim(edit18.Text))
        +' and 课程编号='+quotedstr(kcbh);
      Adoquery1.sql.add(s1);
      Adoquery1.parameters.parambyname('cj').value:=cj;
      Adoquery1.ExecSQL;
      adoquery3.Requery();
    except
      on E: Exception do
        messagedlg(E.Message,mterror,[mbok],0);
    end;
  end;
end;

procedure Tform2.Button8Click(Sender: TObject);
var
  xh,xm,kcbh,s1: string;
  i:integer;
begin
  s1:=trim(combobox2.Text);
  i:=pos('/',s1);
  if i>0 then
    kcbh:=copy(s1,1,i-1)
  else
    kcbh:='';
  xh:=trim(edit18.Text);
  xm:=trim(edit19.Text);
  if (xh='') or (xm='') or (kcbh='') then
    application.MessageBox('必须指定学号、姓名和课程编号！','提示',mb_ok)
  else if application.MessageBox('你确实要删除当前纪录吗？','警告!',mb_okcancel)=idok then
    begin
      adoquery1.close;
      adoquery1.sql.clear;
      Adoquery1.sql.add('delete from 成绩表 where 学号='+quotedstr(xh)
        +' and 课程编号='+quotedstr(kcbh));
      Adoquery1.ExecSQL;
      adoquery3.Requery();
      //或者使用下面的1条语句替代上面5条语句
      //adoquery3.Delete;
      application.MessageBox('学生成绩删除成功！','提示',mb_ok);
    end;

end;

procedure Tform2.ToolButton6Click(Sender: TObject);
begin
  PageControl1.TabIndex:=2;
end;

procedure Tform2.ToolButton1Click(Sender: TObject);
begin
  form2.Close;
end;

end.