10-08-2007, 12:06 PM
I have a basic HTML page that queries an Access database through ADO SQL for very simple information. I have everything working except for one thing.
Any help would be much appreciated.
10-08-2007, 03:06 PM
Hey Joe --
When you state that you don't have the UID (Unique IDentifier), do you mean that the record does not have one, or that you do not pull this information in the SELECT?
If the latter, I would simply alter your SELECT statements and add hidden fields where necessary in order to remove the records in question.
May take you about 2 hours to get this up and running correctly, but it will save you days later on when you are trying to restore from backups...
10-08-2007, 04:02 PM
Thanks for getting back to me Daemon.
I can't really do it that way as the queries could be different every time.
A constructed query might look like this:
SELECT Record_Id, Colleague, Employee_Number, Cost_Centre, Amount FROM Incentives WHERE Amount >= 2000000 ORDER BY Record_Id DESC
At which point I can use the Record Id to delete the field as I have the database set not to allow duplicates on this field.
But a query could also look like this:
SELECT Colleague, Employee_Number, Cost_Centre, Amount FROM Incentives WHERE Amount >= 2000000 ORDER BY Employee_Number DESC
At which point I have no way of allowing the deletion of one of the records as Record_Id is not present.
I know you said to use a hidden field but for each record? Seems a bit overkill as there could be thousands of records brought back at a time. I could query Record_ID regardless of what the user asks for, and just display what the user asks for, but the re-coding will take me ages, just wanted to know if there were any shortcuts you could think of.
10-08-2007, 04:47 PM
I hear ya... Re-coding is a drag, but sometimes it is necessary...
My suggestion to use Hidden fields was simply based on not knowing certain aspects, like 1000's of returned records... :o
Couple of thoughts --
1) You may need to select the record_id at all times, and while this may be a drag, would solve your issue every time. FIND and REPLACE will easily fix this, as you can FIND "SELECT " and REPLACE with "SELECT Record_Id, ". Then, do a FIND "SELECT Record_Id, Record_Id, " and REPLACE with "SELECT Record_Id, ".
A bit much, but better than killing yourself typing the same thing over and over again.
2) When using the DELETE, try DELETE FROM Incentives WHERE Colleague = <returned value> AND Employee_Number = <returned value> AND Cost_Centre = <returned value> AND Amount = <returned value>;
That direction would ensure that you get the exact record you want to delete, but you may run into other issues (like duplicates).
If you go this route, you might want to do a "You are going to delete these records: <<display records in question>>. Are you sure you want to do this?"
3) When requesting a DELETE option, do a secondary SELECT that pulls the correct Record_Id first, then runs the DELETE query. Similar to above, slightly more intensive on the server, but would guarantee that you only pull / delete 1 record.
4) Final option - may not be the best ( or truly an option for that matter ) : Remove the ability to delete files from the users hands. Give them the ability to request a delete, that way you can set up a simple page that will delete the records based on an exact search by you (or one of your team).
I'm not sure that there is an easy way to do this, as you have certainly done an excellent job in getting up and running to this point. This may have just been beyond the scope of the original design.
Hope these options help you, and please let me know if I can be of any other assistance.
10-08-2007, 05:49 PM
"This may have just been beyond the scope of the original design."
^Understatement :D I hate hindsight!
Anyway to business. I've decided to try the extra query route. That is, every time the user runs a query, I'll store the results of an extra query in the background. The only thing I'm not sure of (and it's probably because I've not thought it through yet), is how to link the stored (hidden) results (containing the Record ID, with the visible results(not containing the Record_Id).
Thanks for all your help Daemon.
10-08-2007, 06:23 PM
Here's a thought ---
On your DELETE button (if you have one, if not, maybe a good idea) -- make the button push the "invisible" ID to your DELETE query.
<input type="button" name="B1" value="Delete Record" onclick="document.location.href='YourDeleteScript.asp?RecordID=<%= record_id %>';" />
<a href="formpage.asp?Delete=<%= record_id%>">Delete this record?</a>
You are very welcome!