0

I am deleting entries in table using Connection object Execute() function. I am unable to get numbers of rows affected. Could someone help?

Dim db As ADODB.Connection
db = OpenDatabase(ConnectionString)
deleteExams = "delete from  [Table2] where  [PlanDate] < '" & sDate & "'"
db.Execute(deleteExams)  
db.Close()
Karthik Karnam
  • 170
  • 11
  • The requirement is I must use ADO Connection Object with Execute() method :( – Karthik Karnam Jun 30 '22 at 09:21
  • If you're using MS SQL server, the [@@ROWCOUNT](https://learn.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-ver16) server function is also worth a look. MS SQL also has a [special table ```deleted```](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver16). – Hel O'Ween Jun 30 '22 at 10:12

1 Answers1

0

the ADIDB execte method has a affected rows option see https://learn.microsoft.com/de-de/sql/ado/reference/ado-api/execute-method-ado-connection?view=sql-server-ver16

    Dim ObjAffectedrows As New Object
    db.Execute(deleteExams, ObjAffectedrows)
    Dim LongAfeected_Rows As Long = CLng(ObjAffectedrows)
    Dim LongAfeectedRows As Long
    Long.TryParse(ObjAffectedrows.ToString, LongAfeectedRows)
nbk
  • 45,398
  • 8
  • 30
  • 47
  • I have set option strict on, and also Execute function is accepting object datatype. Syntax: Execute(CommandText As String, ByRef Optional RecordsAffected As Object = Nothing, Optional Options As Integer = -1) As Recordset – Karthik Karnam Jun 30 '22 at 09:46
  • the linked documentation says that it is an Long Variable, like my code has, so it shouldn't be a problem. Or are you getting an error? – nbk Jun 30 '22 at 10:10
  • Ya , I couldn't pass long variable as variable @nbk .... – Karthik Karnam Jun 30 '22 at 10:53
  • i add a strict on Version, where convert the object into a long afterwards – nbk Jun 30 '22 at 11:04