Delete.row not work well, why?

Getting Started
1

Here I am again. I would like to understand why in this cycle the records of the tables are not deleted … I have a main table IntestazioneFatturaAcqusiti where there is an ID, with this ID I connect to another table CorpoFattureAcquisti where it is allocated to recognize all the articles belonging to the main table. I would like to delete the main ID and consequently, delete on the second table all the IDs connected to the first … but it only deletes 1.

rs = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti WHERE "+DenominazioneCampoDataNellaTabellaDB+" >= " +txtStart +" and "+DenominazioneCampoDataNellaTabellaDB+" <"+txtEnd)
While Not rs.AfterLastRow 
  rs2 = db.SelectSQL("SELECT * FROM CorpoFatturaAcquisti where Id_IntestazioneFattura=" + rs.Column("ID").StringValue)
  While Not rs2.AfterLastRow 
    rs2.RemoveRow
    rs2.MoveToNextRow
  wend
  rs.RemoveRow
  rs.MoveToNextRow
wend
2

It is very likely that the “rs.RemoveRow” is changing your location within the recordset.

3

Try something like this:

Var aKeys() as String

rs = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti WHERE "+DenominazioneCampoDataNellaTabellaDB+" >= " +txtStart +" and "+DenominazioneCampoDataNellaTabellaDB+" <"+txtEnd)
While Not rs.AfterLastRow 
   aKeys.Add rs.Column("ID").StringValue // Remember the ID's you want to remove
Wend

For each cKey as String in aKeys
   // Remove the secondary records
   db.ExecuteSQL( "Delete FROM CorpoFatturaAcquisti where Id_IntestazioneFattura=" + cKey )
   // Then the primary ones
   db.ExecuteSQL( "Delete FROM IntestazioneFatturaAcquisti WHERE ID=" + cKey )
Next
4

or delete in once where id in (1,2,3)
and encapsulate in transactions if you modify multiple tables and rows, if one fail you can redo.

1 Like
5

Sounds like a great idea. I’ll try it right away. Thanks.

6

Hi IAN
I’m trying your code, but it goes in LOOP.
I don’t understand where the problem could be.
Remains in the first loop, does not exit While Not rs.AfterLastRow

7

Ok,I think I found the problem.

While Not rs.AfterLastRow 
  aKeys.Add rs.Column("ID").StringValue // Remember the ID's you want to remove
  messagebox(" I FIND: " + rs.Column("ID").StringValue)
  rs.MoveToNextRow
Wend
1 Like