Android Question Deleted SQL records are not Actually Deleted

[Pelky]

I am hoping someone can assist me with this. The application is rather simple in that it records driver information/stats on a weekly basis and allows for monthly analysis over several months. When it starts up it checks for records that are old and no longer relevant - these are deleted.
You will see that it does a count before and after the delete. I get no errors and the logs show the dates correctly but the record count at the end is the same as before. I also don't get a "failed to delete" log.
I thank you in advance for any suggestions

Dim RemovalCursor As ResultSet
    
    RemovalCursor = DB.ExecQuery("SELECT * FROM TotRecord Order By TotDate")
    Log("Total Records for deletion Check = "&RemovalCursor.RowCount)
    
    Do While RemovalCursor.NextRow
        Dim RecordDated As String = RemovalCursor.GetString("TotDate")
        If RecordDated < RemovalDate Then
           Log(DateTime.Date(RecordDated)&" Older Than "&DateTime.Date(RemovalDate)&" Will Be deleted")
            Try
                DB.ExecNonQuery2("Delete From LogRecord where LogDate = ?", Array As Object(RecordDated))
            Catch
                Log("Failed to delete Record")
            End Try
        End If
    Loop
    RemovalCursor.Close
    
' Count Remaining Records
    Dim RemovalCursor As ResultSet
    RemovalCursor = DB.ExecQuery("SELECT * FROM TotRecord Order By TotDate")
    Log("Total Records After Deletion = "&RemovalCursor.RowCount)
    RemovalCursor.Close
' End Count

 

[aeric]

How about add the execute query into a batch first or a list then only execute them after you close the first resultset?

 

[LucaMs]

The fact that no errors are reported does not mean that the records were deleted, but only that, in this case, no records matching the "Where" clause were found.

You don't need all that "Do While" loop to delete those records, the following line is sufficient (and necessary):

DB.ExecNonQuery2("Delete From LogRecord where LogDate = ?", Array As Object(RecordDated))

Check the number of records that meet that condition, before and after deletion:

Dim NumOfRecs As Int
NumOfRecs = DB.ExecQuerySingleResult2("SELECT COUNT(*) FROM LogRecord WHERE LogDate = ?", Array As Object(RecordDated))
Log(NumOfRecs)

 

[Pelky]

Thank you Lucas but surely DB.ExecNonQuery2("Delete From LogRecord where LogDate = ?", Array As Object(RecordDated)) will only delete the one record. There are a number of records and the loop is there to check each record in the database and only those older than RecordDated
are deleted.
Sorry maybe I wasn't clear before.

 

[LucaMs]

Thank you Lucas but surely DB.ExecNonQuery2("Delete From LogRecord where LogDate = ?", Array As Object(RecordDated)) will only delete the one record.

You're right, reading quickly I thought you were past the deletion date (It happens when you've been awake for a short time )

 

[Pelky]

sorry Lucas but I meant to say only records older than the RemovalDate are to be removed.

 

[LucaMs]

But the point is that since you don't get any errors, you think that some records have been deleted but the total number of records remains unchanged. In reality the query has not deleted any records and rightly does not generate errors.
Check the dates (and their formats) carefully.

 

[LucaMs]

sorry Lucas but I meant to say only records older than the RemovalDate are to be removed.

Yes, you explained it well, I looked at your code with at least one eye closed (and all the neurons turned off )

 

[aeric]

If RecordDated < RemovalDate Then

Are the date stored as Long integers? Why did you compare them using String?

Luca is right that you don't need to query and delete it one by one.
You can do a SELECT query if you want to know is there any rows matched the condition but this is optional step.

The real query is just 1.

DB.ExecNonQuery2("DELETE FROM LogRecord WHERE LogDate < ?", Array As Object(RemovalDate))

 

[alwaysbusy]

you do the select in the TotRecord table and the delete in the LogRecord table

 

[Pelky]

I did do that using the log to show me the actual detail held. Surely if it has entered into the 'IF' and shown me that the date, Extracted from the ResultSet, is older (correctly in the log) then by using the same criteria the delete should work.

 

[Pelky]

you do the select in the TotRecord table and the delete in the LogRecord table

Thank You so much ... Sometimes you cant see the wood for the trees. I feel totally stupid....
Maybe it just takes a new set of eyes

 

[Pelky]

I seem to have found my error with the the assistance of the forum. You are all heroes

Thank you so very much