在VB中怎么调用写好的SQL语句?

renjie45 2008-11-03 11:56:10

我的sql语句保存在一个文本文件中,内容

SET NOCOUNT Off

 Create Table #jzc_DATA2 (

     FItemID int null,

     Fnumber varchar(255) null,

     Fname varchar(255) null,

     Fmodel varchar(255) null,

     Funitid int null,

     Funitname varchar(255) null,

     FCustID int null,

     Fcustname varchar(255) null,

     FYWYID  int null, 

     FYWYname varchar(255) null,

     FOutQty float null,

     FOutAmount Decimal(28,2) null) 

Insert Into #jzc_DATA2 (FItemID,FCustID,FYWYID,FOutQty,FOutAmount) 

Select v2.FItemID,v1.FSupplyID,v1.FEmpid,sum(v2.FQty),sum(v2.FconsignAmount) 

From ICStockBill v1,ICStockBillEntry v2 

Where v1.FTranType =21   And v1.FCancelLation=0  and v1.FInterID = v2.FInterID

AND v1.FDate >='2008-09-01'  AND v1.FDate <='2008-09-30'  

group by v1.Fempid,v1.fSupplyid,v2.fitemid



update t1 set t1.fnumber=t2.fnumber,t1.fname=t2.fname,t1.fmodel=t2.fmodel,t1.funitid=t2.funitid  from #jzc_DATA2 t1,t_icitem t2 where t1.fitemid=t2.fitemid



update t1 set t1.fcustname=t2.fname from #jzc_DATA2 t1,t_Organization t2 where t1.fcustid=t2.FItemID 



update t1 set t1.fywyname=t2.fname from #jzc_DATA2 t1,t_emp t2 where t1.fywyid=t2.FItemID 



update t1 set t1.funitname=t2.fname from #jzc_DATA2 t1,t_unitgroup t2 where t1.Funitid=t2.Fdefaultunitid 

Insert Into #jzc_DATA2 

Select 200000+fywyid,'业务员小计','','','','',100000,'',Fywyid,'',sum(FoutQty),sum(FoutAmount) 

From #jzc_DATA2 Group By Fywyid  --业务员合计



Insert Into #jzc_DATA2 

Select 100000+fcustid,'客户小计','','','','',fcustid,'',Fywyid,'',sum(FoutQty),sum(FoutAmount) 

From #jzc_DATA2  where fcustid<>100000 Group By Fywyid,fcustid  --在业务员下分客户小计



--delete #jzc_DATA2 where fnumber='客户小计' and 



Insert Into #jzc_DATA2 

Select 300000,'总计','','','','',299999,'',199999,'',sum(FoutQty),sum(FoutAmount) 

From #jzc_DATA2    --总计



update t1 set t1.fnumber=t2.Fname+'业绩小计'  from #jzc_DATA2 t1,t_emp t2 where t1.Fywyid=t2.fitemid and t1.fnumber='业务员小计'

update t1 set t1.fnumber=t2.Fname+'销售小计'  from #jzc_DATA2 t1,t_Organization t2 where  t1.FcustID=t2.FItemID and t1.fnumber='客户小计'



select Fywyname as '业务员',Fcustname AS '客户',Fnumber as '商品编码',Fname AS '商品名称',Fmodel AS '规格型号',Funitname AS '单位',FOutQty AS '数量',FOutAMOUNT AS '金额'

 from #jzc_DATA2 order by Fywyid,Fcustid,Fitemid 

--select * from  #jzc_data2 order by fywyid,Fcustid,Fitemid

DROP TABLE #jzc_DATA2  

--delete #jzc_DATA2

--select * from icstockbill where ftrantype=21 order by fEMPid and  fEMPid=0   --43499



delete t2 from icstockbill t1,icstockbillentry t2 

 where t1.finterid=t2.finterid and t1.ftrantype=1 and t2.finterid<=9322 and t2.finterid>=7435

打开icstockbillentry 粘贴EXECL中的sheet3表

select * from icstockbill