I have a command object which at the moment always returns 0, this was a bit
confusing at first because I spent a day trying to find out why my update
command object was not working, then I figured I better go check and lo and
behold it was working, it was just always returning 0 records affected.  I
have another snippet of code, which I knocked up to test what I was doing
and it always returns the correct number of records affected and basically I
cant spot the difference between the 2.  However, this is the only
difference which I cant explain or expect this to be the problem.

The code which does not work, does things this way.

I have an object which when created, creates and associated ADO.Command
object, the code initilizes the command text, parameters etc, however the
active connection is set by the following method;

The code below is called by another object which passes the ADODB.Connection
object by referencing the ActiveConnection property of a ADODB.Recordset
object;

Public Sub Initilize(sDestTable As String, sPrimaryKey As String, adCon As
ADODB.Connection)

    Dim OField As CField
    Dim sCmd As String
    Dim aPrm As ADODB.Parameter

    Set adCmd = New ADODB.Command
    Select Case CommandType
        Case "U"
            sCmd = "UPDATE " & sDestTable & " SET "
            adCmd.CommandType = adCmdText
            adCmd.Prepared = True
            adCmd.ActiveConnection = adCon
            For Each OField In coFields
                sCmd = sCmd & OField.FieldDest & " = ?, "
                Select Case OField.FieldType
                    Case adVarChar, adChar
                        Set aPrm =
adCmd.CreateParameter(IIf(OField.FieldAlias & "" <> "", OField.FieldAlias,
OField.FieldDest), OField.FieldType, adParamInput, OField.FieldSize, "")
                    Case adDate
                        Set aPrm =
adCmd.CreateParameter(IIf(OField.FieldAlias & "" <> "", OField.FieldAlias,
OField.FieldDest), adVarChar, adParamInput, OField.FieldSize, "")
                    Case Else
                        If IsNull(OField.FieldSize) Then
                            Set aPrm =
adCmd.CreateParameter(IIf(OField.FieldAlias & "" <> "", OField.FieldAlias,
OField.FieldDest), OField.FieldType, adParamInput, , 0)
                        Else
                            Set aPrm =
adCmd.CreateParameter(IIf(OField.FieldAlias & "" <> "", OField.FieldAlias,
OField.FieldDest), OField.FieldType, adParamInput, OField.FieldSize, 0)
                            aPrm.Precision = OField.FieldPrecision
                            aPrm.NumericScale = OField.FieldScale
                        End If
                End Select
                adCmd.Parameters.Append aPrm
                Set aPrm = Nothing
            Next
            sCmd = Left$(sCmd, Len(sCmd) - 2)
            sCmd = sCmd & " WHERE " & sPrimaryKey & " = ?"
            adCmd.CommandText = sCmd
            Set aPrm = adCmd.CreateParameter(sPrimaryKey, adVarChar,
adParamInput, 20, "")
            adCmd.Parameters.Append aPrm

    End Select

End Sub

This is basically the only difference between my code which does work and
the code which does not work, and I cant figure out why using the above
method when I set the parameters and call the execute method of the command
object, I always get 0 affected records, yet the query did actually do its
job.

Any help, most appreciated!

regards,
Michael Pine