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

[火鸟33]

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

[zhangcyu]

你的存储过程有问题
因为我把你的存储过程换成其他的都没有问题

[火鸟33]

是存储过程问题，因为第一句sql语句不返回纪录集，哎

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

看能设定参数，使纪录能正常返回，不然要改存储过程，就不能兼容老程序，而且改的范围也太大

[zhangcyu]

你在delete from TempDb后加一个go
然后在游标循环后close shiftcursor
DEALLOCATE shiftcursor 再加一个go
在 --返回纪录
select * from tempdb order by L25,Ldate
前加一个go

[yoki]

在你的存储过程前后分别加上：

set nocount on
.
.
.
.
set nocount off

[火鸟33]

结帖子

zhangcyu() 300分 你的太麻烦，改动太多
yoki(小马哥) 7000 使用这种方法

[火鸟33]

谁解决廖，我发1000分

在线等

[SoHo_Andy]

Set rs = ChargeRD.Execute
这个语句返回的是静态的只能前滚的记录集，你将它rs.MoveFirst 是不行的

[火鸟33]

要怎么做啊？

[火鸟33]

不是这个问题，我用exec 执行存储过程也不行，而且绝对有记录集

[aalei]

你不要用COMMAND试试

[火鸟33]

仔细看代码，只要能使用存储过程返回纪录就行，我都试验过的，一样

Delphi没问题，VBA怎么这么多问题，破领导，非的叫用excel开发报表系统，烦

[lisong770818]

你在建源打开前即(conn.open前)，把下面这段代码加进去试试。
conn.CursorLocation = adUseClient

[ad4017]

请注意：是存储过程的问题，把存储过程贴出来看看。

[火鸟33]

存储过程，比较复杂

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

[火鸟33]

/*处理月报 */
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]

还是不行的