如何使用Excel VBA字符串中的参数运行存储过程？ [英] How do I run a Stored procedure with parameters from Excel VBA string?

问题描述

我试图使用VBA从Excel运行存储过程，但是我使用以下代码获得类型不匹配错误。可以使用参数作为传递给Command对象的字符串执行存储过程，如下所示？

I am trying to run a stored procedure from Excel using VBA, but I'm getting a "Type Mismatch" error with the following code. Can I execute a Stored procedure with parameters as a string passed to the Command object as shown below?

Function Sproc()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim ConnectionString As String
    Dim StrSproc As String

    ConnectionString = "Provider=SQLOLEDB;Data Source=DBSource;" & _
                  "Initial Catalog=CurrentDb;" & _
                  "Integrated Security=SSPI;"

    'Opens connection to the database
    cnn.Open ConnectionString

    'Timeout error in seconds for executing the entire query; this will run for 15 minutes
    'before VBA times out, but your database might timeout before this value

    cnn.CommandTimeout = 900

    StrSproc = "EXEC StoredProcedure " & _
                    "@parameter1 = 0," & _
                    "@parameter2 = 0," & _
                    "@parameter3 = 0,"

        Application.StatusBar = "Running stored procedure..."
        Set rst = cmd.Execute(, , StrSproc)
End Function

推荐答案

尝试这个：

Option Explicit

Function Sproc()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim cnnStr As String
    Dim Rs As New ADODB.Recordset
    Dim StrSproc As String

    cnnStr = "Provider=SQLOLEDB;Data Source=DBSource;" & "Initial Catalog=CurrentDb;" & _
             "Integrated Security=SSPI;"
    With cnn
        .CommandTimeout = 900
        .ConnectionString = cnnStr
        .Open
    End With
    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "[StoredProcedureName]"
        .Parameters.Append .CreateParameter("@parameter1", adInteger, adParamInput, , 0)
        .Parameters.Append .CreateParameter("@parameter2", adInteger, adParamInput, , 0)
        .Parameters.Append .CreateParameter("@parameter2", adInteger, adParamInput, , 0)
    End With
    With Rs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmd
    End With
    Application.StatusBar = "Running stored procedure..."
    Set rst = cmd.Execute
End Function

这篇关于如何使用Excel VBA字符串中的参数运行存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！