Saturday, April 17, 2010

Wednesday, September 23, 2009

Using Variabled TOP Select Statements

After a day of trying to figure out how to get a stored procedure to work for me, I ran across something that makes my life a heck of a lot easier. I always assumed this didn't work because I could never figure out the syntax, but luckily it does! Ever need to select a certain number of rows based off a variable? You can use the following to do just that:

SELECT TOP (@top) * FROM <table>
Now, if you need a certain number of random rows you can also use the following:

Note: This is only applicable in SQL Server 2005+. 2000 you need to use dynamic SQL or a subselect.

Monday, September 14, 2009

How To Fix: AJAX Isn't Returning Database Changes Without Clearing The Cache

I've recently been delving into AJAX, getting away from MicroSoft's built in framework/library for Visual Studio. First, two one complaints about AJAX:
  • What a pain to have to have all different files for every AJAX call. Why can't we do methods in one big file?!
  • Why are we still calling on ASP or PHP files? In Windows Vista/7, you have to specially set up IIS to work well with these files. We should be hitting class files directly or aspx files. Get with the times!
Now that that's over, let's move on.

I had a list of recipes that when the user scrolled over the recipe name I wanted a little preview of the recipe image to pop up next to it (hence the previous post). The AJAX was working fine, but I noticed that when I changed an image file name in the database and then reloaded the page, the new image didn't reflect for that recipe until I cleared my cache. After a day of struggling, I found a post (which I lost the URL in my taste of victory) that cleared things up for me somewhat.

The browsers seem to cache the URL, which holds the ID of the recipe I want the image for, and then returns what it expects the database to return. Dastardly! So, to avoid this, I simply added another variable to the URL that holds JavaScript's Date.getTime() value, or the value of milliseconds since like 1/1/1901. Therefore, it's always changing and will always return the correct image!

var currDate = new Date();
var url = 'directory/aspPageName.asp?var1=' + recipeID + '&rdm=' + currDate.getTime();

UPDATE: After talking to a colleague, I found out you can in fact hit an ASPX page! Just put the code in the page load sub and nothing in the html part. Awesome! However, my first gripe still stands, though he pointed out you can pass a parameter that the page load then reads and directs to another sub. Not bad, but not perfect.