vba 使用参数的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11436061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure using Parameters
提问by Luke Stringer
The Following code returns a stored procedure with a hard value coded. I need to allow 74 to change to whatever is selected from a combo box. Any help is greatly appreciated. I am using a pass through query in Access.
以下代码返回一个带有硬值编码的存储过程。我需要允许 74 更改为从组合框中选择的任何内容。任何帮助是极大的赞赏。我在 Access 中使用传递查询。
Private Sub ok_Click()
Dim objConnection As New ADODB.Connection
Dim objCom As ADODB.Command
Dim provStr As String
Set objCom = New ADODB.Command
objConnection.Provider = "sqloledb"
provStr = "Data Source=**;" & "Initial Catalog=IKB_QA;User Id=**;Password=**;"
objConnection.Open provStr
With objCom
.ActiveConnection = objConnection
.CommandText = "dbo.ix_spc_planogram_match 74"
.CommandType = adCmdStoredProc
.Execute
End With
End Sub
采纳答案by Luke Stringer
The following code grabs parameter from form and executes the stored procedure.
以下代码从表单中获取参数并执行存储过程。
Dim Cmd1 As ADODB.Command
Dim lngRecordsAffected As Long
Dim rs1 As ADODB.Recordset
Dim intRecordCount As Integer
'-----
Dim cnnTemp As ADODB.Connection
Set cnnTemp = New ADODB.Connection
cnnTemp.ConnectionString = "DRIVER=SQL Server;SERVER=***;" & _
"Trusted_Connection=No;UID=***;PWD=***;" & _
"Initial Catalog=IKB_QA;"
cnnTemp.ConnectionTimeout = 400
'Open Connection
cnnTemp.Open
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = cnnTemp
'---
With Cmd1
Dim localv As Integer
Dim inputv
localv = [Forms]![start]![Selection]![cat_code]
.CommandText = "dbo.ix_spc_planogram_match " & inputv
.CommandType = adCmdStoredProc
Set inputv = Cmd1.CreateParameter("@catcode", 3, 1, 10000, localv)
Cmd1.Parameters.Append inputv
Set rs1 = Nothing
Set rs1 = Cmd1.Execute
localv = 0
Do While Not rs1.EOF
Debug.Print rs1.Fields.Item("POG_DBKEY").Value = "POG_DBKEY"
Debug.Print rs1.Fields.Item("COMP_POG_DBKEY").Value = "COMP_POG_DBKEY"
Debug.Print rs1.Fields.Item("CURR_SKU_CNT").Value = "CURR_SKU_CNT"
Debug.Print rs1.Fields.Item("COMP_SKU_CNT").Value = "COMP_SKU_CNT"
Debug.Print rs1.Fields.Item("SKU_TOTAL").Value = "SKU_TOTAL"
Debug.Print rs1.Fields.Item("MATCHD").Value = "MATCHD"
localv = localv + 1
rs1.MoveNext
Loop
localv = localv
rs1.Close
Set rs1 = Nothing
Set rs1 = Nothing
End With
End Sub
回答by Francis Dean
You can use the command object's parameter fields for a neater approach:
您可以使用命令对象的参数字段来获得更简洁的方法：
With objCom
.ActiveConnection = objConnection
.CommandText = "dbo.ix_spc_planogram_match"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters(1).Value = ComboBox1.Value
.Execute
End With
回答by Learner
You can try this concatenation:
你可以试试这个串联：
replace your statement:
替换您的声明：
.CommandText = "dbo.ix_spc_planogram_match 74"
with:
和：
.CommandText = "dbo.ix_spc_planogram_match " & yourComboBox.Text
Assuming the combo box name is yourComboBox
假设组合框名称是 yourComboBox
