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);