In a database I have, I want to delete all entries with a DATACUSTOMER name of Idle.
Here is the code:
[code]Dim siteRS as RecordSet
'dim z as Integer
dim wipesql as String
sql = "SELECT * FROM ACTIVITY Where DATACUSTOMER = ‘Idle’ "
wipesql = "DELETE FROM ACTIVITY Where DATACUSTOMER = ‘Idle’ "
siteRS = DB.SqlSelect(sql)
if DBError then Return
'Get Each entry in the database and put it in the listbox
if siteRS <> NIL Then
while not siteRS.EOF
DB.sqlexecute(wipesql)
wend
else
MessageBox(“Nothing to Delete”)
siteRS.Close
end if
MessageBox (“Done”)[/code]
When I step through the method, I never exit. However if I close the program and then reopen it, the Idle fields are removed. The project never gets out of the loop.
if siteRS <> NIL and (not siteRS.EOF) Then
DB.sqlexecute(wipesql)
else
MessageBox("Nothing to Delete")
siteRS.Close
end if
Since you are not interating through siteRS, it will never be at EOF - that’s why you get the endless-loop
I think that Jurg’s point is that the sql statement wipesql = "DELETE FROM ACTIVITY Where DATACUSTOMER = 'Idle' " does the work in one step. Iterating through the "SELECT * FROM ACTIVITY Where DATACUSTOMER = 'Idle' " recordset just does it repeatedly, where once was sufficient.
In clear, if you have 10 records where DATACUSTOMER = ‘Idle’, the delete command will wipe them all in one step. Iterating through the select recordset will issue the command 9 times too many. His code is definitely more optimized.
