Creating Batch update in Classic ASP and ADO

Question

I have an ASP form that brings in a number of records that I want to modify and then update. Currently, the updates take place as one-at-a-time update through DLL's that simply build a SQL statement and call the database for each record.

Now, I need to place a check in the SQL to make sure that I'm not getting rid of the last type of location from a given building. Such that I have 5 workplaces and if I tried to turn all of those workplaces into offices, I should get an error saying that I need at least one workplace per floor, and the SQL should end without updating any rows. Currently, it would update each row till the last one, and then throw the error.

Someone pointed out to me that I should try to batch-job the SQL or update, but I don't know how I should go about this. Any hints, please?

Do you mean create an SQL transaction in order to allow a rollback if something goes wrong? Could you detail a bit more the sentence: "to make sure that I'm not getting rid of the last type of location from a given building"? — jdecuyper, Aug 20 '10 at 14:22
I tried to lock it by transaction, but it didn't seem to work. As in, it gave me errors when I tried to update more then one record. What I mean by that statement...I have locations inside of a floor of a building. They have a type (workplace, office, kitchen, ect). Each floor HAS to have at least one workplace, so I have to restrict the user from making changes that would remove the last workplace of a floor. If they try to make a large number of changes and try to submit those changes, and those changes would remove the last workplace, I have to stop ALL of those changes. — CrystalBlue, Aug 20 '10 at 15:32

score 0 · Answer 1 · answered Aug 20 '10 at 16:31

You can chain several commands and throw an error if some business rules fail

Something like (pseudo-code, not tested):

For i = 0 to ubound(Items) - 1
  stmt = stmt + " UPDATE BuildingFloor set Type=" + Items(i).Type + " WHERE ID=" + Items(i).ID + ";"
next
stmt = stmt + " IF NOT EXISTS(SELECT * FROM BuildingFloor"
stmt = stmt + " WHERE Type = 'WorkPlace') RAISERROR ('Must have a workplace', 16, 1);"

If you execute the batch, all the sentences will be run inside a transaction and the DB will not be updated if an error is raised.

Disclaimer: Just an idea, not tested.

Creating Batch update in Classic ASP and ADO

1 Answers1