unit7.~pas

sql server 2000 数据库与应用

unit Unit7;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, ExtCtrls, Grids, DBGrids, Spin, ComCtrls,unit1;

type
  TForm7 = class(TForm)
    Label4: TLabel;
    Label6: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label1: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    DBGrid1: TDBGrid;
    Panel1: TPanel;
    GroupBox1: TGroupBox;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    ADOQuery2: TADOQuery;
    Label5: TLabel;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Label7: TLabel;
    Edit3: TEdit;
    Edit4: TEdit;
    ADOQuery3: TADOQuery;
    ListBox1: TListBox;
    Edit5: TEdit;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    DBGrid2: TDBGrid;
    DataSource2: TDataSource;
    ADOQuery4: TADOQuery;
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ListBox1DblClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Edit5DblClick(Sender: TObject);
    procedure DBGrid2DblClick(Sender: TObject);
    procedure Edit1Change(Sender: TObject);
    procedure Edit2Change(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form7: TForm7;
  procedure ComboboxList1;
  procedure ComboboxList2;
  procedure listboxlist1;
  procedure adoquery3_initialize;
  procedure initialize;

implementation

{$R *.dfm}
procedure ComboboxList1;
begin
   form7.adoquery1.Close;
   form7.adoquery1.SQL.Clear;
   form7.adoquery1.SQL.Add('select distinct 系部编号,系部名称 from 系部班级');
   form7.adoquery1.Open;
   form7.ComboBox1.Items.Clear;
   form7.adoquery1.First;
   while not form7.adoquery1.Eof do
   begin
     //将系部编号和系部名称添加到组合框中
    form7.ComboBox1.Items.Add(form7.adoquery1.Fieldbyname('系部编号').value
     +'/'+form7.adoquery1.Fieldbyname('系部名称').value);
    form7.adoquery1.Next;
  end;
end;

procedure ComboboxList2;
var
  s1,xbbh: string;
  i: integer;
begin
  s1:=form7.ComboBox1.Text;
  i:=pos('/',s1);
  if i>0 then
    xbbh:=copy(s1,1,i-1)
  else
   xbbh:='';
   form7.ComboBox2.Text:='';
   form7.adoquery2.Close;
   form7.adoquery2.SQL.Clear;
   form7.adoquery2.SQL.Add('select 班级编号,班级名称 from 班级表 where 系部编号=:系部编号');
   form7.adoquery2.Parameters.ParamByName('系部编号').Value:=xbbh;
   form7.adoquery2.Open;
   form7.ComboBox2.Items.Clear;
   form7.adoquery2.First;
   while not form7.adoquery2.Eof do
   begin
    //将班级编号和班级名称添加到组合框中
    form7.ComboBox2.Items.Add(form7.adoquery2.Fieldbyname('班级编号').value
     +'/'+form7.adoquery2.Fieldbyname('班级名称').value);
    form7.adoquery2.Next;
  end;
end;

procedure listboxlist1;
var
  s1,xbbh: string;
  i: integer;
begin
  s1:=form7.ComboBox1.Text;
  i:=pos('/',s1);
  if i>0 then
    xbbh:=copy(s1,1,i-1)
  else
   xbbh:='';
   form7.ComboBox2.Text:='';
   form7.adoquery2.Close;
   form7.adoquery2.SQL.Clear;
   form7.adoquery2.SQL.Add('select 课程编号,课程名称 from 课程信息表 where 系部编号=:系部编号');
   form7.adoquery2.Parameters.ParamByName('系部编号').Value:=xbbh;
   form7.adoquery2.Open;
   form7.ListBox1.Items.Clear;
   form7.adoquery2.First;
   while not form7.adoquery2.Eof do
   begin
    //课程编号和课程名称添加到列表框1中
     form7.listbox1.Items.Add(form7.adoquery2.Fieldbyname('课程编号').value
     +'/'+form7.adoquery2.Fieldbyname('课程名称').value);
    form7.adoquery2.Next;
  end;
end;

procedure adoquery3_initialize;
var
  s1,xbbh,bjbh,kcbh: string;
  i: integer;
begin
  s1:=form7.combobox1.Text;
  i:=pos('/',s1);
  if i>0 then
    xbbh:=copy(s1,1,i-1)
  else
    xbbh:='';
  s1:=form7.combobox2.Text;
  i:=pos('/',s1);
  if i>0 then
    bjbh:=copy(s1,1,i-1)
  else
    bjbh:='';
  kcbh:=trim(form7.edit1.Text);
  form7.adoquery3.Close;
  form7.adoquery3.SQL.Clear;
  s1:='select s.学号, s.姓名, c.成绩'
    +' FROM 系部表 as x INNER JOIN'
    +' 班级表 as b ON x.系部编号 = b.系部编号 INNER JOIN'
    +' 学生基本信息表 as s ON'
    +' b.班级编号 = s.班级编号 INNER JOIN'
    +' 课程信息表 as k INNER JOIN'
    +' 成绩表 as c ON k.课程编号 = c.课程编号 ON'
    +' s.学号 = c.学号'
    +' WHERE (x.系部编号 = :xbbh) AND (k.课程编号 = :kcbh) AND (b.班级编号 = :bjbh)';
  form7.ADOQuery3.SQL.Add(s1);
  form7.adoquery3.Parameters.ParamByName('xbbh').Value:=xbbh;
  form7.adoquery3.Parameters.ParamByName('kcbh').Value:=kcbh;
  form7.adoquery3.Parameters.ParamByName('bjbh').Value:=bjbh;
  form7.adoquery3.Open;

end;

procedure initialize;
begin
  form7.edit3.Text:='';
  form7.edit3.ReadOnly:=True;
  form7.edit4.Text:='';
  form7.edit5.Text:='';
end;

procedure TForm7.Button2Click(Sender: TObject);
begin
  initialize;
end;

procedure TForm7.Button3Click(Sender: TObject);
begin
  form7.Close ;
end;

procedure TForm7.FormActivate(Sender: TObject);
begin
  comboboxlist1;
  initialize;
  dbgrid1.DataSource.AutoEdit:=false;
end;

procedure TForm7.ComboBox2Change(Sender: TObject);
var
  s1,bjbh: string;
  i: integer;
begin
  s1:=combobox2.Text;
  i:=pos('/',s1);
  if i>0 then
    bjbh:=copy(s1,1,i-1)
  else
    bjbh:='';
  form7.adoquery4.Close;
  form7.adoquery4.SQL.Clear;
  form7.adoquery4.SQL.Add('select 学号,姓名 from 学生基本信息表 where 班级编号=:班级编号');
  form7.adoquery4.Parameters.ParamByName('班级编号').Value:=bjbh;
  form7.adoquery4.Open;
  adoquery3_initialize;
 
end;

procedure TForm7.ComboBox1Change(Sender: TObject);
begin
  listboxlist1;
  comboboxlist2;
  adoquery3_initialize;
end;

procedure TForm7.ListBox1DblClick(Sender: TObject);
var
  s1,kcbh,kcmc: string;
  i,j: integer;
begin
  j:=listbox1.ItemIndex;
  s1:=listbox1.Items.Strings[j];
  i:=pos('/',s1);
  if i>0 then
    kcbh:=copy(s1,1,i-1)
  else
    kcbh:='';
  kcmc:=copy(s1,i+1,255);
  edit1.Text:=kcbh;
  edit2.Text:=kcmc;

end;

procedure TForm7.Button1Click(Sender: TObject);
begin
  if (edit1.Text='')or(edit4.Text='')or(edit5.Text='') then
    begin
      application.MessageBox('信息不完整！','提示',mb_ok);
      exit;
    end
  else
    if adoquery3.Locate('学号',edit5.Text,[loCaseInsensitive]) then
    begin
      application.MessageBox('成绩已存在，不能在此处修改！','提示',mb_ok);
      edit4.Text:='';
      exit;
    end
    else
    try
      adoquery1.close;
      adoquery1.sql.clear;
      Adoquery1.sql.add('insert into 成绩表 (学号,课程编号,成绩)'
      +'values (:xh,:kcbh,:cj)');
      Adoquery1.Parameters.ParamByName('xh').Value:=edit5.text;
      Adoquery1.Parameters.ParamByName('kcbh').Value:=edit1.Text;
      Adoquery1.Parameters.ParamByName('cj').Value:=strtofloat(trim(Edit4.Text));
      Adoquery1.ExecSQL;
      adoquery3.Requery();
      //application.MessageBox('成绩已录入！','提示',mb_ok);
    except
      on E: Exception do
        messagedlg(E.Message,mterror,[mbok],0);
  end;
end;

procedure TForm7.Edit5DblClick(Sender: TObject);
begin
  if adoquery4.Locate('学号',edit5.Text,[loCaseInsensitive]) then
    edit3.Text:=ADOQuery4.fieldbyname('姓名').Value
  else
    edit3.Text:='';

end;

procedure TForm7.DBGrid2DblClick(Sender: TObject);
begin
  if not form7.ADOQuery4.fieldbyname('姓名').IsNull then
    edit3.Text:=form7.ADOQuery4.fieldbyname('姓名').Value
  else
    edit3.Text:='';
  if not form7.ADOQuery4.fieldbyname('学号').IsNull then
    edit5.Text:=form7.ADOQuery4.fieldbyname('学号').Value
  else
    edit5.Text:='';

end;

procedure TForm7.Edit1Change(Sender: TObject);
begin
  adoquery3_initialize;
end;

procedure TForm7.Edit2Change(Sender: TObject);
begin
  adoquery3_initialize;
end;

end.