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



Fri, 04 Oct 2002 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Returning Recordset and Return Value from Command Object

2. CDO: Parent property always returns session object

3. Recordset of a command object is always read-only (ADO, VC++)

4. The resulting recordset of a command object execution is always read-only

5. Unable to return float, always returning int with ADO, IIS and SQLServer

6. How to show the return records?(by command)

7. Return bookmark for new record when using INSERT INTO command

8. Return Value from command object using Data Environment

9. Returning Recordset from Command object

10. disconnect recordsets returned by command objects

11. ado command object not returning count

12. Command Object (Prepared) returning a Recordset

 

 
Powered by phpBB® Forum Software