Make sure to "return" before assigning an "onclick" event in code-behind

I often find myself forgetting the trivial... especially when it comes to assigning onclick attributes to a control in code-behind processing. A perfect example is in the OnRowDataBound event for a GridView where I want to assign an cleint-side event handler to a Delete button to show a JavaScript Confirm (OK/Cancel) before the data hits the bit-bucket.

GridView Event Handler in code-behind:
protected void gridview_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// Add a JavaScript warning event handler to the delete button
LinkButton deleteLinkButton = (LinkButton)e.Row.FindControl("btnDelete");
if (deleteLinkButton != null)
deleteLinkButton.Attributes.Add("onclick", "return ConfirmDelete('" + whateverString
+ "');");
}
}


JavaScript Function in ASPX Mark-Up:

function ConfirmDelete(someData)
{
var msg = "Are you sure you wish to delete this item?\n\n("
+ someData + ")";
if (confirm(msg) == true)
return true;
else
return false;
}

Static Connection String Cause of DataReader Failure - SQLDataReader IndexOutOfRange

Background:
For the past four months, I have been performing fixes and enhancements to a data management application that my client had developed by a third-party vendor. It is a relatively data-intensive ASP.NET application that utilizes the ASP.NET Membership Provider, as well as several custom data access classes. All query logic is done through stored procedures deployed on a SQL Server 2005 database. This application is intended to support concurrent users, but unforrtunately we frequently encounter exceptions such as SQLDataReader ... IndexOutOfRange. This error typically occurs if you try to reference columns that were not returned by a query. In our particular case, we verified that the stored procedures always returned the named columns, and that the DataReader.HasRows always evaluated to true. The DataReader logic was nested within a try/catch block like:

try
{
conn.Open();
SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.SingleRow);
string userName = "";
reader.Read();

if (reader.HasRows)
{
userName = GetDBValue(reader["UserName"]);
}
reader.Close();

return userName;
}
catch (SqlException ex)
{
throw new ApplicationException("An error has occured ...");
}
finally
{
conn.Close();
}

I was lucky enough to find a pattern to consistenly reproduce the error condition, which was essentially opening two browsers and executing the same query. This is a sad story given the application fails under the load of two concurrent users. I spent several days tuning the SQL Server 2005 database, and modifying application code to minimize calls to the database such as persisting data into ViewState. After these efforts, we still experienced the dreaded DataReader errors.

Solution:
I came across this large thread on MSDN entitled "SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange" This article offers several suggestions such as handling DataReader errors, disabling connection pooling and avoid persisting a SQL data connection in a static instance. I took the first, and least invasive route by disabling connection pooling in the connection string located in Web.Config. Unfortunately, this not only impacted the performance, but I still observed the same DataReader errors.

I took the next path by determing how the SQL connection as being utilized. Bingo! I saw that a utility class contained the following:

public class DBUtils
{
protected static SqlConnection conn = null;

protected static SqlConnection GetConn()
{
if (ConnectionString == null)
ConnectionString = WebConfigurationManager.ConnectionStrings
["ApplicationConnectionString"].ConnectionString;
return new SqlConnection(ConnectionString);
}
}

Calls were being made to the above function as:

conn = GetConn();

I modified the above to use a more traditional approach as follows:

SqlConnection conn = new SqlConnection(Common.AppConfig.SqlConnectionString);

Note: AppConfig.SQLConnection string is a public accessor in the application's Common namespace to the connnection string stored in Web.Config.

By creating a new connection string instance instead of relying on the static instance, the application is now able to handle concurrent queries without throwing any DataReader exceptions. I can not say that this will be the ultimate solution for this application, but at least it's a proven stop-gap measure for now.

References:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/74a5db87-c63f-4386-b317-e848dedb2cd9).

Reset Identity Column

Script for resetting an identity column in SQL Server:


Declare @identInitValue int
Declare @tableName varchar(200)
set @identInitValue=1
set @tableName = 'WHATEVER'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED('dbo' + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

SQL Delete from all tables and reset identity

I came across this useful script on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 to delete all data from a SQL server table and re-seed the identity columns:

use [#db name#]
Set NoCount ON

Declare @tableName varchar(200)
Declare @tableOwner varchar(100)
Declare @skipident varchar(3)
Declare @identInitValue int
set @tableName = ''
set @tableOwner = ''
set @skipident = 'NO'
set @identInitValue=1
/*
Step 1: Disable all constraints
*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*
Step 2: Delete the data for all child tables & those which has no relations
*/
While exists
(
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments')
and Table_type='BASE TABLE' and T.table_name > @TableName
)

Begin
Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments')
and Table_type='BASE TABLE' and T.table_name > @TableName
order by t.table_name

--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)
--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end
checkpoint
End
/*
Step 3: Delete the data for all Parent tables
*/
set @TableName=''
set @tableOwner=''
While exists
(
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key'
and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
and T.table_name > @TableName
)

Begin
Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key'
and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
and T.table_name > @TableName
order by t.table_name
--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)
--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end
checkpoint
End
/*
Step 4: Enable all constraints
*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
Set NoCount Off

asp:ImageButton Issues With Form Submission on Enter Key Press

Background:
I had a webform that contained two text fields, an asp:ImageButton and an asp:Button. The ImageButton and Button each had their own event handlers doing very different things. The OnClick event handler for the ImageButton performed an action specific to the Textbox to the left of it, and was only intended to fire when the user clicked the button. The Button control performed an action specific to both Textboxes, and was intended to fire either when clicked OR upon depressing the enter key.

Problem:
The form was always posting-back when the enter key was depressed -- even in the case where the user entered text into the Textbox belonging to the Button control.

Solution:
The first thing that came to mind was to set a property on the ImageButton to prevent submission just as the
UseSubmitBehavior="false" works for asp:Button... you would think! But, nah... this is not a property of asp:Imagebutton. I tried implementing Javascript to listen for a keyclick and disable the submission by the Imagebutton when enter (13) was clicked, but no such luck. When such things happen, who you gonna call... Google!

After doing some searching around, I decided to replace with the ImageButton with a regular asp:Button control and set UseSubmitBehavior="false". But, now this looked like regular button and needed to resemble the previous ImageButton implementation that had a magifying glass image. The problem is that asp:Button does not have a property to assign an image... that's why asp:ImageButton was created!

To get around this, I assigned an inline style (could have also been done in CSS) setting a background:url to the image and setting the width/height to the size of the image. Since the location of the image file is in a different directory than the WebForm, and the root of the URL may change, I did this in the Page_Load of the code-behind by adding it as an attribute to the asp:Button control as follows:

btnGoTo.Attributes.Add("style", "position: relative; background:url(" + Request.ApplicationPath + "/images/expandview.gif) no-repeat; border:none 0px; width:24px; height:24px; vertical-align:top; cursor:pointer;");

This did the trick!

Setting Focus in ASP.NET User Control

When using an ASP.NET user control, there is no tag to set a Javascript event handler for calling a function to set focus to a control. One solution I found was to write a public utility method that accepts a control as an argument, and dynamically builds the Javascript function to set focus to that specific control.

1. Create a public utility class in App_Code containing a public method named "SetFocus":

using System;
using System.Data;
using System.Configuration;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public class PageUtils
{
public static void SetFocus(Control control)
{
StringBuilder sb = new StringBuilder();
sb.Append("\r\n
");

control.Page.RegisterClientScriptBlock("SetFocus", sb.ToString());
}
}

2. Call the above method in the code-behind of the user control's page loading/rendering method passing the control that requires initial focus:

PageUtils.SetFocus(this.txtSearchField);

NowhereLand Filming in LoDo

Just outside my office at 1860 Blake Street, the film crew for the upcoming Eddie Murphy movie, "NeverLand", a prop Mercades was being configured with camera equipment.

A full article about the filming of NowhereLand can be found online:

http://www.denverpost.com/search/ci_7033903