Stored Procedure using Parameters

Question

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.

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

Francis Dean · Accepted Answer · 2012-07-12 08:24:36Z

2

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

answered Jul 12, 2012 at 8:24

Francis Dean

2,5062 gold badges23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2013-10-09 03:23:17Z

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

EdChum · Accepted Answer · 2012-07-11 22:51:55Z

0

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

edited Jul 11, 2012 at 22:51

EdChum

397k204 gold badges837 silver badges583 bronze badges

answered Jul 11, 2012 at 15:32

Learner

112 bronze badges

1 Comment

Fionnuala Over a year ago

Not .text, the .text property is only available when the control have the focus. The default property is .value so you can just say yourComboBox, as long as the bound column contains the required value. If you must, you can use the .value property explicitly, but you do not have to.

Collectives™ on Stack Overflow

Stored Procedure using Parameters

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related