Deleting all rows with a certain value

[woodyinoz]

Hi all,

I have a table which holds a field called "GRADE". I want to delete all rows in the table with a grade "DUMMY".
I have tried to use the code below which I adapted from a script that delets all blank rows in the table but it doesn't seem to work in this instance.

method run(var eventInfo Event)

var

matGrade tCursor
searchName string

endvar

;// Delete dummy grades
;//
searchName = "DUMMY"
matGrade.open("artManartReport.db&quot
matGrade.edit()
if matGrade.locate("GRADE", searchName) then
if matGrade."GRADE" = "DUMMY" then
matGrade.deleteRecord() ; delete the active record
message(searchName + " deleted&quot
endIf
endIf

endmethod

Has anyone got any ideas upon either where I'm going wrong or a different way of approaching the problem?

Thanks,

Woody.

 

[Langley]

Why don't you just do a delete query. It's fast and simple.

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[woodyinoz]

Thanks for the suggestion Mac but what do you mean by a delete query?

 

[Langley]

Sorry... A delete query is a query that deletes all records in a table that meet certain criteria. For example:

var

searchName    string
qVar          query

endvar


searchName = "Dummy"

qVar = Query

:Partman:Partreport.db |Grade       |
Delete                 |~searchName |

endQuery


if not qVar.executeQBE()
     then errorshow()
endif

This would remove all records with a grade of "Dummy"

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[Bystander]

Woody,

Langley is right the simplest way is the Delete Query.

However, having looked at your initial approach may I hazard a guess that using Locate will only find one row and delete it, the next step would have been to use

while matGrade.locateNext("GRADE", searchName) etc

to force your code to find more instances of "DUMMY".

The down side is that this might not necessarily delete all rows in one pass. Depending on the number of "DUMMY" rows it might first delete half of them then when it runs again delete half of the remainder etc until there are none left.

With examples like your one above it is always a good idea to make sure there is some instruction in the code to allow iteration or looping through the whole of the table or record set in question.

Regards

Bystander