Problem with passing parameters to SQL procedure using VBA

I am trying to pass @i开发者_Go百科ntDocumentNo 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

Try using SET NOCOUNT OFF in your SQL sp.

When an update or insert runs, it returns information on the amount of rows affected (like when you run in SSMS).

When an adodb.command recieves this information it stops executing, hence only executing your first statement.

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

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