ASP与存储过程
问题描述：
1。我在存储过程中，用参数代替，可以执行出查询结果
2。我在ASP中执行，没有结果显示，rs_buy.recordcout =-1,conn.state=1
第一次使用这种方法，请教了
ASP的代码如下
dim cmd,rs_buy
Const adCmdStoredProc=4
Set cmd=Server.CreateObject("ADODB.Command")
Set rs_buy=Server.CreateObject("ADODB.Recordset")
With cmd
.ActiveConnection=conn
.CommandText ="a_2012hu_wwworder_list"
.CommandType =4
.Prepared =True
.Parameters.Append .CreateParameter("Fyear",20,1,8,getn)
.Parameters.Append .CreateParameter("Fmonth",20,1,5,gety)
.Parameters.Append .CreateParameter("Fuserid",20,1,20,0)
.Parameters.Append .CreateParameter("Fxsb",20,1,15,getdep)
.Parameters.Append .CreateParameter("Fbsc",20,1,15,getbsc)
.Parameters.Append .CreateParameter("Fcust",20,1,15,nowzu)
Set rs_buy=.Execute
End With
存储过程的代码如下：
ALTER proc [dbo].[a_2012hu_wwworder_list]
@Fyear int,
@Fmonth int,
@Fuserid int,
@Fxsb int,
@Fbsc int,
@Fcustid int
as
SET NOCOUNT ON
--declare @Fyear int
--declare @Fmonth int
--declare @Fuserid int
--declare @Fxsb int
--declare @Fbsc int
--declare @Fcustid int
--set @Fyear=2012
--set @Fmonth=7
--set @Fuserid=0
--set @Fxsb=0
--set @Fbsc=45102
--set @Fcustid=0
CREATE TABLE #tkhb(
[Fcustid] [int] NOT NULL,
[Fname] [varchar](80) NULL,
[F_111] [int] NULL,
[Fbsc] [varchar](20) NOT NULL,
[Fxsb] [varchar](20) NOT NULL,
[F_112] [int] NULL
) ON [PRIMARY]
CREATE TABLE #khb(
[Fcustid] [int] NOT NULL,
[Fname] [varchar](80) NULL,
[F_111] [int] NULL,
[Fbsc] [varchar](20) NOT NULL,
[Fxsb] [varchar](20) NOT NULL,
[F_112] [int] NULL
) ON [PRIMARY]
if @Fcustid=0
-----------------------------------------------
--客户不确定
begin
if @Fuserid=0
----------------------------------------------------
---BI用户
begin
insert into #tkhb
(Fcustid,a.Fname,F_111,Fbsc,Fxsb,F_112)
select a.Fcustid,c.Fname,c.F_111,d.Fname,e.Fname,F_112
from a_2012hu_wwwkehuentry a
inner join a_2012hu_wwwkehu b on a.fid=b.fid
inner join t_organization c on a.Fcustid=c.Fitemid
inner join t_item d on c.F_111=d.Fitemid
inner join t_item e on c.F_112=e.Fitemid
end
----------------------------------------------------
else
----------------------------------------------------
----非BI用户
begin
insert into #tkhb
(Fcustid,a.Fname,F_111,Fbsc,Fxsb,F_112)
select a.Fcustid,c.Fname,c.F_111,d.Fname,e.Fname,F_112
from a_2012hu_wwwkehuentry a
inner join a_2012hu_wwwkehu b on a.fid=b.fid
inner join t_organization c on a.Fcustid=c.Fitemid
inner join t_item d on c.F_111=d.Fitemid
inner join t_item e on c.F_112=e.Fitemid
where b.Fuserid=@Fuserid
end
----------------------------------------------------
end
----------------------------------------------------
else
----------------------------------------------------
--客户ID不等0
begin
insert into #tkhb
(Fcustid,a.Fname,F_111,Fbsc,Fxsb,F_112)
select
a.Fitemid,a.Fname,F_111,b.Fname,c.Fname,F_112
from t_organization a
inner join t_item b on a.F_111=b.Fitemid
inner join t_item c on a.F_112=c.Fitemid
where a.Fitemid=@Fcustid
end
---------------------------------------------------
if @Fxsb=0 and @Fbsc=0
begin
insert into #khb(Fcustid,Fname,F_111,Fbsc,Fxsb,F_112)
select Fcustid,Fname,F_111,Fbsc,Fxsb,F_112 from #tkhb
end
else
begin
if @Fxsb<>0 and @Fbsc=0
begin
insert into #khb(Fcustid,Fname,F_111,Fbsc,Fxsb,F_112)
select Fcustid,Fname,F_111,Fbsc,Fxsb,F_112 from #tkhb where F_112=@Fxsb
end
if @Fbsc<>0
begin
insert into #khb(Fcustid,Fname,F_111,Fbsc,Fxsb,F_112)
select Fcustid,Fname,F_111,Fbsc,Fxsb,F_112 from #tkhb where F_111=@Fbsc
end
end
select a.Fbillno,a.Fcust,a.Fdate,a.Fdate2,b.Fordersl
into #order
from(select Fid,Fbillno,Fcust,Fdate,Fdate2 from t_BOS_SaleOrder
where year(Fdate)=@Fyear and month(Fdate)=@Fmonth) a
inner join
(select Fid,sum(Fqty) as Fordersl from t_BOS_SaleOrderentry where Ftype=1 group by Fid) b on a.fid=b.fid
order by a.Fdate desc
select distinct a.Fcustid,a.Fbillno as Ffhd,a.Fskno,b.Fbillno as Fskd,a.Fcashid,a.Fsaleorderno,b.FlastAmount
into #fhd
from (select* from t_sale where Fcustid in(select Fcustid from #khb)
and year(Fdate)=@Fyear and month(Fdate)>=@Fmonth) a
left join xsh_cash b on a.Fcashid=b.Fid
select distinct a.Fsaleorderno as Forder,sum(a.Fhqty) as Fysdsl
into #Fysd
from (select Fsaleorderno,Fhqty from xsh_transport
where Fcustid in(select Fcustid from #khb)
and year(Fdate)=@Fyear and month(Fdate)>=@Fmonth) a
where Fsaleorderno<>''group by Fsaleorderno
select sum(b.Fauxqty) as Fcksl,c.Forderno
into #Fckd
from (
select distinct finterid from icstockbill where Fsupplyid in (select Fcustid from #khb)
and year(Fdate)=@Fyear and month(Fdate)>=@Fmonth and Ftrantype='21'
) a inner join (select Finterid,Fauxqty,Fentryselfb0175
from icstockbillentry where Fentryselfb0175 is not null)
b on a.finterid=b.finterid
inner join (select distinct Fbillno,Fsaleorderno as Forderno
from (select * from t_sale where Fcustid in (select Fcustid from #khb)
and year(Fdate)=@Fyear and month(Fdate)>=@Fmonth) a
where Fcustid in(select Fcustid from #khb)
) c on b.Fentryselfb0175=c.Fbillno
group by Forderno
if @Fxsb=0
--------------------------------------
--销售部不定
select a.Fcustid,Fname,F_111,Fbsc,Fxsb,F_112,
b.Fdate,b.Fdate2 as Fneeddate,b.Fbillno as Fordno,b.Fordersl,
c.Fskno,Flastamount as Fqk,
d.Fysdsl,e.Fcksl
from #khb a
inner join #order b on a.Fcustid=b.Fcust
left join #Fhd c on b.Fbillno=c.Fsaleorderno
left join #Fysd d on b.Fbillno=d.Forder
left join #Fckd e on b.Fbillno=e.Forderno
order by b.Fdate desc
drop table #tkhb
drop table #khb
drop table #order
drop table #fhd
drop table #Fysd
drop table #Fckd
[解决办法]
