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:

SELECT TOP (@top) * FROM <table> ORDER BY NEWID()

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

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.

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;
elem.style.visibility = 'hidden';
document.body.appendChild(elem);
var w = elem.offsetWidth;
document.body.removeChild(elem);
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.

Missing __doPostBack Method On ASP.NET Page

Sometimes, ASP.NET programming has called for a JavaScript call to .NET's JavaScript function __doPostBack. This function exists on (almost) every .aspx page with controls that would create a postback. Once in a while, however, you won't have a control that creates a postback (or for some other odd reason) and the __doPostBack function won't be available. In these situations, you can create your own custom method.

1. Add two hidden fields to hold the postback information. You can add these anywhere on the page.
   
   
   <asp:HiddenField ID="__EVENTTARGET2" runat="server" />
   <asp:HiddenField ID="__EVENTARGUMENT2" runat="server" />
   


2. Create a custom JavaScript __doPostBack method. I often call it __doPostBack2 just incase for some reason the original is created. Don't want to confuse the application. form1 is the form name. It might be ASPForm or something. Best way to find out is to view the source of the page and use that.
   
   
   function __doPostBack2(target,argument)
   {
   

   var f = document.getElementById('form1');

   document.getElementById('__EVENTTARGET2').value = target;

   document.getElementById('__EVENTARGUMENT2').value = argument;

   f.submit();

   }
   
   
   
3. Add the postback function to the element in question.
   
   
   <input id="txt1" type="text" ondblclick="javascript:__doPostBack2(this.id,this.value);" />
   

Next up: Adding custom events to controls.

Finding Programming Answers

Can't figure out a programming problem? Receiving an error message that gives you absolutely no clue what is actually wrong? Let me fill you in on a trade secret: Google it. Google everything. It's your best friend. That's right, copy the error message or a few keywords to describe your situation and slap that SOB into Google.

If that doesn't fix your problem, join a forum. There are plenty of free, useful forums. I like to use both Microsoft's MSDN forum and VBForums. Both have proven to be extremely helpful.

Moving Elements With Javascript

Moving HTML objects, or elements, is a very useful tool and can often replace annoying Flash. In this case, I'll be discussing sliding an element using JavaScript. I recently had to do this quite a bit while helping recreate my girlfriend's online portfolio and I decided to make a quick post about it.

There are two things you need to set up first:

1. If necessary, give an ID to the element you wish to move (this is important if you're not using the element itself to initiate the moving process, which if you are, you can just use the "this" JavaScript keyword to pass a reference to the element).
2. Set the element's CSS position property to absolute so it can be moved. Leaving it as default or relative will create some very frustrating times for you. Though you can set this in JavaScript when you call on the move function, I suggest setting up your page with the element already having this property since setting it at run time can screw up your page layout

Once they have been set up, moving an object really isn't that hard. Basically, you need to move the element a certain number of pixels every X number of milliseconds. Let's say you want an element to move 200 pixels to the right when clicked. You can use the following HTML:

<div style="width:200px;height:200px;background-color:blue;position:absolute;" onclick="javascript:Move(this);">

On click you can now fire this JavaScript function:

var currElem = null;
var intervalID = null;
var moveInterval = 10;
var totalMovement = 200;
var totalMoved = 0;

function Move(elem)
{
currElem = elem;
totalMoved = 0;
intervalID = setInterval('MoveElem()', 10);
}

function MoveElem()
{
if (totalMoved >= totalMovement)
{
cancelInterval(intervalID);
}
else
{
totalMoved += 10;
currElem.style.left = totalMoved + 'px';
}
}

We set the currElem global variable because you can't pass variables to a method using an interval or timer. Then, once the is set, it's just a matter of incrementing the left amount (pushing the element to the right) until it's greater than or equal to the total amount you want it to move. You can then increase or decrease the top to get a diagonal and many other things. Varying the timeout time (in this case 10 milliseconds) and the interval distance will make the movement clunkier or smoother, depending on what you're looking for.

Custom Link In Gridview

One control I try to use as much as possible is the Gridview. Quick, simple and customizable, for it's purpose, to the 'T'. I do wish that the Gridview allowed more customization in the area of display. Several times I've a display that required full width column spans throughout the table for some reason and I've had to resort to building my own HTML and injecting it into a div or literal control. But, when I can, I use the Gridview.

One hold back is that the Gridview has the command buttons, which is Update, Delete, etc, but only if tied to a data source object, which I'm not sure if anyone uses. I often find that I need to put in a "View" linkbutton. But how do we capture the linkbutton postback and then tie that to the correct ID of the row and execute what we need to?

Let's say we have a program that develops tests and we have a Gridview that displays a list of all tests: Name (bound), Date Created (bound), View (item template > link button). Let's name the View linkbutton lnkView for convention-sake. If there's one thing that good security calls for it's almost NEVER showing database IDs. Just not a good idea. So, where do we store the test's ID that we want to view for each row? Easy, in the link button. You can do the following to rename each lnkView to hold the ID of the given test of that row, given that ds is a DataSet holding your data from the database:

Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.DataBound

Dim rowIndex As Integer = 0
For Each gvr As GridViewRow In GridView1.Rows

Dim lnk As LinkButton = CType(gvr.FindControl("lnkView"), LinkButton)
lnk.ID = "lnkView" + ds.Tables(0).Rows(rowIndex)("ID").ToString
rowIndex += 1

Next

End Sub

Now that all of your lnkViews have been renamed, you can now capture it when a user clicks on it. In the page load event, wrapped in a IsPostBack If statement, you can enter this to determine if the lnkView has been clicked and grab the ID of the test that it corresponds to:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If IsPostBack Then

If Request("__EVENTTARGET").ToString.Contains("lnkView") Then

Dim TestID As Integer = CInt(Replace(Request("__EVENTTARGET").ToString, "lnkView", ""))

End If

End If

End Sub

We kept "lnkView" in the lnkView's ID so we can easily decide if the postback target is the link button we're looking for. The Request("__EVENTTARGET") grabs the ID of the control that caused the postback, in this case the lnkView linkbutton. The Request might return the ClientID so it might looks something like:

ctl00$GridView1$ctl02$lnkView181

but you can just parse it.

CSS Z-Index

One CSS rule (as I have come to learn them to be called) that is a pain in the ass is the Z-Index. Why? Because it works the way you want it about 60% of the time every time. Besides the obvious movie reference (Anchorman for anyone not familiar), it's just a confusing rule. Basically, it allows the programmer to stack elements on the Z-Index, as the rule name so aptly describes. So here are some basic guides to Z-Index.

1. You should try any other possible method to get the stacking order you wish before using Z-Index
2. If Z-Index is the only way to go, every stacking element must have the CSS property 'position' set to 'absolute' or 'relative'
3. The lower the Z-Index, the lower in the stack it goes (less visible).

That's about it. Again, I hate Z-Index, but I do find that sometimes you have to use it...

SQL Recursion With A Cursor

Recently, I had to create a SQL recursion to delete nested data. Recursion doesn't come around often, but when it does and you use it correctly, it's so good. Basically, you have a block of code that calls itself. For example, I had to use it because I had nested data, which means that I had a hierarchy of data that related to each other. I had the parent, the Item Type (IT), and then X number of Item Groups (IG). I cannot nest ITs, but I could nest IGs, so I might come up with a structure like so:

-IT
-IG1
-IG2
-IG3
-IG4
-IG5
-IG6
-IT
-IG7
-IG8
-IT

In the database, I have one table for each IG which has the unique field TCSID as the primary key. To show relationships in the table, rather then creating another linking table, I have another column called ParentTCSID, which would contain the parent IG's ID of the given IG. In the example above, that would mean that if IG2's TCSID is 12, then IG3's and IG4's ParentTCSID would be set to 12.

If one were to delete IG2, I would need to delete all children, that includes IG3,4,5,6. If we were only going one level deep, I could just run something like:

DELETE FROM IGTable WHERE ParentTCSID = 12

However, because IG5's ParentTCSID is actually the TCSID of IG4, that won't work.
So, we use recursion. Basically, we build a list of children of IG2, loop through them, delete them, then use the current child's TCSID to call on the same stored procedure to delete all the children for the current child, such as the following:

ALTER PROCEDURE [dbo].[usp_DeleteIG]
@TCSID int
AS
BEGIN
SET NOCOUNT ON;

--generate child list
DECLARE @child TABLE(ID bigint)
INSERT INTO @child
SELECT TCSID FROM IGTable WHERE ParentTCSID = @TCSID

--curs through
DECLARE childCurs CURSOR LOCAL
FOR SELECT ID FROM @child
OPEN childCurs
DECLARE @currID bigint
FETCH NEXT FROM childCurs INTO @currID
WHILE @@FETCH_STATUS = 0
BEGIN
--delete item
DELETE FROM IGTable WHERE TCSID = @currID

--recurs
EXEC usp_DeleteIG @currID

FETCH NEXT FROM childCurs INTO @currID
END
CLOSE childCurs
DEALLOCATE childCurs

--delete group
DELETE FROM IGTable WHERE TCSID = @TCSID
END

Note the red LOCAL in the code. This is the key to the entire post. If you were to run the above without it, you would get an error saying that the cursor childCurs is already open. This is because, by default, cursors are global, so when you open it on the first run then recurse, when you try to open it again, SQL Server already thinks it's open. By putting in the LOCAL keyword, the cursor only exists to the particular instance of the running code, thus allowing multiple cursors of the same name to be open at the same time.

Centering A Page Layout Using CSS

One thing that often stumps new developers is how to center a page layout for a site. It is often over looked by rookies that display changes when you change the window size, and putting a margin-left CSS tag will not show a centered page layout when you increase or decrease the window size. So, in order to have a centered page layout that will work in any browser at any resolution, you can use the following:

<style>
#frame
{
position:absolute;
left:50%;
width:780px;
margin-left:-390px;
}
</style>

<div id="frame">
[page content]
</div>

position:absolute; pulls the div up out of relative positioning, allowing you to place it anywhere. The 'left:50%' puts the left edge of the div in the center of the page. We then set the width of the div, in this case 780px. Then, to center the div's center to the page's center, we pull back the left margin by half the width of the div; in this case -390px. If the width were set to 700px, the margin-left would then be -350px.

That's it. Easy, pretty, and works cross-browser/resolution.

Text Output Parameters For SQL Server 2005 - Tip

I've decided to change this blog into a programming tool and publish tips, tricks and insights. For my first post in my newly formatted blog, I decided to keep it simple and reveal a little tip for people who might have suffered the same confusion. If you are hitting a SQL Server 2005 (and perhaps 2000) and are receiving a text output parameter, you might have received perhaps only the first letter of your expected output, even if when you run the query inside the DBMS it returns the entire string. The answer: the size property. It seems the default size for a varchar or nvarchar output parameter is only one character. So, to receive any and all returned text, you must set the size, such as the following:

Dim retSupp As SqlParameter = cmd.Parameters.Add("@@returnMsg", SqlDbType.VarChar)
retSupp.Size = Integer.MaxValue
retSupp.Direction = ParameterDirection.Output

If you expect a crazy large string in return, you can changed Integer.MaxValue to Long.MaxValue and get the same effect.