使用参数的存储过程

问题描述：

以下代码返回一个硬编码值的存储过程。我需要允许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

答

以下代码从form获取参数并执行存储过程。

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

答

你可以试试这个级联：

更换您的语句：

.CommandText = "dbo.ix_spc_planogram_match 74"

有：

.CommandText = "dbo.ix_spc_planogram_match " & yourComboBox.Text

假设组合框的名字是yourComboBox

不是.text，.text属性仅在控件具有焦点时可用。默认属性是.value，所以只要绑定列包含所需的值，就可以说'yourComboBox'。如果你必须的话，你可以明确地使用.value属性，但是你不需要。 – Fionnuala 2012-07-11 18:22:17

答

你可以使用命令对象的参数字段用于较简洁的方法：

With objCom   
    .ActiveConnection = objConnection   
    .CommandText = "dbo.ix_spc_planogram_match"   
    .CommandType = adCmdStoredProc    

    .Parameters.Refresh 
    .Parameters(1).Value = ComboBox1.Value 

    .Execute  
End With

使用参数的存储过程

相关推荐