UPDATE Access Stored Query Driving me nuts

[Swi]

I am running the following stored query:

UPDATE Master SET JobNum = [@revJobNum], JobName = [@revJobName], Programmer = [@revProgrammer], MailDate = [@revMailDate], RevisedDate = [@newRevisedDate]
WHERE [JobID]=[@curJobID];

It runs fine in Access and updates the record perfectly.

Here is the code in VB6 that I use to invoke the stored query but it does not update the record and I do not receive any error. I have done similar things like this in the past and have not had issues. Any ideas?

Private Sub UpdateJobID()
    Dim cmd As ADODB.Command
    Dim prmCurJobID As ADODB.Parameter
    Dim prmRevJobNum As ADODB.Parameter
    Dim prmRevJobName As ADODB.Parameter
    Dim prmRevProgrammer As ADODB.Parameter
    Dim prmRevMailDate As ADODB.Parameter
    Dim prmNewRevisedDate As ADODB.Parameter
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = "sp_UpdateProc"
        Set prmCurJobID = .CreateParameter("@curJobID", adNumeric, adParamInput, Len(Trim$(JobID)), JobID)
        Set prmRevJobNum = .CreateParameter("@revJobNum", adVarChar, adParamInput, Len(txtJobNum.Text), Trim$(txtJobNum.Text))
        Set prmRevJobName = .CreateParameter("@revJobName", adVarChar, adParamInput, Len(txtJobName.Text), Trim$(txtJobName.Text))
        Set prmRevProgrammer = .CreateParameter("@revProgrammer", adVarChar, adParamInput, Len(cboProgrammer.Text), Trim$(cboProgrammer.Text))
        Set prmRevMailDate = .CreateParameter("@revMailDate", adDate, adParamInput, Len(dtMailDate.Value), Trim$(dtMailDate.Value))
        Set prmNewRevisedDate = .CreateParameter("@newRevisedDate", adDBTimeStamp, adParamInput, Len(Now), Now)
        .Parameters.Refresh
        .Parameters.Append prmCurJobID
        .Parameters.Append prmRevJobNum
        .Parameters.Append prmRevJobName
        .Parameters.Append prmRevProgrammer
        .Parameters.Append prmRevMailDate
        .Parameters.Append prmNewRevisedDate
        .Execute , , adExecuteNoRecords
    End With
    Set prmCurJobID = Nothing
    Set prmRevJobNum = Nothing
    Set prmRevJobName = Nothing
    Set prmRevProgrammer = Nothing
    Set prmRevMailDate = Nothing
    Set prmNewRevisedDate = Nothing
    Set cmd = Nothing
End Sub

Swi

 

[Swi]

I changed the following and it worked like a charm.

Originally:

        .Parameters.Refresh
        [red].Parameters.Append prmCurJobID[/red]
        .Parameters.Append prmRevJobNum
        .Parameters.Append prmRevJobName
        .Parameters.Append prmRevProgrammer
        .Parameters.Append prmRevMailDate
        .Parameters.Append prmNewRevisedDate

Now:

        .Parameters.Refresh
        .Parameters.Append prmRevJobNum
        .Parameters.Append prmRevJobName
        .Parameters.Append prmRevProgrammer
        .Parameters.Append prmRevMailDate
        .Parameters.Append prmNewRevisedDate
        [red].Parameters.Append prmCurJobID[/red]

It seems as though the order of the parameters has to match up with the order in the SQL statement in Access. It doesn't make much sense to me though.

Swi