
Command Object, Records Affected always returns 0
Hey,
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