ADO用使用存储过程老说我纪录集关闭,不知道什么原因 ? 十万火急

火鸟33 2003-12-03 10:59:59
Private Sub BOK_Click()
Dim conn As ADODB.Connection
DBServer = "127.0.0.1"
DBName = "tollsys_s"
DBUser = "sa"
DBPwd = "searitollsys"
DBTimeOut = 60




Set conn = New ADODB.Connection
conn.ConnectionTimeout = DBTimeOut
conn.ConnectionString = "Provider=SQLOLEDB.1;Password=" + DBPwd + ";Persist Security Info=True;User ID=" + DBUser + ";Initial Catalog=" + DBName + ";Data Source=" + DBServer + ""
conn.Open
Dim ChargeRD As ADODB.Command
Dim rs As ADODB.Recordset

StoredProcedure = "[dbo].[substation_print_charge]"
Set ChargeRD = New ADODB.Command
Set rs = New ADODB.Recordset

'用这段和用参数调用存储过程一样,也说纪录集关闭
'rs.Open "exec [dbo].[substation_print_charge] ""12 1 2002 12:00:00:000AM"", ""12 1 2002 12:00:00:000AM"", ""1F010000000009010001000006"", 2", conn, , adCmdStoredProc

'用这段代码,又没有问题,一切正常
'rs.Open "select * from ExitRecordDayTemp", conn, , adCmdText


''
Set ChargeRD.ActiveConnection = conn
Set rs.ActiveConnection = conn

ChargeRD.CommandText = StoredProcedure

' always use ADO constants
ChargeRD.CommandType = adCmdStoredProc

' Check into the NamedParameters property at some point
' It doesn't require the order to be enforced, but it is always a good idea to enforce it anyway (for the documentation aspect of coding)

' RETURN parameter needs to be first
ChargeRD.Parameters.Append ChargeRD.CreateParameter("RETURN", adInteger, adParamReturnValue, 1)

ChargeRD.Parameters.Append ChargeRD.CreateParameter("@TDBegin", adDate, adParamInput, 8, "2002-12-01")
ChargeRD.Parameters.Append ChargeRD.CreateParameter("@TDEnd", adDate, adParamInput, 8, "2002-12-01")
ChargeRD.Parameters.Append ChargeRD.CreateParameter("@NodeID", adChar, adParamInput, 26, "1F010000000009010001000006")
ChargeRD.Parameters.Append ChargeRD.CreateParameter("@Type ", adTinyInt, adParamInput, 1, 2)


Set rs = ChargeRD.Execute

'rs.Open

'下面老说我纪录集关闭,不知道什么原因
rs.MoveFirst

While Not rs.EOF

ChargeReport.Range("A1").Select
'ÒÆ¶¯µ½´°Ìå×îµ×²¿
Selection.End(xlDown).Select
'ÒÆ¶¯µ½´°Ìå×î×ó±ß
Selection.End(xlToLeft).Select

'½«´°ÌåµÄÊäÈëÖµ±£´æµ½´°Ìå×îºóн¨µÄÒ»Ìõ
ActiveCell.Offset(1, 0).Range("A1") = rs.Fields(1)
ActiveCell.Offset(1, 0).Range("B1") = rs.Fields(2)
ActiveCell.Offset(1, 0).Range("C1") = rs.Fields(3)
ActiveCell.Offset(1, 0).Range("D1") = rs.Fields(4)
ActiveCell.Offset(1, 0).Range("E1") = rs.Fields(5)
ActiveCell.Offset(1, 0).Range("F1") = rs.Fields(6)

rs.MoveNext


Wend

rs.Clone
conn.Close





End Sub
...全文
87 16 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
火鸟33 2003-12-03
  • 打赏
  • 举报
回复
还是不行的
火鸟33 2003-12-03
  • 打赏
  • 举报
回复
/*处理月报 */
if @Type=2
begin
set @TDBegin=( Cast(DATEPART(year,@TDBegin) as Char(4)) +'-'+Cast(DATEPART(month,@TDBegin) as Char(2)) +'-'+Cast(1 as Char(2)))
insert into tempdb SELECT ( Cast(DATEPART(year, ExStatDate) as Char(4)) +'-'+Cast(DATEPART(month, ExStatDate) as Char(2)) +'-'+Cast(DATEPART(day, ExStatDate) as Char(2))) ,'',
SUM(cast(a.expass as bigint)) AS l3,
SUM(cast(a.expassdebt as bigint)) AS l4,
SUM(cast(a.exloan as bigint)) AS l5,
SUM(cast(a.exloandebt as bigint)) AS l6,
SUM(cast(a.exforfeit as bigint)) AS l7,
SUM(cast(a.exforfeitdebt as bigint)) AS l8,
SUM(cast(a.exrcvdsum as bigint)) AS l9,
SUM(NumTotal) as L10,
SUM(cast(a.MonthCardNum as bigint)) AS L11,
SUM(cast(a.PrintNum as bigint)) AS L12,
SUM(cast(a.PrintCount as bigint)) AS L13,
SUM(cast(a.HandNum as bigint)) AS L14,
SUM(cast(a.HandCount as bigint)) AS L15,
SUM(cast(a.CashCardNum as bigint)) AS L16,
SUM(cast(a.CashCardCount as bigint)) AS L17,
' ',' ',' ',' ','','','',ExStatDate,getdate()
FROM ExitRecordDayTemp a where ExStatDate>=@TDBegin and ExStatDate<DATEADD(mm, 1, @TDBegin) and cast(a.ExNodeID as varchar(16) )= (cast(@NodeID as varchar(16))) group by ( Cast(DATEPART(year,ExStatDate) as Char(4)) +'-'+Cast(DATEPART(month, ExStatDate) as Char(2)) +'-'+Cast(DATEPART(day, ExStatDate) as Char(2))),ExStatDate

insert into tempdb SELECT '合计:' ,'',
SUM(cast(a.expass as bigint)) AS l3,
SUM(cast(a.expassdebt as bigint)) AS l4,
SUM(cast(a.exloan as bigint)) AS l5,
SUM(cast(a.exloandebt as bigint)) AS l6,
SUM(cast(a.exforfeit as bigint)) AS l7,
SUM(cast(a.exforfeitdebt as bigint)) AS l8,
SUM(cast(a.exrcvdsum as bigint)) AS l9,
SUM(NumTotal) as L10,
SUM(cast(a.MonthCardNum as bigint)) AS L11,
SUM(cast(a.PrintNum as bigint)) AS L12,
SUM(cast(a.PrintCount as bigint)) AS L13,
SUM(cast(a.HandNum as bigint)) AS L14,
SUM(cast(a.HandCount as bigint)) AS L15,
SUM(cast(a.CashCardNum as bigint)) AS L16,
SUM(cast(a.CashCardCount as bigint)) AS L17,
' ',' ',' ',' ','','','','9999-99-99',getdate()+10

FROM ExitRecordDayTemp a where ExStatDate>=@TDBegin and ExStatDate<DATEADD(mm, 1, @TDBegin) and cast(a.ExNodeID as varchar(16) )= (cast(@NodeID as varchar(16)))

select * from tempdb order by cast(l25 as binary),LDate
END
/*处理年报 */
if @Type=3
begin
set @TDBegin=Cast(DATEPART(year,@TDBegin) as Char(4)) +'-'+'1' +'-'+'1'
insert into tempdb SELECT ( Cast(DATEPART(year, ExStatDate) as Char(4)) +'-'+Cast(DATEPART(month, ExStatDate) as Char(2)) ) ,'',
SUM(cast(a.expass as bigint)) AS l3,
SUM(cast(a.expassdebt as bigint)) AS l4,
SUM(cast(a.exloan as bigint)) AS l5,
SUM(cast(a.exloandebt as bigint)) AS l6,
SUM(cast(a.exforfeit as bigint)) AS l7,
SUM(cast(a.exforfeitdebt as bigint)) AS l8,
SUM(cast(a.exrcvdsum as bigint)) AS l9,
SUM(NumTotal) as L10,
SUM(cast(a.MonthCardNum as bigint)) AS L11,
SUM(cast(a.PrintNum as bigint)) AS L12,
SUM(cast(a.PrintCount as bigint)) AS L13,
SUM(cast(a.HandNum as bigint)) AS L14,
SUM(cast(a.HandCount as bigint)) AS L15,
SUM(cast(a.CashCardNum as bigint)) AS L16,

SUM(cast(a.CashCardCount as bigint)) AS L17,
' ',' ',' ',' ','','','',( Cast(DATEPART(year, ExStatDate) as Char(4)) +'-'+Cast(DATEPART(month, ExStatDate) as Char(2)) ) ,getdate()

FROM ExitRecordDayTemp a where ExStatDate>=@TDBegin and ExStatDate<DATEADD(year, 1, @TDBegin) and cast(a.ExNodeID as varchar(16) )= (cast(@NodeID as varchar(16))) group by ( Cast(DATEPART(year,ExStatDate) as Char(4)) +'-'+Cast(DATEPART(month, ExStatDate) as Char(2)) )

insert into tempdb SELECT '合计:' ,'',
SUM(cast(a.expass as bigint)) AS l3,
SUM(cast(a.expassdebt as bigint)) AS l4,
SUM(cast(a.exloan as bigint)) AS l5,
SUM(cast(a.exloandebt as bigint)) AS l6,
SUM(cast(a.exforfeit as bigint)) AS l7,
SUM(cast(a.exforfeitdebt as bigint)) AS l8,
SUM(cast(a.exrcvdsum as bigint)) AS l9,
SUM(NumTotal) as L10,
SUM(cast(a.MonthCardNum as bigint)) AS L11,
SUM(cast(a.PrintNum as bigint)) AS L12,
SUM(cast(a.PrintCount as bigint)) AS L13,
SUM(cast(a.HandNum as bigint)) AS L14,
SUM(cast(a.HandCount as bigint)) AS L15,
SUM(cast(a.CashCardNum as bigint)) AS L16,
SUM(cast(a.CashCardCount as bigint)) AS L17,
' ',' ',' ',' ','','','','9999-99',getdate()

FROM ExitRecordDayTemp a where ExStatDate>=@TDBegin and ExStatDate<DATEADD(year, 1, @TDBegin) and cast(a.ExNodeID as varchar(16) )= (cast(@NodeID as varchar(16)))

select * from tempdb order by cast(l25 as binary),LDate
END
return
GO
火鸟33 2003-12-03
  • 打赏
  • 举报
回复
存储过程,比较复杂

CREATE PROCEDURE dbo.substation_print_charge(@TDBegin datetime,@TDEnd datetime,@NodeID varchar(26),@Type int)AS
DECLARE @shiftname varchar(40),@shiftno int, @empname varchar(20),@empid int,@I Int
--declare @t1 varchar(30),@t2 varchar(30),@t3 varchar(30),@t4 varchar(30),@t5 varchar(30),@t6 varchar(30),@t7 varchar(30)
--delete from ReportTemp
delete from TempDb
--处理日报
if @Type=1
begin
set @TDBegin=( Cast(DATEPART(year,@TDBegin) as Char(4)) +'-'+Cast(DATEPART(month,@TDBegin) as Char(2)) +'-'+Cast(DATEPART(dd,@TDBegin) as Char(2)))
set @I=0
DECLARE shiftcursor CURSOR
FOR
select ShiftNo,descr from CurrentShiftArrange where shiftno<>0 order by ShiftNo
OPEN shiftcursor
FETCH NEXT FROM shiftcursor
INTO @shiftno,@shiftname
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
set @I =@I +1
insert into tempdb SELECT @shiftname,b.EmpID,
SUM(cast(a.expass as bigint)) AS l3,
SUM(cast(a.expassdebt as bigint)) AS l4,
SUM(cast(a.exloan as bigint)) AS l5,
SUM(cast(a.exloandebt as bigint)) AS l6,
SUM(cast(a.exforfeit as bigint)) AS l7,
SUM(cast(a.exforfeitdebt as bigint)) AS l8,
SUM(cast(a.exrcvdsum as bigint)) AS l9,
SUM(NumTotal) as L10,
SUM(cast(a.MonthCardNum as bigint)) AS L11,
SUM(cast(a.PrintNum as bigint)) AS L12,
SUM(cast(a.PrintCount as bigint)) AS L13,
SUM(cast(a.HandNum as bigint)) AS L14,
SUM(cast(a.HandCount as bigint)) AS L15,
SUM(cast(a.CashCardNum as bigint)) AS L16,
SUM(cast(a.CashCardCount as bigint)) AS L17,
b.empname,' ',' ',' ','','','',@I*100000+(case b.EmpID when Null then 0 else b.EmpID end) ,Getdate()

FROM ExitRecordDayTemp a,employee b where a.ExShift=@ShiftNo and a.exempid=b.empid and ExStatDate>=@TDBegin and ExStatDate<@TDBegin+1 and cast(a.ExNodeID as varchar(16) )= (cast(@NodeID as varchar(16))) group by b.EmpID,b.empname order by b.EmpID

insert into tempdb SELECT @shiftname,'小计:',
SUM(cast(a.expass as bigint)) AS l3,
SUM(cast(a.expassdebt as bigint)) AS l4,
SUM(cast(a.exloan as bigint)) AS l5,
SUM(cast(a.exloandebt as bigint)) AS l6,
SUM(cast(a.exforfeit as bigint)) AS l7,
SUM(cast(a.exforfeitdebt as bigint)) AS l8,
SUM(cast(a.exrcvdsum as bigint)) AS l9,
SUM(NumTotal) as L10,
SUM(cast(a.MonthCardNum as bigint)) AS L11,
SUM(cast(a.PrintNum as bigint)) AS L12,
SUM(cast(a.PrintCount as bigint)) AS L13,
SUM(cast(a.HandNum as bigint)) AS L14,
SUM(cast(a.HandCount as bigint)) AS L15,
SUM(cast(a.CashCardNum as bigint)) AS L16,
SUM(cast(a.CashCardCount as bigint)) AS L17,
'',' ',' ',' ','','','',@I*200000,Getdate()+1

FROM ExitRecordDayTemp a,employee b where a.ExShift=@ShiftNo and a.exempid=b.empid and ExStatDate>=@TDBegin and ExStatDate<@TDBegin+1 and cast(a.ExNodeID as varchar(16) )= (cast(@NodeID as varchar(16)))
FETCH NEXT FROM shiftcursor
INTO @shiftno,@shiftname

END
close shiftcursor
DEALLOCATE shiftcursor
insert into tempdb SELECT '合计:',' ',
SUM(cast(a.expass as bigint)) AS l3,
SUM(cast(a.expassdebt as bigint)) AS l4,
SUM(cast(a.exloan as bigint)) AS l5,
SUM(cast(a.exloandebt as bigint)) AS l6,
SUM(cast(a.exforfeit as bigint)) AS l7,
SUM(cast(a.exforfeitdebt as bigint)) AS l8,
SUM(cast(a.exrcvdsum as bigint)) AS l9,
SUM(NumTotal) as L10,
SUM(cast(a.MonthCardNum as bigint)) AS L11,
SUM(cast(a.PrintNum as bigint)) AS L12,
SUM(cast(a.PrintCount as bigint)) AS L13,
SUM(cast(a.HandNum as bigint)) AS L14,
SUM(cast(a.HandCount as bigint)) AS L15,
SUM(cast(a.CashCardNum as bigint)) AS L16,
SUM(cast(a.CashCardCount as bigint)) AS L17,
' ',' ',' ',' ','','','',@I*300000,Getdate()+2

FROM ExitRecordDayTemp a where ExStatDate>=@TDBegin and ExStatDate<@TDBegin+1 and cast(a.ExNodeID as varchar(16) )= (cast(@NodeID as varchar(16)))

--返回纪录
select * from tempdb order by L25,Ldate

END
ad4017 2003-12-03
  • 打赏
  • 举报
回复
请注意:是存储过程的问题,把存储过程贴出来看看。
lisong770818 2003-12-03
  • 打赏
  • 举报
回复
你在建源打开前即(conn.open前),把下面这段代码加进去试试。
conn.CursorLocation = adUseClient
火鸟33 2003-12-03
  • 打赏
  • 举报
回复
仔细看代码,只要能使用存储过程返回纪录就行,我都试验过的,一样

Delphi没问题,VBA怎么这么多问题,破领导,非的叫用excel开发报表系统,烦
aalei 2003-12-03
  • 打赏
  • 举报
回复
你不要用COMMAND试试

火鸟33 2003-12-03
  • 打赏
  • 举报
回复
不是这个问题,我用exec 执行存储过程也不行,而且绝对有记录集
火鸟33 2003-12-03
  • 打赏
  • 举报
回复
要怎么做啊?
SoHo_Andy 2003-12-03
  • 打赏
  • 举报
回复
Set rs = ChargeRD.Execute
这个语句返回的是静态的只能前滚的记录集,你将它rs.MoveFirst 是不行的
火鸟33 2003-12-03
  • 打赏
  • 举报
回复
谁解决廖,我发1000分

在线等
火鸟33 2003-12-03
  • 打赏
  • 举报
回复
结帖子

zhangcyu() 300分 你的太麻烦,改动太多
yoki(小马哥) 7000 使用这种方法
yoki 2003-12-03
  • 打赏
  • 举报
回复
在你的存储过程前后分别加上:

set nocount on
.
.
.
.
set nocount off
zhangcyu 2003-12-03
  • 打赏
  • 举报
回复
你在delete from TempDb后加一个go
然后在游标循环后close shiftcursor
DEALLOCATE shiftcursor 再加一个go
在 --返回纪录
select * from tempdb order by L25,Ldate
前加一个go

火鸟33 2003-12-03
  • 打赏
  • 举报
回复
是存储过程问题,因为第一句sql语句不返回纪录集,哎

但是delphi没问题,不只delphi怎么处理的,,

看能设定参数,使纪录能正常返回,不然要改存储过程,就不能兼容老程序,而且改的范围也太大
zhangcyu 2003-12-03
  • 打赏
  • 举报
回复
你的存储过程有问题
因为我把你的存储过程换成其他的都没有问题
资源下载链接为: https://pan.quark.cn/s/abbae039bf2a 在计算机视觉领域,实时目标跟踪是许多应用的核心任务,例如监控系统、自动驾驶汽车和无人机导航等。本文将重点介绍一种在2017年备受关注的高效目标跟踪算法——BACF(Boosted Adaptive Clustering Filter)。该算法因其卓越的实时性和高精度而脱颖而出,其核心代码是用MATLAB编写的。 BACF算法全称为Boosted Adaptive Clustering Filter,是基于卡尔曼滤波器改进的一种算法。传统卡尔曼滤波在处理复杂背景和目标形变时存在局限性,而BACF通过引入自适应聚类和Boosting策略,显著提升了对目标特征的捕获和跟踪能力。 自适应聚类是BACF算法的关键技术之一。它通过动态更新特征空间中的聚类中心,更准确地捕捉目标的外观变化,从而在光照变化、遮挡和目标形变等复杂情况下保持跟踪的稳定性。此外,BACF还采用了Boosting策略。Boosting是一种成学习方法,通过组合多个弱分类器形成强分类器。在BACF中,Boosting用于优化目标检测性能,动态调整特征权重,强化对目标识别贡献大的特征,从而提高跟踪精度。BACF算法在设计时充分考虑了计算效率,能够在保持高精度的同时实现快速实时的目标跟踪,这对于需要快速响应的应用场景(如视频监控和自动驾驶)至关重要。 MATLAB作为一种强大的数学计算和数据分析工具,非常适合用于算法的原型开发和测试。BACF算法的MATLAB实现提供了清晰的代码结构,方便研究人员理解其工作原理并进行优化和扩展。通常,BACF的MATLAB源码包含以下部分:主函数(实现整个跟踪算法的核心代码)、特征提取模块(从视频帧中提取目标特征的子程序)、聚类算法(实现自适应聚类过程)、Boosting算法(包含特征权重更新的代
内容概要:本书《Deep Reinforcement Learning with Guaranteed Performance》探讨了基于李雅普诺夫方法的深度强化学习及其在非线性系统最优控制中的应用。书中提出了一种近似最优自适应控制方法,结合泰勒展开、神经网络、估计器设计及滑模控制思想,解决了不同场景下的跟踪控制问题。该方法不仅保证了性能指标的渐近收敛,还确保了跟踪误差的渐近收敛至零。此外,书中还涉及了执行器饱和、冗余解析等问题,并提出了新的冗余解析方法,验证了所提方法的有效性和优越性。 适合人群:研究生及以上学历的研究人员,特别是从事自适应/最优控制、机器人学和动态神经网络领域的学术界和工业界研究人员。 使用场景及目标:①研究非线性系统的最优控制问题,特别是在存在输入约束和系统动力学的情况下;②解决带有参数不确定性的线性和非线性系统的跟踪控制问题;③探索基于李雅普诺夫方法的深度强化学习在非线性系统控制中的应用;④设计和验证针对冗余机械臂的新型冗余解析方法。 其他说明:本书分为七章,每章内容相对独立,便于读者理解。书中不仅提供了理论分析,还通过实际应用(如欠驱动船舶、冗余机械臂)验证了所提方法的有效性。此外,作者鼓励读者通过仿真和实验进一步验证书中提出的理论和技术。

7,785

社区成员

发帖
与我相关
我的任务
社区描述
VB 基础类
社区管理员
  • VB基础类社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧