关于调用存储过程的问题,请各位高手指点一二!!!
一、程序功能:
1、利用存储过程将一张表内数据按不同条件汇总,并插入到一临时表中;
2、在VB中将存储过程中的临时表数据,调入到Recordset记录集
二、问题:
在运行到 Set adors1 = cntcmd.Execute 时出现实时错误 3704,“对象关闭时,操作不被允许”。
三、程序清单:
1、存储过程:
create proc t_colour
as
begin
/*创建临时表,用于存储返回结果集*/
create table #tmp
(
tmp_ballno CHAR(2),
tmp_redcnt integer,
tmp_bluecnt integer,
tmp_happycnt integer
)
declare @redcnt integer,
@bluecnt integer,
@happycnt integer
DECLARE @i INTEGER,
@cnt integer,
@iChar char(2)
select @i = 1
WHILE @I <= 30 /*汇总01至30的数据*/
BEGIN
IF @i < 10
select @iChar = '0' + ltrim(rtrim(str(@i)))
else
select @iChar = ltrim(rtrim(str(@i)))
SELECT @redcnt = count(*) from t_ball where redball like '%' @iChar + '%'
SELECT @bluecnt = count(*) from t_ball where blueball = @i
SELECT @happycnt = count(*) from t_ball where happyball = @i
insert into #tmp(tmp_ballno, tmp_redcnt, tmp_bluecnt, tmp_happycnt)
values (@iChar, @redcnt, @bluecnt, @happycnt)
select @i = @i + 1
END
select * from #tmp
end
2、vb中的程序
Private Sub Command1_Click()
Dim i As Integer
Dim cntcmd As ADODB.Command
Dim adors1 As ADODB.Recordset
Set cntcmd = New ADODB.Command
With cntcmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "t_colour"
End With
Set adors1 = New ADODB.Recordset
Set adors1 = cntcmd.Execute
End Sub