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.

Wednesday, September 09, 2009

Getting The Pixel Width And Height Of A String

I've recently been making an application that required a popup image to dynamically appear beside text, which is variable in length. Since I always want the image to show up 15px to the right of the text, I needed to get the width of the string. What I could do (if you are positive of the font face it's show in) is take the average width of each character, get the number of characters and get the width that way. However, you throw in a few "..." or move between browsers that have differeing rendering for the same font and you may be SOL depending on your application.

I was looking around the web for a solution and found the following article. The article had an example with an existing TD, however, what you're doing, and what I did, may not be in a TD. So I came up with the following to make it dynamic:

function GetStringPixelWidth(str)
var elem = document.createElement('SPAN');
elem.innerHTML = str; = 'hidden';
var w = elem.offsetWidth;
return w;

One must append it to the document so the offsetWidth is actually calculated. I added it to the body, but you can add it to the parentElement that str is coming from so you can get the width of wrapping text.

UPDATE: I found that in Mozilla FireFox, adding the TD doesn't work. In light of this, I've updated the code to instead add a SPAN element. I'm thinking FF doesn't know how to handle adding a single TD into the document; it kept returning 2 as the offsetWidth. It didn't matter if I created TABLE and TR elements and nested them. Always 2. With the span, it's still a dynamic width, unlike DIVs, and can be independently added to the page with no issues.