Problem with passing parameters to SQL procedure using VBA

Question

Hi,

I am trying to pass @intDocumentNo and @intCustomerNo to a stored procedure using VBA but only @intCustomerNo is updated in dbo.tblOrderHead. I don't think the problem is with the procedure because if I enter the values manually it runs properly.

What am I doing wrong?

VBA Code:

Private Sub intCustomerNo_Click()

Dim cmdCommand As New ADODB.Command

With cmdCommand
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "uspSelectCustomer"

        '@intDocumentNo
        .Parameters("@intDocumentNo").Value = Forms![frmSalesOrder].[IntOrderNo]

        '@intCustomerNo
        .Parameters("@intCustomerNo").Value = Me![intCustomerNo]

    .Execute

End With

DoCmd.Close
Forms![frmSalesOrder].Requery

End Sub

Procedure:

UPDATE      dbo.tblOrderHead
SET         dbo.tblOrderHead.intCustomerNo   = @intCustomerNo ,
            dbo.tblOrderHead.intPaymentCode  = dbo.tblCustomer.intPaymentCode,
            dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode,
            dbo.tblOrderHead.txtRegionCode   = dbo.tblCustomer.txtRegionCode,
            dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode,
            dbo.tblOrderHead.txtLanguageCode = dbo.tblCustomer.txtLanguageCode
FROM        dbo.tblOrderHead
INNER JOIN  dbo.tblCustomer ON dbo.tblOrderHead.intCustomerNo = 
            dbo.tblCustomer.intCustomerNo
AND         dbo.tblOrderHead.intOrderNo = @intDocumentNo

Solution

Change the procedure to this (suggestion below might work as well, but I have not yet tested):

UPDATE      dbo.tblOrderHead
SET         dbo.tblOrderHead.intCustomerNo   = @intCustomerNo
WHERE       dbo.tblOrderHead.intOrderNo      = @intDocumentNo;


UPDATE      dbo.tblOrderHead
SET         dbo.tblOrderHead.intPaymentCode  = dbo.tblCustomer.intPaymentCode,
            dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode,
            dbo.tblOrderHead.txtRegionCode   = dbo.tblCustomer.txtRegionCode,
            dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode,
            dbo.tblOrderHead.txtLanguageCode = dbo.tblCustomer.txtLanguageCode
FROM        dbo.tblOrderHead
INNER JOIN  dbo.tblCustomer ON dbo.tblOrderHead.intCustomerNo = 
            dbo.tblCustomer.intCustomerNo
AND         dbo.tblOrderHead.intOrderNo = @intDocumentNo

Answer 1

You could try creating Parameter objects, then appending them to the Parameters collection.

The following is untested.

Private Sub intCustomerNo_Click()

Dim cmdCommand As New ADODB.Command
Dim paramDocNo as ADODB.Parameter
Dim paramCustNo as ADODB.Parameter

Set paramDocNo = cmdCommand.CreateParameter("@intDocumentNo", adInteger, adParamInput)
Set paramCustNo = cmdCommand.CreateParameter("@intCustomerNo", adInteger, adParamInput)

cmdCommand.Parameters.Append paramDocNo
cmdCommand.Parameters.Append paramCustNo

paramDocNo.Value = Forms![frmSalesOrder].[IntOrderNo]
paramCustNo.Value = Me![intCustomerNo]

With cmdCommand
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "uspSelectCustomer"
    .Execute
End With

DoCmd.Close
Forms![frmSalesOrder].Requery

End Sub

Answer 2

Could it possibly but you not declaring the parameters and it using an old cached copy? Personally when I issue parameters I use something like this. I’m not saying that is what is causing the problem but try it this way and see if it helps

Set cmd = New ADODB.Command
With cmd
    .CommandText = "sptblTest_answers_UPSERT"
    .CommandType = adCmdStoredProc
    .ActiveConnection = dbCon

    .Parameters.Append .CreateParameter("@Answer_ID", adInteger, adParamInput, , Me.txtAnswer_ID)

    .Parameters.Append .CreateParameter("@Question_ID", adInteger, adParamInput, , Me.txtQuestion_ID)

    .Parameters.Append .CreateParameter("@Answer_number", adTinyInt, adParamInput, , Me.txtAnswer_number)


    .Execute
End with