我有一个Button的事件如下:
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('if not object_id(''tb1'') is null drop table tb1');
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('Create table tb1([TrnspName] nvarchar(3),[U_bumen] nvarchar(3),[LineTotal] int)');
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add(adoquery3.SQL.Text);
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('select tb1.*,百分比=cast(cast(1.0*LineTotal /(select sum(LineTotal )from tb1)*100 as dec(6,2)) as varchar)+''%'' from tb1') ;
adoquery1.Open ;其中adoquery3.SQL.Text的语句如下:
Insert tb1
select #a.TrnspName,#a.U_bumen,sum(#a.LineTotal) as LineTotal from (
select a.TrnspName,d.U_bumen,sum(b.LineTotal) as LineTotal from OSHP a join OITM c on a.TrnspCode=c.ShipType
join INV1 b on b.ItemCode=c.ItemCode join OINV d on b.DocEntry=d.DocEntry
--where d.DocDate between :bd and :ed group by a.TrnspName,d.U_bumen
where d.DocDate between '2009-1-1' and '2009-2-28' group by a.TrnspName,d.U_bumen
union all
select a1.TrnspName,d1.U_bumen,sum(-b1.LineTotal) as LineTotal from OSHP a1 join OITM c1 on a1.TrnspCode=c1.ShipType
join RIN1 b1 on b1.ItemCode=c1.ItemCode join ORIN d1 on b1.DocEntry=d1.DocEntry
--where d1.DocDate between :bd and :ed group by a1.TrnspName,d1.U_bumen ) #a
where d1.DocDate between '2009-1-1' and '2009-2-28' group by a1.TrnspName,d1.U_bumen ) #a
group by #a.TrnspName,#a.U_bumen
having #a.TrnspName<>'赠品材料' and #a.U_bumen='商场部'adoquery3.SQL.Text的语句是没错的,但是单击这个事件执行后出错,错误为:不正常地定义参数对象,提供了不一致或不完整的信息.请问哪里错了?
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('if not object_id(''tb1'') is null drop table tb1');
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('Create table tb1([TrnspName] nvarchar(3),[U_bumen] nvarchar(3),[LineTotal] int)');
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add(adoquery3.SQL.Text);
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('select tb1.*,百分比=cast(cast(1.0*LineTotal /(select sum(LineTotal )from tb1)*100 as dec(6,2)) as varchar)+''%'' from tb1') ;
adoquery1.Open ;其中adoquery3.SQL.Text的语句如下:
Insert tb1
select #a.TrnspName,#a.U_bumen,sum(#a.LineTotal) as LineTotal from (
select a.TrnspName,d.U_bumen,sum(b.LineTotal) as LineTotal from OSHP a join OITM c on a.TrnspCode=c.ShipType
join INV1 b on b.ItemCode=c.ItemCode join OINV d on b.DocEntry=d.DocEntry
--where d.DocDate between :bd and :ed group by a.TrnspName,d.U_bumen
where d.DocDate between '2009-1-1' and '2009-2-28' group by a.TrnspName,d.U_bumen
union all
select a1.TrnspName,d1.U_bumen,sum(-b1.LineTotal) as LineTotal from OSHP a1 join OITM c1 on a1.TrnspCode=c1.ShipType
join RIN1 b1 on b1.ItemCode=c1.ItemCode join ORIN d1 on b1.DocEntry=d1.DocEntry
--where d1.DocDate between :bd and :ed group by a1.TrnspName,d1.U_bumen ) #a
where d1.DocDate between '2009-1-1' and '2009-2-28' group by a1.TrnspName,d1.U_bumen ) #a
group by #a.TrnspName,#a.U_bumen
having #a.TrnspName<>'赠品材料' and #a.U_bumen='商场部'adoquery3.SQL.Text的语句是没错的,但是单击这个事件执行后出错,错误为:不正常地定义参数对象,提供了不一致或不完整的信息.请问哪里错了?
adoquery1.SQL.Clear ;
adoquery1.SQL.Add(sql1);
adoquery1.SQL.Add(sql2);
...
adoquery1.ExecSQL ;
SQLStr := SQLStr + 'insert Table1 (id, column_1) values (1, 1)'然后一次执行即可。
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('if not object_id(''tb1'') is null drop table tb1');
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('Create table tb1([TrnspName] nvarchar(3),[U_bumen] nvarchar(3),[LineTotal] int)');
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.text := adoquery3.SQL.Text ; //不要分行。
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('select tb1.*,百分比=cast(cast(1.0*LineTotal /(select sum(LineTotal )from tb1)*100 as dec(6,2)) as varchar)+''%'' from tb1') ;
adoquery1.Open ;
----------------------------------------------------------------
type
TDbAccess = class private
{ Private declarations }
public
function ExecSQL(ADOQuery:TADOQuery;SqlStr:String):TDataset; end;implementationfunction TDbAccess.ExecSQL(ADOQuery:TADOQuery;SqlStr:String):TDataset;
begin
with ADOQuery do
begin
Close;
SQL.Clear;
SQL.Add(SqlStr);
Open;
end;
Result:=ADOQuery;
end;以后需要用ADOQuery执行SQL命令时,就创建创建TDbAccess对象并调用ExecSQL方法。
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('delete from tb1');
adoquery1.SQL.Add(adoquery3.SQL.Text);
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('select tb1.*,百分比=cast(cast(1.0*LineTotal /(select sum(LineTotal )from tb1)*100 as dec(6,2)) as varchar)+''%'' from tb1') ;
adoquery1.Open ;我这样也出错,在第6行出错,错误原因同上.
select #a.TrnspName,#a.U_bumen,sum(#a.LineTotal) as LineTotal from (
select a.TrnspName,d.U_bumen,sum(b.LineTotal) as LineTotal from OSHP a join OITM c on a.TrnspCode=c.ShipType
join INV1 b on b.ItemCode=c.ItemCode join OINV d on b.DocEntry=d.DocEntry
--where d.DocDate between :bd and :ed group by a.TrnspName,d.U_bumen
where d.DocDate between '2009-1-1' and '2009-2-28' group by a.TrnspName,d.U_bumen
union all
select a1.TrnspName,d1.U_bumen,sum(-b1.LineTotal) as LineTotal from OSHP a1 join OITM c1 on a1.TrnspCode=c1.ShipType
join RIN1 b1 on b1.ItemCode=c1.ItemCode join ORIN d1 on b1.DocEntry=d1.DocEntry
--where d1.DocDate between :bd and :ed group by a1.TrnspName,d1.U_bumen ) #a
where d1.DocDate between '2009-1-1' and '2009-2-28' group by a1.TrnspName,d1.U_bumen ) #a
group by #a.TrnspName,#a.U_bumen
having #a.TrnspName <>'赠品材料' and #a.U_bumen='商场部'
这条语句能执行吗?
adoquery1.SQL.Add(adoquery3.SQL.Text); // 在執行前,你調試下看下:adoquery1.SQL.text 裏是什麼。是不是由於換行的一些其它原因,導致你帶參數的那部分注釋代碼有問題了。
adoquery1.ExecSQL ;
Insert tb1
select #a.TrnspName,#a.U_bumen,sum(#a.LineTotal) as LineTotal from (
select a.TrnspName,d.U_bumen,sum(b.LineTotal) as LineTotal from OSHP a join OITM c on a.TrnspCode=c.ShipType
join INV1 b on b.ItemCode=c.ItemCode join OINV d on b.DocEntry=d.DocEntry
--where d.DocDate between :bd and :ed group by a.TrnspName,d.U_bumen
where d.DocDate between '2009-1-1' and '2009-2-28' group by a.TrnspName,d.U_bumen
union all
select a1.TrnspName,d1.U_bumen,sum(-b1.LineTotal) as LineTotal from OSHP a1 join OITM c1 on a1.TrnspCode=c1.ShipType
join RIN1 b1 on b1.ItemCode=c1.ItemCode join ORIN d1 on b1.DocEntry=d1.DocEntry
--where d1.DocDate between :bd and :ed group by a1.TrnspName,d1.U_bumen ) #a
where d1.DocDate between '2009-1-1' and '2009-2-28' group by a1.TrnspName,d1.U_bumen ) #a
group by #a.TrnspName,#a.U_bumen
having #a.TrnspName <>'赠品材料' and #a.U_bumen='商场部' 以上语句在SQL2000中执行没问题的.
我在上面已經跟你說過很多次了吧??
或者直接:adoquery1.SQL.text := adoquery3.SQL.Text ;
哦,我明白了,是sql语句中不认这样的注释。将
--where d.DocDate between :bd and :ed group by a.TrnspName,d.U_bumen
和
--where d1.DocDate between :bd and :ed group by a1.TrnspName,d1.U_bumen ) #a
两条语句从sql文件中删除掉。之所以sql中能执行是因为sql查询管理器中允许注释。但在delphi中却不认这样的注释,所以出错。
如果sql文件中要保留注释,则在加载sql文件时务必将注释过滤掉
var
bd,ed:Tdatetime;
begin
bd:=dtp1.date; //dtp1是DateTimePicker1控件
ed:=dtp2.date;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('delete from tb');
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add(adoquery3.SQL.Text);
adoquery1.ExecSQL ;
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add('select tb.*,百分比=cast(cast(1.0*LineTotal /(select sum(LineTotal )from tb)*100 as dec(6,2)) as varchar)+''%'' from tb') ;
adoquery1.Open ;
end;adoquery3.sql.text的语句如下:
Insert tb
select #a.TrnspName,#a.U_bumen,sum(#a.LineTotal) as LineTotal from (
select a.TrnspName,d.U_bumen,sum(b.LineTotal) as LineTotal from OSHP a join OITM c on a.TrnspCode=c.ShipType
join INV1 b on b.ItemCode=c.ItemCode join OINV d on b.DocEntry=d.DocEntry
where d.DocDate between :bd and :ed group by a.TrnspName,d.U_bumen
union all
select a1.TrnspName,d1.U_bumen,sum(-b1.LineTotal) as LineTotal from OSHP a1 join OITM c1 on a1.TrnspCode=c1.ShipType
join RIN1 b1 on b1.ItemCode=c1.ItemCode join ORIN d1 on b1.DocEntry=d1.DocEntry
where d1.DocDate between :bd and :ed group by a1.TrnspName,d1.U_bumen ) #a
group by #a.TrnspName,#a.U_bumen
having #a.TrnspName<>'赠品材料' and #a.U_bumen='商场部'看看哪里错了.adoquery3.sql.text语句其它的不用考虑,只考虑:bd和:ed就行了,因为我用'2009-1-1'代替:bd,用'2009-2-28'代替:ed是通过的.
另外,书写代码的规范也要注意哦
1.两个参数,应该用两个bd和两个ed
2.SQL里的注释用--来表示,但放在delphi里是出错的.
谢谢大家关注!