我写了个程序,将EXCEL中的数据导入SQL2000数据库中,其中要用到一个功能就是EXCEL中的数据,在SQL2000中有几个表,然后我就需要分别导入,但是我写出来的程序,只导入了一个表,不知道怎么回事,请高手看看!~代码在下面!!!
unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, RzButton, ADODB, Provider, DBClient, DB, Grids, DBGridEh,
RzShellDialogs,comObj,ActiveX, StdCtrls;
type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
DBGridEh1: TDBGridEh;
DataSource1: TDataSource;
ADOConnection2: TADOConnection;
ADOQuery1: TADOQuery;
DataSource2: TDataSource;
DBGridEh2: TDBGridEh;
RzBitBtn1: TRzBitBtn;
ADOQuery2: TADOQuery;
ADOQuery3: TADOQuery;
procedure FormCreate(Sender: TObject);
procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
procedure RzBitBtn1Click(Sender: TObject);
procedure ADOQuery1BeforePost(DataSet: TDataSet);
procedure ADOQuery1AfterPost(DataSet: TDataSet);
procedure ADOQuery1PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
procedure ADOQuery3PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
procedure ADOQuery3BeforePost(DataSet: TDataSet);
procedure ADOQuery3AfterPost(DataSet: TDataSet); private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;
implementation{$R *.dfm}procedure TForm1.FormCreate(Sender: TObject);
begin
try
ADOConnection2.Connected:=true;
ADOConnection1.Connected:=true;
Except
Application.MessageBox('数据库连接失败,请检查参数!','警告',mb_iconwarning)
end;
end;procedure TForm1.ADOQuery1BeforePost(DataSet: TDataSet);
begin
ADOConnection2.BeginTrans;
end;procedure TForm1.ADOQuery1AfterPost(DataSet: TDataSet);
begin
ADOConnection2.CommitTrans;
end;procedure TForm1.ADOQuery1PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
begin
ADOConnection2.RollbackTrans;
Action := daAbort;
end;procedure TForm1.RzBitBtn1Click(Sender: TObject);
var
I,iNo:Integer;
sGUID,strNo:string;
TmpGUID:TGUID;
begin
with ADOQuery2 do
begin
ADOQuery2.SQL.clear;
ADOQuery2.SQL.add('select * from [sheet1$]');
ADOQuery2.Open;
ADOQuery2.First;
end; ADOQuery1.SQL.Clear;
ADOQuery1.SQL.add('select * from Pub_Btype');
ADOQuery1.Open;
iNo:=1;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.add('select * from Pub_Btype_Assist');
ADOQuery3.Open;
with Adoquery2 do while not eof do
begin
if CoCreateGUID(TmpGUID) = S_OK then
sGUID := GUIDToString(TmpGUID) ;
ADOQuery1.Append;
strNo:='00001'+'0000'+IntToStr(iNo);
inc(iNo);
ADOQuery1.FieldByName('BTypeID').AsString:=strNo;
ADOQuery1.FieldByName('_id').asstring:=sguid;
ADOQuery1.FieldByName('parid').AsString:='00001' ;
ADOQuery1.FieldByName('_Level').AsString:='2' ;
ADOQuery1.FieldByName('Modetype').AsString:='1' ;
ADOQuery1.FieldByName('PriceType').AsString:='preprice1' ;
ADOQuery1.FieldByName('FullName').asstring:=adoquery2.fieldbyname('名称').asstring;
ADOQuery1.FieldByName('UserCode').asstring:=adoquery2.fieldbyname('编码').asstring;
ADOQuery1.FieldByName('abbrName').asstring:=adoquery2.fieldbyname('简码').asstring;
ADOQuery1.FieldByName('Comment').asstring:=adoquery2.fieldbyname('备注').asstring;
next;
end;
if AdoQuery1.state in [dsinsert,dsEdit] then ADOQuery1.Post;
with Adoquery2 do while not eof do
begin
if CoCreateGUID(TmpGUID) = S_OK then
sGUID := GUIDToString(TmpGUID) ;
ADOQuery3.Append;
ADOQuery3.FieldByName('BID').asstring:=sguid;
ADOQuery3.FieldByName('TaxNumber').asstring:=ADOQuery2.fieldbyname('税号').asstring;
ADOQuery3.FieldByName('Bank').asstring:=ADOQuery2.fieldbyname('银行账号').asstring;
next;
end;
if AdoQuery3.state in [dsinsert,dsEdit] then ADOQuery3.Post;
end;procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
ADOConnection1.Connected:=false;
ADOConnection2.Connected:=false;
end;
procedure TForm1.ADOQuery3PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
begin
ADOConnection2.RollbackTrans;
Action := daAbort;
end;procedure TForm1.ADOQuery3BeforePost(DataSet: TDataSet);
begin
ADOConnection2.BeginTrans;
end;procedure TForm1.ADOQuery3AfterPost(DataSet: TDataSet);
begin
ADOConnection2.CommitTrans;
end;end.
unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, RzButton, ADODB, Provider, DBClient, DB, Grids, DBGridEh,
RzShellDialogs,comObj,ActiveX, StdCtrls;
type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
DBGridEh1: TDBGridEh;
DataSource1: TDataSource;
ADOConnection2: TADOConnection;
ADOQuery1: TADOQuery;
DataSource2: TDataSource;
DBGridEh2: TDBGridEh;
RzBitBtn1: TRzBitBtn;
ADOQuery2: TADOQuery;
ADOQuery3: TADOQuery;
procedure FormCreate(Sender: TObject);
procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
procedure RzBitBtn1Click(Sender: TObject);
procedure ADOQuery1BeforePost(DataSet: TDataSet);
procedure ADOQuery1AfterPost(DataSet: TDataSet);
procedure ADOQuery1PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
procedure ADOQuery3PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
procedure ADOQuery3BeforePost(DataSet: TDataSet);
procedure ADOQuery3AfterPost(DataSet: TDataSet); private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;
implementation{$R *.dfm}procedure TForm1.FormCreate(Sender: TObject);
begin
try
ADOConnection2.Connected:=true;
ADOConnection1.Connected:=true;
Except
Application.MessageBox('数据库连接失败,请检查参数!','警告',mb_iconwarning)
end;
end;procedure TForm1.ADOQuery1BeforePost(DataSet: TDataSet);
begin
ADOConnection2.BeginTrans;
end;procedure TForm1.ADOQuery1AfterPost(DataSet: TDataSet);
begin
ADOConnection2.CommitTrans;
end;procedure TForm1.ADOQuery1PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
begin
ADOConnection2.RollbackTrans;
Action := daAbort;
end;procedure TForm1.RzBitBtn1Click(Sender: TObject);
var
I,iNo:Integer;
sGUID,strNo:string;
TmpGUID:TGUID;
begin
with ADOQuery2 do
begin
ADOQuery2.SQL.clear;
ADOQuery2.SQL.add('select * from [sheet1$]');
ADOQuery2.Open;
ADOQuery2.First;
end; ADOQuery1.SQL.Clear;
ADOQuery1.SQL.add('select * from Pub_Btype');
ADOQuery1.Open;
iNo:=1;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.add('select * from Pub_Btype_Assist');
ADOQuery3.Open;
with Adoquery2 do while not eof do
begin
if CoCreateGUID(TmpGUID) = S_OK then
sGUID := GUIDToString(TmpGUID) ;
ADOQuery1.Append;
strNo:='00001'+'0000'+IntToStr(iNo);
inc(iNo);
ADOQuery1.FieldByName('BTypeID').AsString:=strNo;
ADOQuery1.FieldByName('_id').asstring:=sguid;
ADOQuery1.FieldByName('parid').AsString:='00001' ;
ADOQuery1.FieldByName('_Level').AsString:='2' ;
ADOQuery1.FieldByName('Modetype').AsString:='1' ;
ADOQuery1.FieldByName('PriceType').AsString:='preprice1' ;
ADOQuery1.FieldByName('FullName').asstring:=adoquery2.fieldbyname('名称').asstring;
ADOQuery1.FieldByName('UserCode').asstring:=adoquery2.fieldbyname('编码').asstring;
ADOQuery1.FieldByName('abbrName').asstring:=adoquery2.fieldbyname('简码').asstring;
ADOQuery1.FieldByName('Comment').asstring:=adoquery2.fieldbyname('备注').asstring;
next;
end;
if AdoQuery1.state in [dsinsert,dsEdit] then ADOQuery1.Post;
with Adoquery2 do while not eof do
begin
if CoCreateGUID(TmpGUID) = S_OK then
sGUID := GUIDToString(TmpGUID) ;
ADOQuery3.Append;
ADOQuery3.FieldByName('BID').asstring:=sguid;
ADOQuery3.FieldByName('TaxNumber').asstring:=ADOQuery2.fieldbyname('税号').asstring;
ADOQuery3.FieldByName('Bank').asstring:=ADOQuery2.fieldbyname('银行账号').asstring;
next;
end;
if AdoQuery3.state in [dsinsert,dsEdit] then ADOQuery3.Post;
end;procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
ADOConnection1.Connected:=false;
ADOConnection2.Connected:=false;
end;
procedure TForm1.ADOQuery3PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
begin
ADOConnection2.RollbackTrans;
Action := daAbort;
end;procedure TForm1.ADOQuery3BeforePost(DataSet: TDataSet);
begin
ADOConnection2.BeginTrans;
end;procedure TForm1.ADOQuery3AfterPost(DataSet: TDataSet);
begin
ADOConnection2.CommitTrans;
end;end.
var
I,iNo:Integer;
sGUID,strNo:string;
TmpGUID:TGUID;
begin
with ADOQuery2 do
begin
ADOQuery2.SQL.clear;
ADOQuery2.SQL.add('select * from [sheet1$]');
ADOQuery2.Open;
ADOQuery2.First;
end; ADOQuery1.SQL.Clear;
ADOQuery1.SQL.add('select * from Pub_Btype');
ADOQuery1.Open;
iNo:=1;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.add('select * from Pub_Btype_Assist');
ADOQuery3.Open;
with Adoquery2 do while not eof do
begin
if CoCreateGUID(TmpGUID) = S_OK then
sGUID := GUIDToString(TmpGUID) ;
ADOQuery1.Append;
strNo:='00001'+'0000'+IntToStr(iNo);
inc(iNo);
ADOQuery1.FieldByName('BTypeID').AsString:=strNo;
ADOQuery1.FieldByName('_id').asstring:=sguid;
ADOQuery1.FieldByName('parid').AsString:='00001' ;
ADOQuery1.FieldByName('_Level').AsString:='2' ;
ADOQuery1.FieldByName('Modetype').AsString:='1' ;
ADOQuery1.FieldByName('PriceType').AsString:='preprice1' ;
ADOQuery1.FieldByName('FullName').asstring:=adoquery2.fieldbyname('名称').asstring;
ADOQuery1.FieldByName('UserCode').asstring:=adoquery2.fieldbyname('编码').asstring;
ADOQuery1.FieldByName('abbrName').asstring:=adoquery2.fieldbyname('简码').asstring;
ADOQuery1.FieldByName('Comment').asstring:=adoquery2.fieldbyname('备注').asstring;
next;
end;
if AdoQuery1.state in [dsinsert,dsEdit] then ADOQuery1.Post;
ADOQuery2.First;
with Adoquery2 do while not eof do
begin
if CoCreateGUID(TmpGUID) = S_OK then
sGUID := GUIDToString(TmpGUID) ;
ADOQuery3.Append;
ADOQuery3.FieldByName('BID').asstring:=sguid;
ADOQuery3.FieldByName('TaxNumber').asstring:=ADOQuery2.fieldbyname('税号').asstring;
ADOQuery3.FieldByName('Bank').asstring:=ADOQuery2.fieldbyname('银行账号').asstring;
next;
end;
if AdoQuery3.state in [dsinsert,dsEdit] then ADOQuery3.Post;
end;
begin
with ADOQuery2 do
begin
ADOQuery2.SQL.clear;
ADOQuery2.SQL.add('select * from [sheet1$]');
ADOQuery2.Open;
ADOQuery2.First; end;
然后我按你的方法在你说的地方添加了,结果程序报错了,报错的内容是ADOQuery2未发现银行账号,
ADOQuery3.FieldByName('Bank').asstring:=ADOQuery2.fieldbyname('银行账号').asstring;
next;
end;
这个END我做了个断点测试,这里报错了,你看看怎么回事