我試圖在傳遞存盤程序的同時在 Excel 中使用 VBA 執行 SQL Server 存盤程序。
我的存盤程序代碼如下：
update [ASHCOURT_Weighsoft5].[dbo].[Invoice] set Is3rdPartyPosted = 0 where DocumentId = @document_no
我的 VBA 代碼如下：
Sub reverse_posted()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set Rs = New ADODB.Recordset
Dim i As Long
i = InputBox("Invoice Number to be re-posted")
con.Open "Provider=SQLOLEDB;Data Source=ashcourt_app1;Initial Catalog=ASHCOURT_Weighsoft5;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con
cmd.CommandText = "ashcourt_balfour_reverse_posting" & i
Set Rs = cmd.Execute(, , adCmdStoredProc)
Rs.Close
Set Rs = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing
End Sub
我的 VBA 非常生疏，所以很抱歉。本質上，我試圖將 i 變數的內容傳遞給存盤程序中的引數 @document_no。
提前致謝
uj5u.com熱心網友回復：
在命令中添加引數
Option Explicit
Sub reverse_posted()
Const PROC = "ashcourt_balfour_reverse_posting"
Dim con As ADODB.Connection, cmd As ADODB.Command, i As Long
i = InputBox("Invoice Number to be re-posted")
Set con = New ADODB.Connection
con.Open "Provider=SQLOLEDB;Data Source=ashcourt_app1;" & _
"Initial Catalog=ASHCOURT_Weighsoft5;" & _
"Integrated Security=SSPI;Trusted_Connection=Yes;"
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = PROC
.Parameters.Append .CreateParameter("P1", adInteger, adParamInput)
.Execute , i
End With
con.Close
Set con = Nothing
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/358511.html
上一篇：如果沒有找到與搜索值匹配的行