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
...全文
91 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
  • 打赏
  • 举报
 回复
你的存储过程有问题
因为我把你的存储过程换成其他的都没有问题
数据库帖子收集
这样的数据列表在存储过程中应该怎么样选择得到？ 请问造成SQL2000服务不能启动的原因有哪些？ sql server中有無類似于if 的函數,即像這樣select if(cancel=1,'cancel','') from sales 这样怎么返回呀！！！！...
我在CSDN参与的3000个帖子
今日偶然翻到，感慨万千 1：申述：版主，是否扣了我的专家分？ 2：100分急求，随机输出十个小写字母，但是，要求这十个字母不相同 3：求Sn=a+aa+aaa+…+aaa…a（n个a）之值 4：数组题 望高手帮忙！ 5：呵呵，来推荐...
WEB开发文档2 总结
http://blog.donews.com/lvjiyong/archive/2006/06/29/931071.aspx 怎样将后台生成的在内存中的图象显示到客户端 Microsoft IE WebControls下载地址 如何在DATAGRID中使用JAVASCRIPT脚本控制 DataGrid中连接到...
MS-SQL Server 基础类 - SQL语句
现在我的企业管理器打不开了，只能用查询分析器，可我不知道怎样用SQL语句在原表的基础上增... 复制表结构 判断表是否为空？ 查询结果问题？ 比较两个表中的记录是否一致？ 高分请教一条SQL语句 帮我看看，怎么回事...
C语言笔记01（7.15-7.18）
C语言基本知识点思维导图
VB基础类

7,785

社区成员

197,585

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章