Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Dec 2006
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    ADO SQL and Access record Deletion question

    Hello everyone,

    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.

    I need to work out how to give each record a unique identifier (normally this would obviously be a record number). Sometimes however, the record number is not included in the query. This means that I have no way to delete only the one record without risking other records that may have similar information inside them. The queries are built on user input and are very basic, they consist of a few select boxes containing the field names, operators and values etc. Its done through Javascript so if I need to post in that forum let me know, however, because its more databasey I thought I would try here first.

    Any help would be much appreciated.

    Thanks
    Joe
    http://www.Kanotix.com --My OS of choice, the best hardware detection on the planet!-
    http://distrowatch.com -Linux is the future-
    http://www.packetstormsecurity.org -Security Gurus-

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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...
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New Coder
    Join Date
    Dec 2006
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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:

    Code:
    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.

    Thanks
    Joe
    http://www.Kanotix.com --My OS of choice, the best hardware detection on the planet!-
    http://distrowatch.com -Linux is the future-
    http://www.packetstormsecurity.org -Security Gurus-

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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...

    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • Users who have thanked Daemonspyre for this post:

    LFCFan (10-08-2007)

  • #5
    New Coder
    Join Date
    Dec 2006
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Daemon,

    "This may have just been beyond the scope of the original design."

    ^Understatement 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.

    Joe
    Last edited by LFCFan; 10-08-2007 at 06:07 PM.
    http://www.Kanotix.com --My OS of choice, the best hardware detection on the planet!-
    http://distrowatch.com -Linux is the future-
    http://www.packetstormsecurity.org -Security Gurus-

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.

    FOR EXAMPLE:

    Code:
    <input type="button" name="B1" value="Delete Record" onclick="document.location.href='YourDeleteScript.asp?RecordID=<%= record_id %>';" />
    OR

    Code:
    <a href="formpage.asp?Delete=<%= record_id%>">Delete this record?</a>
    You are very welcome!
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •