Using ADODB.Command to delete records in a SQL database

[milesg]

How do I obtain the number of records deleted by the following code extract? (assuming sql_query_1 contains a valid SQL DELETE Statement)
   RS = CreateObject("ADODB.Command")
   RS.ActiveConnection = DB
   RS.CommandType = 1
   RS.CommandText = sql_query_1
   RS.Execute

Thanks in advance for your help.

[td]

Can't say that I have ever used it on a delete operation but based on the information provided by the WIL Type Viewer,  try something like the following:

Code (winbatch) Select Expand


RecordsAffected = 0
RS.Execute(RecordsAffected)
Message("Records Affected", RecordsAffected)


[milesg]

I tried exactly that the ADODB.Execute cmd complains about the type being incorrect

[td]

Then you need to specify the type information in the standard way.  You might want to consider specify the name of the parameter since it is optional and just to be safe pass the query directly into the method instead of using a property.

Code (winbatch) Select Expand


nAffected = 0
RS.Execute(sql_query_1, :: i4:RecordsAffected=nAffected)


COM Automation named and typed parameters are explained by examples in the COM help file section of the Consolidated WIL Help file. 

[milesg]

Thanks for the help, I appear to be moving in the right direction now.

[milesg]

I have tested this call process using a SQL DELETE, SQL UPDATE and SQL INSERT and the .Execute process always returns a 0 value. I know that my SQL transactions are in fact actually INSERTING, DELETING or UPDATINF records correctly, as I have tested them outside of WINBATCH.
I set the RecordsAffected variable to 99 prior to the call and it is returned as 0 by the .Execute function so I know that the call is setting it.
Could the ADODB Command.Execute call have a problem in WINBATCH and the RecordsAffected value is not being set correctly upon return?

[td]

It is very doubtful that 'ADODB Command.Execute call have a problem in WINBATCH'.  There are many layers of software between the database engine and WinBatch. Not all features are made available by all drivers/providers and this is a common problem reported by users of COM Automation clients other than WinBatch.

[milesg]

Thanks, I had to ask the question.

[stanl]

Have you considered using Connection.Execute rather than Command.Execute?

[td]

Also note that some databases support turning record counting on and off via an SQL command and may be configured to default to the off state for performance reasons.  But the possibly is of course, source engine dependent.