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?

Answer 1

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.