dim rs as new adodb.recordset set rs=cn.execute "execute 存储过程名" & 传入参数
反正需要用到adodb.command对象,查一下帮助,这个东西可以设置参数是输入还是输出
dim mrst as new adodb.recordset Set mrst = cn.Execute("procName('" & exampleA & "')", , adCmdStoredProc) Set Me.Mshflexgrid1.DataSource = rs Mshflexgrid1.Refresh其中cn为ADODB.Connection
Private Sub Form_Load()
'先引用ADO对象库 Dim objCn As New ADODB.Connection Dim objCm As New ADODB.Command Dim objPara As New ADODB.Parameter
With objPara .Name = "@f2" .Type = adBigInt .Direction = adParamOutput End With
Set objPara = Nothing Set objCm = Nothing Set objCn = Nothing End Sub'---------sp_Test的建立----------'CREATE PROCEDURE [dbo].[sp_Test] '@f2 BigInt OUT ' AS 'set nocount on 'declare @f3 BigInt 'SELECT @f2 = ( ' SELECT f2 FROM t1 WHERE f1 = 1 ' ) 'SELECT @f3 = ( ' SELECT f3 FROM t1 WHERE f1 = 1 ' ) 'return @f3 'GO
问题: 我会建立并调用参数存储过程了,可是如何获得返回值哪?
回答:
Access ADP 调用代码如下:
dim comm as new adodb.command
comm.activeconnection=currentproject.connection
Comm.CommandText="cg_GP_GetBeforeDateAROrAPBalance23"
Comm.Parameters.Append Comm.CreateParameter("ReturnCode",adInteger,adParamReturnValue)
Comm.Parameters.Append Comm.CreateParameter("OperatorID",advarchar,adParamInput,25)
Comm.Parameters("OperatorID")="56557325"
Comm.Parameters.Append Comm.CreateParameter("nType",adinteger,adParamInput)
Comm.Parameters("nType")=nType
Comm.Parameters.Append Comm.CreateParameter("StartDate",adDate,adParamInput)
Comm.Parameters("StartDate")=szStartDate
Comm.Parameters.Append Comm.CreateParameter("BtypeId",advarchar,adParamInput,25)
Comm.Parameters("BtypeId")=szBtypeID
Comm.Parameters.Append Comm.CreateParameter("PreTotal",adDouble,adParamOutput)
Comm.Execute()
dPreTotal=Comm("PreTotal") '这个就是存储过程的返回值
nRe=Comm("ReturnCode") 存储过程如下:Create PROCEDURE cg_GP_GetBeforeDateAROrAPBalance23
(
@OperatorID varchar(25), ----------------操作员ID号
@nType int=1, ---------------往来单位类型,1:代表客户 2:代表供应商
@StartDate SmallDatetime, ----------------时间点
@BtypeID Varchar(25), ----------------客户typeid
@PreTotal Numeric(18,4) output ----------------返回余额
)
AS
select @PreTotal =255
return 1
go
录入:changechange(CSDN) 责任编辑:cg1
set rs=cn.execute "execute 存储过程名" & 传入参数
Set mrst = cn.Execute("procName('" & exampleA & "')", , adCmdStoredProc)
Set Me.Mshflexgrid1.DataSource = rs
Mshflexgrid1.Refresh其中cn为ADODB.Connection
'先引用ADO对象库
Dim objCn As New ADODB.Connection
Dim objCm As New ADODB.Command
Dim objPara As New ADODB.Parameter
With objPara
.Name = "@f2"
.Type = adBigInt
.Direction = adParamOutput
End With
objCn.Open "Provider=SQLOLEDB.1;PassWord=yhy;UID=sa;Initial Catalog=test;Data Source=杨宏义"
With objCm
.ActiveConnection = objCn
.CommandType = adCmdStoredProc
'返回值
.Parameters.Append .CreateParameter("@f3", adBigInt, adParamReturnValue)
'输出参数
.Parameters.Append objPara
.CommandText = "sp_Test"
.Execute
MsgBox .Parameters("@f3").Value
End With
'输出参数的值
MsgBox objPara.Value
Set objPara = Nothing
Set objCm = Nothing
Set objCn = Nothing
End Sub'---------sp_Test的建立----------'CREATE PROCEDURE [dbo].[sp_Test]
'@f2 BigInt OUT
' AS
'set nocount on
'declare @f3 BigInt
'SELECT @f2 = (
' SELECT f2 FROM t1 WHERE f1 = 1
' )
'SELECT @f3 = (
' SELECT f3 FROM t1 WHERE f1 = 1
' )
'return @f3
'GO