我在VB调用SQL2000中的存储过程时,老是出现“对象关闭时,操作不被允许”,这是怎么一回事?代码如下:
Private Sub Command1_Click()
…………
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "order_pro"
Set cmd.ActiveConnection = cnn Set Param1 = cmd.CreateParameter("", adInteger, adParamReturnValue)
cmd.Parameters.Append Param1
Set Param2 = cmd.CreateParameter("", adInteger, adParamInput, , Val(Text1.Text))
cmd.Parameters.Append Param2
Set Param3 = cmd.CreateParameter("", adInteger, adParamOutput)
cmd.Parameters.Append Param3 Set rs = New ADODB.Recordset
Set rs = cmd.Execute
Dim i As Integer
i = 0
While Not rs.EOF …………提示在这里出错!
For Each Fld In rs.Fields
Debug.Print Fld(i) & Space(8)
i = i + 1
Next Fld
rs.MoveNext
i = 0
Wend
…………
End Sub
Private Sub Command1_Click()
…………
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "order_pro"
Set cmd.ActiveConnection = cnn Set Param1 = cmd.CreateParameter("", adInteger, adParamReturnValue)
cmd.Parameters.Append Param1
Set Param2 = cmd.CreateParameter("", adInteger, adParamInput, , Val(Text1.Text))
cmd.Parameters.Append Param2
Set Param3 = cmd.CreateParameter("", adInteger, adParamOutput)
cmd.Parameters.Append Param3 Set rs = New ADODB.Recordset
Set rs = cmd.Execute
Dim i As Integer
i = 0
While Not rs.EOF …………提示在这里出错!
For Each Fld In rs.Fields
Debug.Print Fld(i) & Space(8)
i = i + 1
Next Fld
rs.MoveNext
i = 0
Wend
…………
End Sub
方法:
1.在存储过程里加上set count off,在需要返回记录集的地方再打开set count on
2.在程序里
...
Set rs = cmd.Execute
do while not rs.state=adstatopen
set rs=rs.nextrecordset
loop Dim i As Integer
i = 0
While Not rs.EOF …………提示在这里出错!
For Each Fld In rs.Fields
Debug.Print Fld(i) & Space(8)
i = i + 1
Next Fld
rs.MoveNext
i = 0
Wend
…………
CREATE PROCEDURE Sales_pro @inparam int,@outparam int output AS
SET NOCOUNT ON
SELECT @outparam=quantity*price FROM SAELS
WHERE quantity>@inparam
and the storeprocedure returns more than 100,How & Where can I
put "SET NOCOUNT OFF" into the upper Sales_pro
CREATE PROCEDURE Sales_pro @inparam int,@outparam int output AS
SELECT @outparam=sum(quantity*price) FROM SAELS
WHERE quantity>@inparam程序:
cmd.Execute
debug.print "数量大于 " & Text1.Text & " 价格合计为 " & Param3.value如果你的存储过程为:
CREATE PROCEDURE Sales_pro @inparam int AS
SELECT quantity*price as Total FROM SAELS
WHERE quantity>@inparam程序为:
set rs=cmd.Execute() While Not rs.EOF
Debug.Print rs.fields("total").value & Space(8)
rs.MoveNext
Wend
reach my purpose?Because what you give me the STOREDPROCEDURE has changed,namely
parameter of output is vanish?
Sorry,my OS is Japanese Version,so puting any chinese characters is an obstales to me,here I have to commuciate in English!