有必要用到游标嘛? 直接用ADO.可以取出EXCEL中的数据 with qrySel do begin ConnectionString :='Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '+ 'e:\test.xls' + ';Extended Properties=Excel 8.0;'; Close; SQL.Clear; sql.Add('SELECT * FROM [Sheet1$]'); try Open; except end; end;
这是取出数据,但如何插入到SQL中去呢?而且一次要插入多条,这个怎 么做呀?
A. 使用简单游标和语法 打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标,对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。DECLARE authors_cursor CURSOR FOR SELECT * FROM authors OPEN authors_cursor FETCH NEXT FROM authors_cursorB. 使用嵌套游标生成报表输出 下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。SET NOCOUNT ONDECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40), @message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lname FROM authors WHERE state = "UT" ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Books by Author: " + @au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based -- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t WHERE ta.title_id = t.title_id AND ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0 PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @message = " " + @title PRINT @message FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor DEALLOCATE titles_cursor
-- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname ENDCLOSE authors_cursor DEALLOCATE authors_cursor GO-------- Utah Authors report --------
----- Books by Author: Anne Ringer The Gourmet Microwave Is Anger the Enemy?
----- Books by Author: Albert Ringer Is Anger the Enemy? Life Without Fear
qxj(做人要厚道^-^) 的应该可以的。我意思他的方法。
qxj(做人要厚道^-^) 的应该可以的。我意思他的方法。
做个循环了, while not qrySel.eof then begin //在这写入插入语句. end;
还是没有解决,其实问题是这样的,我想把EXCEL中的数据插入到一个SQLSERVER表中,但有个要求,就是一次要把EXCEL表中编号为1的数据(有多条)的相关列插入到SQLSERVER中对应列里面,同时要几个变量中记录号也插入到SQLSERVER中,记录号对于插入的每条数据是一样的。如何实现这些呢,我用了qxj(做人要厚道^-^) 的办法,但发现在写插入语句的时候从EXCEL中取出的数据不知道怎么用变量表示,请问用什么办法,可以解决呢? 例如,我从EXCEL中取出了三条记录 A B C a1 b1 c1 a2 b2 c2 a3 b3 c3 插入到SQLSERVER中后要求这样 A B C D E #1 a1 b1 c1 2004 #1 a2 b2 c2 2004 #1 a3 b3 c3 2004 #1 和2004是两个变量,请问大家这个语句应该怎 么写?
Excel中的A为qrysel.Fields[0].AsString B为qrysel.Fields[1].AsString C为qrysel.Fields[2].AsString //循环中内容为: with qryInsert do beign close; sql.clear; sql.add('insert into Table1(a,b,c,d,e) values(:a,:b,:c,:d,:e)'); Parameters.ParamByName('a').Value :='#1'; Parameters.ParamByName('a').Value :=qrysel.Fields[0].AsString Parameters.ParamByName('a').Value :=qrysel.Fields[1].AsString Parameters.ParamByName('a').Value :=qrysel.Fields[2].AsString Parameters.ParamByName('a').Value :='2004' try execsql; except end; end;
我按你的方法写了如下一段代码,可执行后其循环不能结束,我试了一下查询结果的行数是4行,为什么不能结束呢,还有其中有一列的数据Parameters.ParamByName('a8').Value :=datamodule2.bhquery.Fields[4].AsString;这一列在EXCEL表中他的数值是不同的,但写进去后,所有的都是第一行的数值,难道其不是一行一行读进去的吗?麻烦帮着看看!在这儿先谢谢了! datamodule2.bhQuery.Close; datamodule2.bhQuery.SQL.Clear; datamodule2.bhQuery.SQL.Text:='select * from [ddnr$] where 订单号='+n+''; datamodule2.bhQuery.Active:=true; k:=datamodule2.bhQuery.RecordCount;//查询结果行数 showmessage(inttostr(k)); c:=1; while not datamodule2.bhQuery.Eof do begin with datamodule2.Insertbsquery do begin close; sql.clear; SQL.Text:='insert into tf_pos (os_id,os_no,itm,prd_no,prd_name,prd_,unit,qty,up,tax_rto,est_dd) values (:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11)'; Parameters.ParamByName('a1').Value :='SO'; Parameters.ParamByName('a2').Value :=ddh; Parameters.ParamByName('a3').Value :=c; Parameters.ParamByName('a4').Value :=datamodule2.bhquery.Fields[0].AsString; Parameters.ParamByName('a5').Value :=datamodule2.bhquery.Fields[1].AsString; Parameters.ParamByName('a6').Value :=datamodule2.bhquery.Fields[2].AsString; Parameters.ParamByName('a7').Value :=datamodule2.bhquery.Fields[3].AsString; Parameters.ParamByName('a8').Value :=datamodule2.bhquery.Fields[4].AsString; Parameters.ParamByName('a9').Value :=price; Parameters.ParamByName('a10').Value:=17; Parameters.ParamByName('a11').Value:=datamodule2.bhquery.Fields[5].AsString; try execsql; c:=c+1; except end; end;
你少了一名NEXT; while not datamodule2.bhQuery.Eof do begin //插入表 NEXT;//记录下移,不然你就是死循环,且记录永远为第一个 END;
直接用ADO.可以取出EXCEL中的数据
with qrySel do
begin
ConnectionString :='Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '+ 'e:\test.xls' + ';Extended Properties=Excel 8.0;';
Close;
SQL.Clear;
sql.Add('SELECT * FROM [Sheet1$]');
try
Open;
except
end;
end;
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标,对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursorB. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。SET NOCOUNT ONDECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based
-- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GO-------- Utah Authors report --------
----- Books by Author: Anne Ringer
The Gourmet Microwave
Is Anger the Enemy?
----- Books by Author: Albert Ringer
Is Anger the Enemy?
Life Without Fear
while not qrySel.eof then
begin
//在这写入插入语句.
end;
例如,我从EXCEL中取出了三条记录
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
插入到SQLSERVER中后要求这样
A B C D E
#1 a1 b1 c1 2004
#1 a2 b2 c2 2004
#1 a3 b3 c3 2004
#1 和2004是两个变量,请问大家这个语句应该怎 么写?
B为qrysel.Fields[1].AsString
C为qrysel.Fields[2].AsString
//循环中内容为:
with qryInsert do
beign
close;
sql.clear;
sql.add('insert into Table1(a,b,c,d,e) values(:a,:b,:c,:d,:e)');
Parameters.ParamByName('a').Value :='#1';
Parameters.ParamByName('a').Value :=qrysel.Fields[0].AsString
Parameters.ParamByName('a').Value :=qrysel.Fields[1].AsString
Parameters.ParamByName('a').Value :=qrysel.Fields[2].AsString
Parameters.ParamByName('a').Value :='2004'
try
execsql;
except
end;
end;
datamodule2.bhQuery.Close;
datamodule2.bhQuery.SQL.Clear;
datamodule2.bhQuery.SQL.Text:='select * from [ddnr$] where 订单号='+n+'';
datamodule2.bhQuery.Active:=true;
k:=datamodule2.bhQuery.RecordCount;//查询结果行数
showmessage(inttostr(k));
c:=1;
while not datamodule2.bhQuery.Eof do
begin
with datamodule2.Insertbsquery do
begin
close;
sql.clear;
SQL.Text:='insert into tf_pos (os_id,os_no,itm,prd_no,prd_name,prd_,unit,qty,up,tax_rto,est_dd) values (:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11)';
Parameters.ParamByName('a1').Value :='SO';
Parameters.ParamByName('a2').Value :=ddh;
Parameters.ParamByName('a3').Value :=c;
Parameters.ParamByName('a4').Value :=datamodule2.bhquery.Fields[0].AsString;
Parameters.ParamByName('a5').Value :=datamodule2.bhquery.Fields[1].AsString;
Parameters.ParamByName('a6').Value :=datamodule2.bhquery.Fields[2].AsString;
Parameters.ParamByName('a7').Value :=datamodule2.bhquery.Fields[3].AsString;
Parameters.ParamByName('a8').Value :=datamodule2.bhquery.Fields[4].AsString;
Parameters.ParamByName('a9').Value :=price;
Parameters.ParamByName('a10').Value:=17;
Parameters.ParamByName('a11').Value:=datamodule2.bhquery.Fields[5].AsString;
try
execsql;
c:=c+1;
except
end;
end;
while not datamodule2.bhQuery.Eof do
begin
//插入表
NEXT;//记录下移,不然你就是死循环,且记录永远为第一个
END;