Dim rs As New ADODB.Recordset Dim Cmd As Command
Dim param As ADODB.Parameter
Set Cmd = New Command
Cmd.ActiveConnection = db 'db 是连接字符串 Cmd.CommandText = "P_PayRents"
Cmd.CommandType = adCmdStoredProc
Set param = Cmd.CreateParameter("aa", adDate, adParamInput, 10, DTPicker1.Value) 'DTPicker1.Value 是一个日期控件
Cmd.Parameters.Append param Set rs = Cmd.Execute错误提示
运行错误 ‘3704’
对象关闭时,不能访问
就是 rs 没有打开
Dim param As ADODB.Parameter
Set Cmd = New Command
Cmd.ActiveConnection = db 'db 是连接字符串 Cmd.CommandText = "P_PayRents"
Cmd.CommandType = adCmdStoredProc
Set param = Cmd.CreateParameter("aa", adDate, adParamInput, 10, DTPicker1.Value) 'DTPicker1.Value 是一个日期控件
Cmd.Parameters.Append param Set rs = Cmd.Execute错误提示
运行错误 ‘3704’
对象关闭时,不能访问
就是 rs 没有打开
这个db应该是数据库连接 不是字符串
看看你的con打开了吗
从所给的代码中确实是难看出问题来
不如把这个过程的代码贴全再让大家看看
-----------------------------
不知道你的连接串是否正确呀?你可以先建个连接对象,用这个连接串连接一下,
看一下state属性是否为 adstateopen
(
@date as smalldatetime
)
AS create table #Rent
(
[PayRentRe] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PayRent] [smallmoney] NOT NULL ,
[HouseAddress] [varchar] (60) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OwnerName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OwnerPhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OwnerBankName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OwnerAccount] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OwnerAccountName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OwnerContractId] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[RenterContractId] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PayRent_id] [int] NOT NULL
)insert into #Rent
SELECT dbo.PayRent.PayRentRe, dbo.PayRent.PayRent, dbo.Owner.HouseAddress,
dbo.Owner.OwnerName, dbo.Owner.OwnerPhone, dbo.Owner.OwnerBankName,
dbo.Owner.OwnerAccount, dbo.Owner.OwnerAccountName,
dbo.PayRent.OwnerContractId, '',dbo.PayRent.PayRent_id
FROM dbo.Owner INNER JOIN
dbo.PayRent ON dbo.Owner.OwnerContractId = dbo.PayRent.OwnerContractId
WHERE (dbo.Owner.PayRentStyle <> '年付') AND (dbo.Owner.Cancelled < 2) AND
(dbo.PayRent.Finance = '') AND (dbo.PayRent.PayDate = @date)
update #Rent set #Rent.RenterContractId = renter.RenterContractId from #Rent inner join Renter on #Rent.OwnerContractId = Renter.OwnerContractId
where Renter.Currents = 1select * from #Rent order by OwnerName --最后一句临时表查询数据
见代码之后的存储过程 Dim rs As New ADODB.Recordset Dim Cmd As Command
Dim param As ADODB.Parameter
Set Cmd = New Command
Cmd.ActiveConnection = db 'db 是连接字符串 Cmd.CommandText = "P_PayRents"
Cmd.CommandType = adCmdStoredProc
Set param = Cmd.CreateParameter("aa", adDate, adParamInput, 10, DTPicker1.Value) 'DTPicker1.Value 是一个日期控件
Cmd.Parameters.Append param Set rs = Cmd.Execute存储过程
CREATE PROCEDURE P_PayRents
(
@date as smalldatetime
)
AS SELECT dbo.PayRent.PayRentRe, dbo.PayRent.PayRent, dbo.Owner.HouseAddress,
dbo.Owner.OwnerName, dbo.Owner.OwnerPhone, dbo.Owner.OwnerBankName,
dbo.Owner.OwnerAccount, dbo.Owner.OwnerAccountName,
dbo.PayRent.OwnerContractId, OwnerContractId2 ,dbo.PayRent.PayRent_id
FROM dbo.Owner INNER JOIN
dbo.PayRent ON dbo.Owner.OwnerContractId = dbo.PayRent.OwnerContractId
WHERE (dbo.Owner.PayRentStyle <> '年付') AND (dbo.Owner.Cancelled < 2) AND
(dbo.PayRent.Finance = '') AND (dbo.PayRent.PayDate = @date)
set nocount on
(
@date as smalldatetime
)
AS
set nocount on ---加入
SELECT dbo.PayRent.PayRentRe, dbo.PayRent.PayRent, dbo.Owner.HouseAddress,
dbo.Owner.OwnerName, dbo.Owner.OwnerPhone, dbo.Owner.OwnerBankName,
dbo.Owner.OwnerAccount, dbo.Owner.OwnerAccountName,
dbo.PayRent.OwnerContractId, OwnerContractId2 ,dbo.PayRent.PayRent_id
FROM dbo.Owner INNER JOIN
dbo.PayRent ON dbo.Owner.OwnerContractId = dbo.PayRent.OwnerContractId
WHERE (dbo.Owner.PayRentStyle <> '年付') AND (dbo.Owner.Cancelled < 2) AND
(dbo.PayRent.Finance = '') AND (dbo.PayRent.PayDate = @date)
set 或聚合删除了空值
CREATE PROCEDURE testpro
(
@date as varchar(10)
)
AS create table #Rent
(
[pa_no] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[pa_name] [varchar] (20) NOT NULL ,
[pa_oudat] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[pa_arno1] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
)insert into #Rent
select pa_no,pa_name,pa_oudat,pa_arno1 from iepa00h where pa_oudat=@dateupdate #Rent set pa_arno1=pa_arno1+'qq'select * from #Rent order by pa_no
VB中:
Option Explicit
Dim db As String
Private Sub Command1_Click()
Dim rs As New ADODB.Recordset
Dim Cmd As Command
Dim param As ADODB.Parameter
Set Cmd = New Command
Cmd.ActiveConnection = db 'db 是连接字符串 Cmd.CommandText = "testpro"
Cmd.CommandType = adCmdStoredProc
Set param = Cmd.CreateParameter("aa", adVarChar, adParamInput, 10, "2006/10/25")
Cmd.Parameters.Append param Set rs = Cmd.Execute
Debug.Print rs.RecordCount
End SubPrivate Sub Form_Load()
db = "Driver=SQL Server;Server=127.0.0.1;UID=sa;PWD=123456;DataBase=TEST"
End Sub
经以上测试没有任何问题,其中也用到了临时表
set 或聚合删除了空值"
在查询分析器中执行没有任何问题,也没有任何提示
我有新建了一个与临时表完全相同的实际表,把临时表中的数据全部倒入到这个新建的表中,
之后从这个新建的表中 select 数据,就可以了
项目急,先这么对付,没办法