unit Unit21;

interface

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

type
  TForm18 = class(TForm)
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    Panel2: TPanel;
    DBGrid2: TDBGrid;
    Panel3: TPanel;
    GroupBox1: TGroupBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn4: TBitBtn;
    GroupBox2: TGroupBox;
    Label3: TLabel;
    xh: TLabeledEdit;
    xm: TLabeledEdit;
    km: TComboBox;
    cj: TLabeledEdit;
    Panel4: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    bj: TComboBox;
    xq: TComboBox;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ADOQuery3: TADOQuery;
    procedure FormCreate(Sender: TObject);
    procedure bjChange(Sender: TObject);
    procedure xqChange(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure DBGrid2CellClick(Column: TColumn);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form18: TForm18;
  sqlstring,data_path,data_path2,data_path1:string;
implementation

uses Unit1;

{$R *.dfm}

procedure TForm18.FormCreate(Sender: TObject);
begin
 data_path:=application.GetNamePath+'user/'+form1.user+'/'+'班級.mdb';
 ADOQuery1.connectionstring:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+data_path+';';
 sqlstring:='select * from 班級表';
  bj.Clear;
  with ADOQuery1 do
    begin
      close;
      sql.Clear;
      sql.Add(sqlstring);
      open;
      first;
      while not eof do
         begin
           bj.Items.Add(fieldbyname('名稱').asstring);
            next;
         end;
    end;

end;
procedure TForm18.BitBtn1Click(Sender: TObject);
var
j,xuezhi:integer;
pjf,zf:double;
begin
 pjf:=0;
 zf:=0;
if (bj.Text='') or (xq.Text='') then
 begin
 showmessage('請選擇班級和學期!');
 exit;
 end;
 if (km.Text='') or (cj.Text='') then
 begin
 showmessage('請輸入學號或姓名');
 exit;
 end;

 sqlstring:='select * from 第'+trim(xq.Text)+'學期成績表 where 學號=:number';
  with  ADOQuery2 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
   Parameters.ParamByName('number').Value:=trim(xh.Text);
  open;
   if recordcount<1 then
   begin
  sqlstring:='insert into 第'+trim(xq.Text)+'學期成績表(學號,姓名,'+trim(km.text)+') values(:number,:name,:cj)';
  close;
  sql.Clear;
  sql.Add(sqlstring);
  Parameters.ParamByName('number').Value:=trim(xh.Text);
  Parameters.ParamByName('name').Value:=trim(xm.Text);
  Parameters.ParamByName('cj').Value:=strtofloat(trim(cj.Text));
  execsql;
  close;
  end
 else
  begin
  sqlstring:='update  第'+trim(xq.Text)+'學期成績表 set '+trim(km.text)+'=:cj where 學號=:number';
  close;
  sql.Clear;
  sql.Add(sqlstring);
  Parameters.ParamByName('number').Value:=trim(xh.Text);
  Parameters.ParamByName('cj').Value:=strtofloat(trim(cj.Text));
  execsql;
  
  end;
  end;

  
  sqlstring:='select * from 第'+trim(xq.Text)+'學期成績表';
  with  ADOQuery2 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
  open;
  end;
  with ADOQuery3 do
   begin

  next;
  xh.Text:=fieldbyname('學號').AsString;
  xm.Text:=fieldbyname('姓名').AsString;
     end;
end;

procedure TForm18.BitBtn4Click(Sender: TObject);
begin
close;
end;

procedure TForm18.DBGrid2CellClick(Column: TColumn);
begin
 with ADOQuery3 do
    begin
     xh.Text:=fieldbyname('學號').AsString;
     xm.Text:=fieldbyname('姓名').AsString;
   end;


end;

procedure TForm18.bjChange(Sender: TObject);
var


i,xuezhi:integer;
begin
  data_path2:=application.GetNamePath+'user/'+form1.user+'/'+trim(bj.Text)+'.mdb';
  ADOQuery3.connectionstring:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+data_path2+';';
  sqlstring:='select * from 班級表 where 名稱=:mc';
  xq.Clear;
  with ADOQuery1 do
    begin
      close;
      sql.Clear;
      sql.Add(sqlstring);
      Parameters.ParamByName('mc').Value:=trim(bj.Text);
      open;
      xuezhi:=fieldbyname('學制').AsInteger;
      end;
    for i:=1 to xuezhi*2 do
     xq.Items.Add(inttostr(i));

    sqlstring:='select 學號,姓名 from 學生信息表';
    form1.editdata(ADOQuery3,sqlstring,0);
end;

procedure TForm18.xqChange(Sender: TObject);
var
i,xuezhi:integer;

begin
  data_path1:=application.GetNamePath+'user/'+form1.user+'/'+trim(bj.Text)+'.mdb';
  ADOQuery2.connectionstring:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+data_path1+';';
  sqlstring:='select * from 第'+trim(xq.Text)+'學期成績表';
   km.Clear;
  with  ADOQuery2 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
  open;
  for i:=3 to  ADOQuery2.Recordset.Fields.Count-3 do //  讀取字段名
  begin
  km.Items.Add(ADOQuery2.Recordset.Fields[i].name);
  end;
 end ;
 end;

end.