一个例子,传入三个参数"学生ID","考试科目","成绩"字段.做Insert动作. Dim lcmwuqi As New ADODB.Command On Error GoTo inerr 'If lcmwuqi Is Nothing Then 'Set lcmwuqi = New ADODB.command With lcmwuqi .ActiveConnection = conn .CommandText = "p_NewStudentTest" .CommandType = adCmdStoredProc End With 'End If lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("学生ID", adInteger, adParamInput, 4, CInt(stuID)) lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("考试科目", adVarChar, adParamInput, 30, tbTextName.Text) lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("成绩", adVarChar, adParamInput, 6, tbText.Text) lcmwuqi.Execute
存储过程是: CREATE Proc p_NewStudentTest @学生ID int,@考试科目 varchar(30),@成绩 varchar(6) as ......
信箱TELL ME 我有一个简单的存储过程的例子
调用存储过程的例子: Public Sub ActiveConnectionX() Dim cnn1 As ADODB.Connection Dim cmdByRoyalty As ADODB.Command Dim prmByRoyalty As ADODB.Parameter Dim rstByRoyalty As ADODB.Recordset Dim rstAuthors As ADODB.Recordset Dim intRoyalty As Integer Dim strAuthorID As String Dim strCnn As String ' 定义存储过程的命令对象。 Set cnn1 = New ADODB.Connection strCnn = "Provider=sqloledb;" & _ "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " cnn1.Open strCnn Set cmdByRoyalty = New ADODB.Command Set cmdByRoyalty.ActiveConnection = cnn1 cmdByRoyalty.CommandText = "byroyalty" cmdByRoyalty.CommandType = adCmdStoredProc cmdByRoyalty.CommandTimeout = 15
潘爱民先生的讲解存储过程和参数处理存储过程与前面介绍的“编译过”的命令对象有点类似,存储过程运行在服务器上,它可以把复杂的应用逻辑封装在服务器一端,而在客户程序中只需调用简单的SQL语句即可。如果我们要在ADO的Command对象中执行存储过程,可以把CommandType属性指定为adCmdStoredProc,当然,在存储过程类型的Command对象中,不要再指定Prepared属性为TRUE。存储过程的用法非常简单,下面是一个例子:Dim Cmd As New ADODB.CommandDim rs As New ADODB.RecordsetCmd.ActiveConnection = "DSN=MySamples;uid=sa"Cmd.CommandText = "MyProc"Cmd.CommandType = adCmdStoredProcSet rs = Cmd.Execute()Debug.Print rs(0)rs.Close存储过程允许包含输入输出参数和返回值,对应到Command对象中,这些参数就是Parameter对象,输入参数的处理比较简单,输出参数和返回值的处理有所不同,只有当返回的Recordset对象的记录全部遍历或Recordset对象关闭之后,输出参数和返回值才真正有效。对于下面的存储过程:CREATE PROCEDURE MyProc @ioparm int OUTPUT ASSELECT name FROM MyTestTable WHERE id < 2SELECT @ioparm = 1RETURN 100下面的代码执行此存储过程:Dim Cmd As New ADODB.CommandDim rs As New ADODB.RecordsetDim param As Parameter Cmd.ActiveConnection = "DSN=MySamples;UID=sa"Cmd.CommandText = "MyProc"Cmd.CommandType = adCmdStoredProc' Set up parameters.Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0)Cmd.Parameters.Append paramSet param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)Cmd.Parameters.Append paramSet rs = Cmd.ExecuteIf Not rs.EOF And Not rs.BOF ThenDebug.Print rs(0) rs.CloseEnd IfDebug.Print Cmd(0) ' The return codeDebug.Print Cmd(1) ' The Output parameter
Dim lcmwuqi As New ADODB.Command
On Error GoTo inerr
'If lcmwuqi Is Nothing Then
'Set lcmwuqi = New ADODB.command
With lcmwuqi
.ActiveConnection = conn
.CommandText = "p_NewStudentTest"
.CommandType = adCmdStoredProc
End With
'End If
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("学生ID", adInteger, adParamInput, 4, CInt(stuID))
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("考试科目", adVarChar, adParamInput, 30, tbTextName.Text)
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("成绩", adVarChar, adParamInput, 6, tbText.Text)
lcmwuqi.Execute
CREATE Proc p_NewStudentTest @学生ID int,@考试科目 varchar(30),@成绩 varchar(6)
as
......
我有一个简单的存储过程的例子
Public Sub ActiveConnectionX() Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String ' 定义存储过程的命令对象。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = cnn1
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15
' 定义存储过程的输入参数。
intRoyalty = Trim(InputBox( _
"Enter royalty:"))
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.Value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty
' 通过执行该命令创建记录集。
Set rstByRoyalty = cmdByRoyalty.Execute()
' 打开作者表以便显示作者姓名。
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", strCnn, , , adCmdTable
' 打印记录集中的当前数据,从作者表中添加作者姓名。
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print , rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop rstByRoyalty.Close
rstAuthors.Close
cnn1.Close
End Sub