Delete SQL record via ado not working

[RobHVB6Sql]

Hi, I'm hoping this is a simple one.

I am using a record set to grab one record from a working table. When I am done with the recordset ('adors2') I wish to delete the record from the table. (A bit like a to do list).
I recieve no error messages, but the record is not deleted.

Code:
'break connection to working table
adors2.Close
Set adors2 = Nothing

TheSQL = "DELETE FROM " & strWorkingTable & " " + _
"WHERE PersonID = " & lngOriginalPersonID

'execute the row deletion
Set adors = adoOdysseyConn.Execute(TheSQL)
'adoOdysseyConn.CommitTrans 'gives error


Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[Schroeder]

Seems like it should be more like:

TheSQL = "DELETE * FROM " & strWorkingTable & _
    " WHERE PersonID = " & lngOriginalPersonID & ";"
adoOdysseyConn.Execute TheSQL

A delete query doesn't return any records so trying to use it to set a recordset object doesn't work.

 

[kkitt]

Is there anyway to get the count of the number of records that were deleted?

 

[Schroeder]

It looks like you can monitor the connection object's ExecuteComplete event. The first parameter it returns is RecordsAffected.

 

[RobHVB6Sql]

Ok, maybe I'm having a bad day and need to take baby step. Thanks for hints so far its great, deletion is now working. Can you help with the next steps.
1) Want to use Non–recordset-returning Command
2) Want to fire the ExecuteComplete event
* I dont know the variable to send here.
3) Now to access /use adStatus or RecordAffected.
* I cant see/get to these via Object Browser. (Also covers kkit's question).

CODE So far:

Sub DeleteSelectFromWorkingTable(strWorkingTable As String)
' NB: WithEvents keyword used to designate that events can be handled by the Connection object WithEventadoOdysseyConn.

'REF: Declared outside this sub routine
'  Dim WithEvents WithEventsadors3 As ADODB.Recordset
'  Dim WithEventadoOdysseyConn As New ADODB.Connection
   
   Dim lngRecordsAffected As Long
   Dim pError As ADODB.Error
   Dim adStatus As ADODB.EventStatusEnum
   Dim pCommand As ADODB.Command
   Dim pRecordset As ADODB.Recordset
   Dim pConnection As ADODB.Connection

On Error GoTo errDeleteSelectFromWorkingTable

    TheSQL = "DELETE FROM " & strWorkingTable & " " + _
             "WHERE PersonID = " & lngOriginalPersonID & ";"
     
   'open the connection
   Set WithEventadoOdysseyConn = New ADODB.Connection
   WithEventadoOdysseyConn.ConnectionString = "DSN=" & strDSNName & ";UID=odyketest;PWD=" & strPassword & ";"
   WithEventadoOdysseyConn.Open

   'check connection ok
   If Not WithEventadoOdysseyConn.State = adStateOpen Then
         Err.Raise -1, , "This application could not connect to " & strDBName & "." & vbCrLf & vbCrLf & "Error in sub DeleteSelectFromWorkingTable."
    End If
   
    'execute the row deletion (Non–recordset-returning Command)
    'Set WithEventsadors3 = WithEventadoOdysseyConn.Execute(TheSQL)
    WithEventadoOdysseyConn.Execute (TheSQL)
'    WithEventadoOdysseyConn.Execute(TheSQL, lngRecordsAffected)
   
   'prevent subsequent notifications -how to set this?
   'WithEventadoOdysseyConn.Attributes(adStatus) = adStatusUnwantedEvent
   
   'fire the event
   'syntax
   'ExecuteComplete RecordsAffected, pError, adStatus, pCommand, pRecordset, pConnection
   'attempt below -not working
   ExecuteComplete lngRecordsAffected, pError, adStatus, Execute, pRecordset, WithEventadoOdysseyConn
   
   'check if deletion occured, if not raise an error message
   'ADODB.EventStatusEnum = 1 Then ?How to access
   
'   If Not WithEventadoOdysseyConn.State = adStateOpen Then
'         Err.Raise -1, , "This application could not connect to " & strDBName & "." & vbCrLf & vbCrLf & "Error in sub DeleteSelectFromWorkingTable."
'   End If
...
... 
end sub

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)