请解决这个问题:
对象关闭时，不允许操作
Do While Not rstByQuery.EOF
代码如下：
Dim cnn1 As ADODB.Connection '连接
Dim mycommand As '命令
Dim parm_date1 As ADODB.Parameter '参数1
Dim parm_date2 As ADODB.Parameter '参数2
Dim rstByQuery As ADODB.Recordset '结果集
Dim strCnn As String '连接字符串
Private Sub Command1_Click()
Dim i As Integer
Dim j As Integer
Set parm_date1 = New ADODB.Parameter
Set mycommand = New
' parm_jobid.Name = "name1"
parm_date1.Type = adChar '参数类型
parm_date1.Size = 10 '参数长度
parm_date1.Direction = adParamInput
'参数方向，输入或输出
parm_date1.Value = DTPicker1.Value '参数的值
mycommand.Parameters.Append parm_date1 '加入参数
Set parm_date2 = New ADODB.Parameter
'parm_joblvl.Name = "name2"
parm_date2.Type = adInteger
parm_date2.Size = 10
parm_date2.Direction = adParamInput
parm_date2.Value = DTPicker2.Value
mycommand.Parameters.Append parm_date2
mycommand.ActiveConnection = cnn1
'指定该command 的当前活动连接
= "sp_kjy_tj"
'myprocedure 是你要调用的存储过程名称
= adCmdStoredProc
'表明command 为存储过程
Set rstByQuery = New ADODB.Recordset
Set rstByQuery = mycommand.Execute()
MSFlexGrid1.Rows = 1
'动态设置MSFlexGrid的行和列
MSFlexGrid1.Cols = rstByQuery.Fields.Count
MSFlexGrid1.Row = 0
For i = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = i
MSFlexGrid1.Text = rstByQuery.Fields.Item(i).Name
Next '设置第一行的标题，用域名填充
i = 1
Do While Not rstByQuery.EOF
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.Row = i '确定行
For j = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = j
MSFlexGrid1.Text = rstByQuery(j)
'添充所有的列
Next
rstByQuery.MoveNext
i = i + 1
Loop
End Sub
Private Sub Command2_Click()
End
End Sub
Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
DTPicker1.Value = Form1(Now(), "yyyy-mm-dd
")
End Sub
Private Sub DTPicker2_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
DTPicker1.Value = Form1(Now(), "yyyy-mm-dd
")
End Sub
Private Sub Form_Load()
Set cnn1 = New ADODB.Connection
'生成一个连接
strCnn = "Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=jxrmyy;Data Source=JXJXYY\SQL2005"
cnn1.Open strCnn '打开连接
End Sub
Private Sub Form_Unload(Cancel As Integer)
cnn1.Close '关闭连接
Set cnn1 = Nothing '释放连接
End Sub
End Sub存储过程
CREATE PROCEDURE sp_kjy_tj
@date1 varchar(10),
@date2 varchar(10)
AS
create table #cf_stat
(
patient_id varchar(10) null,
doctor_name varchar(20) null,
doctor_office varchar(30) null,
pz_count int null,
yp_je dec (10,2)null,
--
kj_count int null,
--
cf_code varchar(20)null
--
)
insert into #cf_stat(patient_id,doctor_name,doctor_office,cf_code,pz_count,yp_je)
select patient_id,doctor_code,doctor_office,cf_code,count(item_code),sum((quantity - quantity_return)*price)
from ci_advice_Fee
where
convert(varchar(10),insert_date,120)>=@date1 and
convert(varchar(10),insert_date,120)<=@date2 and kind_flag='0' and cancel_sign='1'
group by patient_id,doctor_code,doctor_office,cf_code
update #cf_stat set kj_count = isnull(b.kj_count ,0) from #cf_stat a inner join v_ci_advice_Fee b on a.patient_id =b.patient_id and a.cf_code = b.cf_code
--
set kj_count =
(select isnull( b.kj_count,0) from
v_ci_advice_Fee b where #cf_stat.cf_code = b.cf_code)
--
where cf_code in ( select cf_code from v_ci_advice_Fee)
--
select patient_id,doctor_name,doctor_office,cf_code,pz_count,yp_je,kj_count
from #cf_stat
drop table #cf_stat
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
如何使用？请指教。
2006-12-22 13:33
