Set DBCmd = New ADODB.Command
With DBCmd
.CommandType = adCmdStoredProc
.CommandText = "GetFullPath"
.Parameters.Refresh
Set .ActiveConnection = DBCn
.Parameters("@Type").Value = "Department"
.Parameters("@Id").Value = tmpID
.Parameters("@Pathcode").Value = "\"
.Execute
FullPath = .Parameters("@FullPath").Value
End With
With DBCmd
.CommandType = adCmdStoredProc
.CommandText = "GetFullPath"
.Parameters.Refresh
Set .ActiveConnection = DBCn
.Parameters("@Type").Value = "Department"
.Parameters("@Id").Value = tmpID
.Parameters("@Pathcode").Value = "\"
.Execute
FullPath = .Parameters("@FullPath").Value
End With
With DBCmd
.CommandType = adCmdStoredProc
.CommandText = "GetFullPath"
Set .ActiveConnection = DBCn
.Parameters.Refresh
.Parameters("@Type").Value = "Department"
.Parameters("@Id").Value = tmpID
.Parameters("@Pathcode").Value = "\"
.Execute
FullPath = .Parameters("@FullPath").Value
End With
存储过程如果过于复杂可能不会有返回值,例如频繁的update/insert操作
一般的简单存储过程是没问题的
还有就是杜绝里面的print语句
存储过程在执行时会产生各种信息,例如update会有 n rows 被更新等。
所以有时接收到不是想要的,可以加上set nocount on,过滤这些信息。
上面的@FullPath是一个输出参数,这个是不会受到影响的,建议使用输出参数,尤其是返回多个值时.
@codepar char(10)
@erp int
as
declare @ctt int
select @ctt = isnull(c,0) from tbl3 where code=@codepar
if @ctt =1
begin
if(select count(*) from tbl1,tbl2 where tbl1.classid = tbl2.classid and tbl1.name = tbl2.name and tbl1.w < tbl2.w ) <> 0
set @erp=1
else
begin
insert into tbl4
select * from tbl2
update tbl1 set tbl1.w = tbl1.w - tbl2.w from tbl1,tbl2 where tbl1.classid = tbl2.classid and tbl1.name = tbl2.name
set @erp=2
end
end
GO
代码:
Dim cnn1 As ADODB.Connection
Dim MyCommand As ADODB.Command
Dim Classid As ADODB.Parameter
Dim MsgInt As ADODB.Parameter
Dim Rst As ADODB.RecordsetConst Cstring = "dsn=ctest"Private Sub Command1_Click()
Dim s As Integer
Set Classid = New ADODB.Parameter
Set MyCommand = New ADODB.Command
MyCommand.Parameters.Append MyCommand.CreateParameter(, adInteger, adParamInput, , 1001) MyCommand.Parameters.Append MyCommand.CreateParameter(, adInteger, adParamOutput)
MyCommand.ActiveConnection = cnn1
'‘指定该command 的当前活动连接
MyCommand.CommandText = "test"
'‘myprocedure 是你要调用的存储过程名称
MyCommand.CommandType = adCmdStoredProc
'‘表明command 为存储过程 Set Rst = MyCommand.Execute()
s = MyCommand.Parameters(1).Value
MsgBox s
End SubPrivate Sub Form_Load()
Set cnn1 = New ADODB.Connection
cnn1.Open Cstring
End SubPrivate Sub Form_Unload(Cancel As Integer)
cnn1.Close
Set cnn1 = Nothing
End Sub
这段代码显示不出信息。为什么》
@codepar char(10)
@erp int Output /*在存储过程中加上Output才是输出参数*/
@t int,
@c int output
AS
select * from tbl1 where classid=@t
set @c=1
GO我用上面的代码做了测试一样的结果
我发现MyCommand.Parameters(1).Value根本没有返回任何参数
是一个空值,所以S显示一直为0