Update Client Status on Long Running Job

Every once in a while I come across a question like: "I have a web application that has a long running job. It could take up to a few dozen minutes to complete. How can I keep the client status updated and not causing a timeout?"

The problem with a web application like this is that if the job run too long, it will cause a script timeout error. One can opt to increase the script timeout to prevent this but when you actually have an error, it would take forever before the client is notified of the error.

People often think that AJAX is the only solution to this problem but it isn't. But with a little clever coding, combining javascript with server side code, you can accomplish this. You can apply this technique to classic ASP or ASP.NET or whatever techonolgy or language you choose to use.

What we need to do is find a way to continuously sending data to the browser so that it doesn't time out on us. with vbscript and vb.net Repsonse.Buffer & Response.Flush can take care of this quite nicely.

So now we have the data streaming to the client, how to we update the status? The answer is javascript. We can script a function and take advantage of the ability to dynamically changing the content of the div or span tag to update the status when we receive some data from the server.

Please examine the source code for the complete example.

Escape SQL strings

As part of my development job, I often create and troubleshoot SQL queries. And one of such tasks is validating SQL data to make sure the query would return the correct data set as expected.

Time and again, I would run into queries like:

Select blah blah From Table1 Where FieldName In ('abc', 'efd'.....)

Dealing with a short list doesn't involve a lot of typing so it's not a big deal. But when facing a large list, this can be a daunting task as you have to escape the strings and separate them by commas.

This is where Excel spreadsheet comes in handy. In Excel you can do a simple formula to take care of this by typing the following in a cell:

="'" & A1 & "',"

What this does is it takes the value in cell A1 and wrapped it with a single quote and then adds a comma at the end. You can replicate this formula to other cells and it should save you quite a bit of typing.

If you have a list of strings like:

abc
edf
123

all you have to do is copy and paste it in the Excel spreadsheet created with the formula above and you will have a list of escape strings that you can place in your IN criteria. I use this a lot and it saves me a whole lot of typing.

Don't forget to take out the last comma. See attached file for more info.