调用存储过程如何提供参数？

koutx

后台SQL Serve库中有表khb，有一更新该表中客户的存储过程如下图：

在Access中用ADO调用此过程时如何提供两个参数？谢谢

koutx

我用如下方法，却总提示@客户_1参数未提供。不知该如何提供此参数？
Dim cmd  As New ADODB.Command
Set cmd.ActiveConnection = cn() ' 调用 cn()打开与SQL Server库的连接
cmd.CommandType = adCmdStoredProc '设置命令类型为存储过程
cmd.CommandText = "update_khb_1"
   
cmd.Parameters("@客户_2").Value = 刘三
cmd.Parameters("@客户_1").Value = 李三
cmd.Execute

roych

koutx 发表于 2015-8-20 16:53
我用如下方法，却总提示@客户_1参数未提供。不知该如何提供此参数？
Dim cmd  As New ADODB.Command
Set  ...

印象中我写过一个，供参考：
http://www.office-cn.net/thread-119044-1-1.html

koutx

没办法,发不上去只好变成图

tmtony

1.你存储过程的字符串参数最好定义一个长度
2.调用方法如下

第一种：

Set parm_opt = New ADODB.Parameter
parm_opt.Name = "optcode"
parm_opt.Type = adInteger '参数类型
parm_opt.Size = 4        '参数长度
parm_opt.Direction = adParamInput   '参数方向，输入或输出
parm_opt.Value = 1000      '参数的值
cmd.Parameters.Append parm_opt  '加入参数
  

Set parm_std = New ADODB.Parameter
parm_std.Name = "startday"
parm_std.Type = adVarChar
parm_std.Size = 10
parm_std.Direction = adParamInput
parm_std.Value = Format(DTPicker1.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_std



第二种：

set parm_opt = cmd.creatrparameter( "optcode",adInteger,adParamInput,4,1000
)

cmd.Parameters.Append parm_opt



set parm_std = cmd.creatrparameter( "startday",adVarChar,adParamInput,10,Format(DTPicker1.Value, "YYYY-MM-DD")
)

cmd.Parameters.Append parm_std

koutx

tmtony 发表于 2015-8-22 08:48
1.你存储过程的字符串参数最好定义一个长度
2.调用方法如下

谢谢，的确是@客户_1未定义长度的问题。

zpy2

'DAO 直接 加 参数 可 执行，但是，要设置odbc
'ADO 需要 append 参数才能 执行

'BeginAppendVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
   
Public Sub Main()
    On Error GoTo ErrorHandler

    'recordset, command and connection variables
    Dim Cnxn As ADODB.Connection
    Dim cmdByRoyalty As ADODB.Command
    Dim prmByRoyalty As ADODB.Parameter
    Dim rstByRoyalty As ADODB.Recordset
    Dim rstAuthors As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQLAuthors As String
    Dim strSQLByRoyalty As String
     'record variables
    Dim intRoyalty As Integer
    Dim strAuthorID As String
   
    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
      
    ' Open command object with one parameter
    Set cmdByRoyalty = New ADODB.Command
    cmdByRoyalty.CommandText = "byroyalty"
    cmdByRoyalty.CommandType = adCmdStoredProc
   
    ' Get parameter value and append parameter
    intRoyalty = Trim(InputBox("Enter royalty:"))
    Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", adInteger, adParamInput)
    cmdByRoyalty.Parameters.Append prmByRoyalty   'ADO 需要 append 参数才能 执行
    prmByRoyalty.Value = intRoyalty
   
    ' Create recordset by executing the command
    Set cmdByRoyalty.ActiveConnection = Cnxn
    Set rstByRoyalty = cmdByRoyalty.Execute
   
    ' Open the Authors Table to get author names for display
    ' and set cursor client-side
    Set rstAuthors = New ADODB.Recordset
    strSQLAuthors = "Authors"
    rstAuthors.Open strSQLAuthors, Cnxn, adUseClient, adLockOptimistic, adCmdTable
   
    ' Print recordset adding author names from Authors table
    Debug.Print "Authors with " & intRoyalty & " percent royalty"
   
    Do Until 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

    ' clean up
    rstByRoyalty.Close
    rstAuthors.Close
    Cnxn.Close
    Set rstByRoyalty = Nothing
    Set rstAuthors = Nothing
    Set Cnxn = Nothing
    Exit Sub
   
ErrorHandler:
    ' clean up
    If Not rstByRoyalty Is Nothing Then
        If rstByRoyalty.State = adStateOpen Then rstByRoyalty.Close
    End If
    Set rstByRoyalty = Nothing
   
    If Not rstAuthors Is Nothing Then
        If rstAuthors.State = adStateOpen Then rstAuthors.Close
    End If
    Set rstAuthors = Nothing
   
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
   
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndAppendVB
Private Sub runSp()
  'ado commands
  Dim db As DAO.Database
  Dim rs As Recordset
  Dim strConnect As String
  Dim strSql As String
  Dim strResult As String
  Dim i As Integer, strRs As String

  strConnect = "ODBC;DSN=Match20140810;DATABASE=tempdb"
  'Open the database
  Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, _
  strConnect)
  
  
  ' String specifying SQL.
  'strSql = "StoredProcedure1 3, 'WanWu'"
  strSql = "StoredProcedure1 '张三', '李三'"
  ' For a stored procedure that doesn't return records.
  db.Execute strSql, dbSQLPassThrough   'DAO 直接 加 参数 可 执行，但是，要设置odbc
  'i = db.RowsAffected
  'Debug.Print i
  db.Close
End Sub

koutx

谢谢详尽的讲解，将经测试通过的ADO调用insert、update、和delete三类存储过程的代码整理一下单独发贴，希望对坛友有所帮助