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.