Tuesday, September 23, 2014

GridView textbox Navigation using Arrow(up/down) keys using Javascript in ASP.Net

 function GetTextBoxValues1(txt) {
//sai krishna for right arrow
//debugger;
if (window.event.keyCode == 39) {
    //var cnt = document.getElementById("GridView1").rows[0].cells.length;
    var gridView = document.getElementById("GridView1");
    for (var i = 1; i < gridView.rows.length - 1; i++) {
        if (txt == document.getElementById("GridView1_txtWeek1_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek2_" + (i - 1)).focus();
            return false;
        }
        if (txt == document.getElementById("GridView1_txtweek2_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek3_" + (i - 1)).focus();
            return false;
        }
        if (txt == document.getElementById("GridView1_txtweek3_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek4_" + (i - 1)).focus();
            return false;
        }
        if (txt == document.getElementById("GridView1_txtweek4_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek4_" + (i - 1)).focus();
            return false;
        }
                  
    }
}
//sai krishna for left arrow
//debugger;
if (window.event.keyCode == 37) {
    //var cnt = document.getElementById("GridView1").rows[0].cells.length;
    var gridView = document.getElementById("GridView1");
    for (var i = gridView.rows.length - 1; i >= 1 ; i--) {
        if (txt == document.getElementById("GridView1_txtWeek1_" + (i - 1)).id) {
            document.getElementById("GridView1_txtWeek1_" + (i-1)).focus();
            return false;
        }
        if (txt == document.getElementById("GridView1_txtweek2_" + (i - 1)).id) {
            document.getElementById("GridView1_txtWeek1_" + (i - 1)).focus();
            return false;
        }
        if (txt == document.getElementById("GridView1_txtweek3_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek2_" + (i - 1)).focus();
            return false;
        }
        if (txt == document.getElementById("GridView1_txtweek4_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek3_" + (i - 1)).focus();
            return false;
        }
    }
}
//sai krishna for down arrow
if (window.event.keyCode == 40) {
    var gridView = document.getElementById("GridView1");
    for (var i = 1; i < gridView.rows.length-1; i++) {
        if (txt == document.getElementById("GridView1_txtWeek1_" + (i - 1)).id)
        {
            document.getElementById("GridView1_txtWeek1_" + (i)).focus();
            return false;
        }
    }
    for (var i = 1; i < gridView.rows.length - 1; i++) {
        if (txt == document.getElementById("GridView1_txtweek2_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek2_" + (i)).focus();
            return false;
        }
    }
    for (var i = 1; i < gridView.rows.length - 1; i++) {
        if (txt == document.getElementById("GridView1_txtweek3_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek3_" + (i)).focus();
            return false;
        }
    }
    for (var i = 1; i < gridView.rows.length - 1; i++) {
        if (txt == document.getElementById("GridView1_txtweek4_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek4_" + (i)).focus();
            return false;
        }
    }
}
//sai krishna for UP arrow
if (window.event.keyCode == 38) {
    var gridView = document.getElementById("GridView1");
    for (var i = gridView.rows.length - 1; i > 1 ; i--) {
        if (txt == document.getElementById("GridView1_txtWeek1_" + (i - 1)).id) {
            document.getElementById("GridView1_txtWeek1_" + (i-2)).focus();
            return false;
        }
    }
    for (var i = gridView.rows.length - 1; i > 1 ; i--) {
        if (txt == document.getElementById("GridView1_txtweek2_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek2_" + (i - 2)).focus();
            return false;
        }
    }
    for (var i = gridView.rows.length - 1; i > 1 ; i--) {
        if (txt == document.getElementById("GridView1_txtweek3_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek3_" + (i - 2)).focus();
            return false;
        }
    }
    for (var i = gridView.rows.length - 1; i > 1 ; i--) {
        if (txt == document.getElementById("GridView1_txtweek4_" + (i - 1)).id) {
            document.getElementById("GridView1_txtweek4_" + (i - 2)).focus();
            return false;
        }
    }
}
}

<asp:GridView ID="GridView1" TabIndex="0" runat="server" Width="100%" GridLines="Horizontal">
  <Columns>
    <asp:TemplateField HeaderText="Week" ItemStyle-HorizontalAlign="Center" HeaderStyle-CssClass="datagridheaders" HeaderStyle-ForeColor="white">
    <ItemTemplate>
  <asp:TextBox ID="txtweek2" runat="server" Width="75px" onkeyup="return GetTextBoxValues1(this.id);" Columns="10"></asp:TextBox>
  </ItemTemplate>
  </asp:TemplateField>
 </Columns>
</asp:GridView>

Thursday, September 11, 2014

Search for a specific text in entire database

USE [AIRLINES]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchTables]
(
     @SearchStr nvarchar(100)
)
AS
BEGIN

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2
nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
     SET @ColumnName = ''
     SET @TableName =
     (
         SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
         FROM    INFORMATION_SCHEMA.TABLES
         WHERE       TABLE_TYPE = 'BASE TABLE'
             AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
             AND OBJECTPROPERTY(
                     OBJECT_ID(
                         QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
                          ), 'IsMSShipped'
                            ) = 0
     )
     WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
     BEGIN
         SET @ColumnName =
         (
             SELECT MIN(QUOTENAME(COLUMN_NAME))
             FROM    INFORMATION_SCHEMA.COLUMNS
             WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                 AND TABLE_NAME  = PARSENAME(@TableName, 1)
                 AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                 AND QUOTENAME(COLUMN_NAME) > @ColumnName
         )
         IF @ColumnName IS NOT NULL
         BEGIN
             INSERT INTO @Results
             EXEC
             (
                 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                 FROM ' + @TableName + ' (NOLOCK) ' +
                 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
             )
         END
     END
END
SELECT ColumnName, ColumnValue FROM @Results END

--To execute the above Stored Procedure
DECLARE @return_value int
EXEC @return_value = [dbo].[SearchTables]
@SearchStr = N'SAIKRISHNA'
SELECT 'Return Value' = @return_value