| Author |
 Topic Search  Topic Options
|
pedalcars 
Senior Member
Joined: 12 August 2002
Location: United Kingdom
Status: Offline
Points: 268
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Delaying tactics
Posted: 25 November 2003 at 10:21am |
|
I have an action file that, when requested in delete mode, deletes the selected record from a table, using:
strSQL = DELETE tblTable.* FROM tblTable WHERE ...(etc);
adoCon.execute(strSQL)
No problems there; however I then want to check tblTable to see if it is now empty, or whether it still contains records.
However, if I then use a select statement;
strSQL = SELECT tblTable.* FROM tblTable;
rsCheckTable.Open strSQL, strCon
If rsCheckTable.EOF Then
'(Do whatever)
End If
the Select statement never reports EOF; even if the deleted record was the only one in the table, it finds (and reports, if I ask it to) that record.
So, what I need is some way of finding if the table is empty after the chosen record has been deleted. Either a way of delaying the script while the (Access) database updates, so the later select statement can return EOF if applicable, or some other way of doing this.
Any suggestions?
Thanks in advance.
|
|
|
 |
MorningZ
Senior Member
Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
|
Post Options
Thanks(0)
Quote Reply
Posted: 25 November 2003 at 2:02pm |
first off, this is wrong syntax:
strSQL = "DELETE tblTable.* FROM tblTable WHERE ...(etc);
it's this:
strSQL = "DELETE FROM tblTable WHERE ...(etc);
As for "if the table is empty":
strSQL = "SELECT COUNT(*) FROM tblTable"
rsCheckTable.Open strSQL, strCon '<<== also wrong, this should be a connection object, not a string
If rsCheckTable(0) = 0 Then
'Table is empty
Else
'Table is not empty
End If
Edited by MorningZ
|
|
Contribute to the working anarchy we fondly call the Internet
|
|
pedalcars
Senior Member
Joined: 12 August 2002
Location: United Kingdom
Status: Offline
Points: 268
|
Post Options
Thanks(0)
Quote Reply
Posted: 27 November 2003 at 9:15am |
|
Thanks for the corrections.
However, the check is still "finding" the deleted item - I've just been testing it, starting with one record in the table, and running the page. Checking the DB shows the record has indeed been deleted (so there's nothing in the table, at all), but the page still returns the "table not empty" clause:
If rsCheckTable(0) = 0 Then
response.write"empty!"
Else
response.write"not empty!"
End If
I get "not empty!" written each time. If I change the strSQL to a SELECT statement to bring back the record details, it will happily tell me any of them (ID, etc), even though the record was previously deleted in the DELETE statement.
Hence, I still have the original problem!
Ta
|
|
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 27 November 2003 at 9:38pm |
|
Try
If rsCheckTable.EOF Then
response.write"empty!"
Else
response.write"not empty!"
End If
|
|
|
|
pedalcars
Senior Member
Joined: 12 August 2002
Location: United Kingdom
Status: Offline
Points: 268
|
Post Options
Thanks(0)
Quote Reply
Posted: 28 November 2003 at 2:47am |
|
Thanks Ljamal, but that's pretty much where I came in.
I still get "not empty" reported, it's "remembering" the deleted record (yes it was the only record in the table; yes it has been deleted).
So far I've only tested this locally (Win XP / IIS / Access XP), but I can't see it being different on a live server.
Maybe I'll just have to work around the problem a different way!
|
|
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 28 November 2003 at 8:21am |
|
The only thing that would cause the record to still be there is if the delete condition is not being met. Are you positive the where clause of the delete statement is being met?
Try doing a select with the same where clause to see what is returned.
|
|
|
|
pedalcars
Senior Member
Joined: 12 August 2002
Location: United Kingdom
Status: Offline
Points: 268
|
Post Options
Thanks(0)
Quote Reply
Posted: 28 November 2003 at 8:37am |
|
The record is very definitely being deleted, yes. I can have the access table open, see the record is there, run the file, switch back to access, the record fields are now shown as "#deleted". Refresh the table and the record has gone. I've done this with multiple records existing (and it's deleting the correct one) and even with just a single record in the table, which is being deleted to leave nothing at all.
However, I do have a workaround in place now. Basically, when you view the results (from a different page), the app checks to see if said table is empty - it's not quite as efficient or as neat as I'd like, but it works.
|
|
|
|
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 November 2003 at 1:35am |
Why are you checking against the EOF? Some DB setups always have a row active.
I think you need to do it in one of two ways:
- Delete the record as usual, always use ID's (WHERE fldID = delID) then close the connection to the DB, open it again and make the check now.
- Following way no.1, after deleting store the delID on an integer (intDelID) and then check the DB if that intDelID exists.
Shouldn't be a problem.
|
|
