'定义存储过程的传递参数
Dim TransComm As ADODB.Command
Dim TransPara As ADODB.Parameter
Dim ReturnFlag As Integer
Set TransComm = New ADODB.Command
TransComm.CommandText = "ZSP_PurOrder"
TransComm.CommandType = adCmdStoredProc
'主表
Set TransPara = New ADODB.Parameter
Set TransPara = TransComm.CreateParameter("Par1", adInteger)
TransPara.Direction = adParamReturnValue
TransComm.Parameters.Append TransPara
Set TransPara = New ADODB.Parameter
Set TransPara = TransComm.CreateParameter("Par2", adVarChar, aramInput, 4000, TransStr1)
TransComm.Parameters.Append TransPara
Set TransPara = New ADODB.Parameter
Set TransPara = TransComm.CreateParameter("Par3", SmallInt,aramInput, , TransFlag)
TransComm.Parameters.Append TransPara
Set TransComm.ActiveConnection = ServerConn
TransComm.Execute
If TransComm.Parameters(0) <> 0 Then
ErrNumber = TransComm.Parameters(0)
ErrFlag = 1
GoTo check
End If
Dim TransComm As ADODB.Command
Dim TransPara As ADODB.Parameter
Dim ReturnFlag As Integer
Set TransComm = New ADODB.Command
TransComm.CommandText = "ZSP_PurOrder"
TransComm.CommandType = adCmdStoredProc
'主表
Set TransPara = New ADODB.Parameter
Set TransPara = TransComm.CreateParameter("Par1", adInteger)
TransPara.Direction = adParamReturnValue
TransComm.Parameters.Append TransPara
Set TransPara = New ADODB.Parameter
Set TransPara = TransComm.CreateParameter("Par2", adVarChar, aramInput, 4000, TransStr1)
TransComm.Parameters.Append TransPara
Set TransPara = New ADODB.Parameter
Set TransPara = TransComm.CreateParameter("Par3", SmallInt,aramInput, , TransFlag)
TransComm.Parameters.Append TransPara
Set TransComm.ActiveConnection = ServerConn
TransComm.Execute
If TransComm.Parameters(0) <> 0 Then
ErrNumber = TransComm.Parameters(0)
ErrFlag = 1
GoTo check
End If
只供参考
serverconn是ado连接
transstr1,transflag是实参值
dim withevents cn as rdoconnection
dim en as rdoenvironment
rs as rdoresultset
qry as rodquyery'数据库连接
dim sql as string
sql="{ call 存储过程
sql="{ call 存储过程名(参数1,参数2,)
set qry=cn.creatQuery("Myquery",sql)
qry.rdoparameters(0)="内容" '传入参数
qry.rdoparameters(1).direction=rdparamoutput '设为传出参数
qry.execute
text1.text=qry.rdoparameters(1)'显示第二个参数值
如:myConn.execute("myProc " & param1 &"," & param2......)
Private Sub Command1_Click()
Dim cn as adodb.connection
Dim old_bm, new_bm As String
cn.Open "driver={SQL Server};server=server;uid=sa;pwd=sa;database=xjc2"'连接数据库
old_bm = "bm123"
new_bm = "bm"
cn.Execute "wfbmpm @old_bm='bm123',@old_pm='bm'"'调用存储过程wfbmpm,它要传入两个参数
End Sub
可我一运行,系统就提示:过程wfbmpm需要参数@old_bm,但未提供该参数。
我以前做过一次了,好像就是这样写的,但这次不行了。
Dim cn as adodb.connection
cn.Open "driver={SQL Server};server=server;uid=sa;pwd=sa;database=xjc2"'连接数据库
cn.Execute "call wfbmpm @old_bm='bm123',@old_pm='bm'"'调用存储过程,其需要传入两个参数,不需要传出任何值
End Sub
我一运行程序,系统就提示:过程wfbmpm需要参数@oldbm,但未提供该参数
我以前做过一次了,好像就是这样写的,但这次不行了。
cn.Execute "wfbmpm 'bm123','bm'"
Dim cn as adodb.connection
cn.Open "driver={SQL Server};server=server;uid=sa;pwd=sa;database=xjc2"'连接数据库
cn.Execute "call wfbmpm @old_bm='bm123',@old_pm='bm'"'调用存储过程,其需要传入两个参数,不需要传出任何值
End Sub
只是我写少了一个参数了,哈哈哈,但为了感谢大家的帮助和支持,也把分给大家了!