Problem with passing parameters to SQL procedure using VBA

Question

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

SchmitzIT · Accepted Answer · 2012-10-21 09:02:29Z

1

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.

edited Oct 21, 2012 at 9:02

SchmitzIT

9,74912 gold badges71 silver badges96 bronze badges

answered Jun 8, 2012 at 15:36

m biber

111 bronze badge

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

Comments

Gary Kerr · Accepted Answer · 2010-04-11 17:52:26Z

0

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

answered Apr 11, 2010 at 17:52

Gary Kerr

14.6k4 gold badges52 silver badges52 bronze badges

1 Comment

unitario Over a year ago

Hi, and thank you for your reply. I don't know how but I managed to solve it by splitting the update statement into two, and where the first one only updates the customer number.

Kevin Ross · Accepted Answer · 2010-04-12 07:36:45Z

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

Collectives™ on Stack Overflow

Problem with passing parameters to SQL procedure using VBA

Solution

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Solution

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related