Hi all. I've created a little web app that will take CSV files of certificate nominations, enter them into a database ready for printing out by the office. Now it all works, the only problem is that if the person entering the data can't spell their subject correctly or can't remember what year tutor groups are in or even spell them right (no bitterness at all I'm sure you can notice) of course the page will throw up an ADO error and stop importing, leaving some of the entered lines there but not all.
I have a rough idea how to trap errors, I have a rough idea on how to show teh error and give them a clue about what they have done wrong. My poor little brain can not seem to work out if using an Access DB will an ASP page be able to either roll back the DB or stop the data being entered if an error is generated?
I think it isn't possible as Access doesn't do roll backs and as the error is generated as teh data is being entered you can't stop the previously entered data being there. But confirmation or other ideas are always welcome.
Last edited by TechMonkey; 2nd April 2008 at 01:15 PM.
You could try importing the data into a new table (a temporary one created in your code).
Watch for errors during import, if there are any you could show a message to the user informing them and drop the new table.
If there are no errors, a second routine could then read the new table and copy the data to the main table.
If the web app is available to more than one person at a time and it is possible to run two imports at once, make sure the temp tables are named using either session data or some random characters so each user gets their own temporary table.
All this sounds like a lot of work but from what you said, you have most of what you need in place already.
I'm not 100% on whether you can make access do roll backs, I don't think you can as it is not fully transaction based. I have a couple of suggestions though. Option one keeping your current system you could add another table into your database with the exact same layout as the final one, import your data to here first which will tell you if you have an error or not, if the ASP catches an error it simply deletes the contents of this table and shows up the error to the user. If it succeeds you can either transfer the rows across to the main table or do another import right into the main table then kill the data in the transfer table.
Option two would be doing the validation client side by having the users enter the certificates into a nice teacher resistant form with drop downs for subjects and students etc.
I guess option three would be using SQL express 2k5 instead as this is quite good but it depends on your current setup and needs.