Asp.Net and SQL SERVER

Saturday, March 26, 2011

Monday, February 28, 2011

Grouping RadioButton inside gridveiw

        function uncheckOthers(id) {
            var elm = document.getElementsByTagName('input');
            for (var i = 0; i < elm.length; i++) {
                if (elm.item(i).id.substring(id.id.lastIndexOf('_')) == id.id.substring(id.id.lastIndexOf('_'))) {
                    if (elm.item(i).type == "radio" && elm.item(i) != id) elm.item(i).checked = false;
                }
            }
        }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        try
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                //To make the RadioButton mutually exclusive
                string strScript = "uncheckOthers(" + ((RadioButton)e.Row.Cells[0].FindControl("rbSelect")).ClientID + ");";
                ((RadioButton)e.Row.Cells[0].FindControl("rbSelect")).Attributes.Add("onclick", strScript);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Selecting or DeSelecting multiple checkbox inside a gridview

function SelectAllOrDeselect(CheckBox, Type, Column, HCheckBox) {
    try {
        var TotalChkBx = parseInt('<%= grd_EmpSelection.Rows.Count %>');
        var TargetBaseControl = document.getElementById('<%= grd_EmpSelection.ClientID%>');
        var TargetChildControl = null;

        if (Column == "1") TargetChildControl = "chkTestSelect";
        else TargetChildControl = "chkTrainingSelect";

        var Inputs = TargetBaseControl.getElementsByTagName("input");
        var SelectCount = 0;
        //Checked/Unchecked all the checkBoxes in side the GridView.
        if (Type == "1") {
            for (var iCount = 0; iCount < Inputs.length; ++iCount) {
                if (Inputs[iCount].type == 'checkbox' && Inputs[iCount].id.indexOf(TargetChildControl, 0) >= 0) 
                    Inputs[iCount].checked = CheckBox.checked;
            }

            //Reset Counter
            SelectCount = CheckBox.checked ? TotalChkBx : 0;
        } else if (Type == "2") {
            //Reset Counter
            for (var iCount = 0; iCount < Inputs.length; ++iCount) {
                if (Inputs[iCount].type == 'checkbox' && Inputs[iCount].id.indexOf(TargetChildControl, 0) >= 0) 
                    if (Inputs[iCount].checked == true) SelectCount++;
            }

            //Modifiy Counter;        
            if (!CheckBox.checked && SelectCount > 0) SelectCount--;

            //Change state of the header CheckBox.
            if (SelectCount < TotalChkBx) HCheckBox.checked = false;
            else if (SelectCount == TotalChkBx) HCheckBox.checked = true;
        }
    } catch (err) {}
}

protected void grd_EmpSelection_RowCreated(object sender, GridViewRowEventArgs e)
    {
        //e.Row.RowState == DataControlRowState.Edit not works on Alternating Rows 
        //if ((e.Row.RowState & DataControlRowState.Edit) > 0)
        if (e.Row.RowType == DataControlRowType.DataRow && (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate))
        {

            CheckBox chkTestSelectAll = (CheckBox)this.grd_EmpSelection.HeaderRow.FindControl("chkTestSelectAll");
            CheckBox chkTestSelect = (CheckBox)e.Row.Cells[1].FindControl("chkTestSelect");

            chkTestSelectAll.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'1','1', {0});", chkTestSelectAll.ClientID);
            chkTestSelect.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'2','1', {0});", chkTestSelectAll.ClientID);


            CheckBox chkTrainingSelectAll = (CheckBox)this.grd_EmpSelection.HeaderRow.FindControl("chkTrainingSelectAll");
            CheckBox chkTrainingSelect = (CheckBox)e.Row.Cells[1].FindControl("chkTrainingSelect");

            chkTrainingSelectAll.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'1','2', {0});", chkTrainingSelectAll.ClientID);
            chkTrainingSelect.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'2','2', {0});", chkTrainingSelectAll.ClientID);

        }
    }

The gridview checkbox will maintain the checked state during postback.

Reference
http://www.codeproject.com/KB/webforms/SelectingAllCheckBoxes.aspx

Thursday, December 2, 2010

Generate Insert Multiple Records Using One Insert Statement – Use of UNION ALL

Version I

DECLARE @TableName            VARCHAR(50)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @InsColNames AS       VARCHAR(8000)
DECLARE @NewLineChar AS       VARCHAR(2)
DECLARE @HorizontalTabChar AS VARCHAR(1)
DECLARE @SQuoteFront AS       VARCHAR(15)
DECLARE @SQuoteBack AS        VARCHAR(15)
DECLARE @Comma AS             VARCHAR(150)

SET @SQuoteFront       = '''''''''+'
SET @SQuoteBack        = '+'''''''''
SET @Comma             = ''',''' + ', '
SET @TableName         = 'NetworkPath'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)

SELECT   @ColNames     = ( COALESCE (@ColNames + ( '''' + column_name + '''' + ', ' ), '') ),
         @InsColNames  = (
                  CASE
                           WHEN
                                    (
                                             data_type = 'bit'
                                    OR       data_type = 'bigint'
                                    OR       data_type = 'decimal'
                                    OR       data_type = 'float'
                                    OR       data_type = 'int'
                                    OR       data_type = 'money'
                                    OR       data_type = 'numeric'
                                    OR       data_type = 'tinyint'
                                    OR       data_type = 'image'
                                    )
                           THEN COALESCE (@InsColNames, '') + ( 'CAST( ' + column_name  + ' AS VARCHAR)' + ' + ' + '''' + ' AS ' + column_name + ''''   + ', ' + '''' + ',' + '''' + ',' )
                           ELSE COALESCE (@InsColNames, '') + ( @SQuoteFront + 'CAST( ' + column_name + ' AS VARCHAR('+ CAST(character_maximum_length AS VARCHAR)  +') )' + @SQuoteBack + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + @Comma )
                  END )
FROM     information_schema.columns
WHERE    table_name = @TableName
ORDER BY ordinal_position ASC

--Remove comma(,) at the end
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)

--PRINT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )'
--Generate Inserte statement for the given table
SELECT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )' AS InsertStatement

--PRINT 'SELECT '+  @InsColNames + ' FROM ' + @TableName

--Remove ,',', at the end
SET @InsColNames = LEFT(@InsColNames, LEN(@InsColNames) - 5)
PRINT CHAR(13) + CHAR(10)

SELECT @InsColNames

/*
PRINT 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames +
''' UNION ALL' +
'''' + CHAR(13) + CHAR(10) + 'FROM ' + @TableName
*/

EXEC( 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames + ''' UNION ALL' + '''' + 'FROM ' + @TableName )

Version II

DECLARE @TableName            VARCHAR(50)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @InsColNames AS       VARCHAR(8000)
DECLARE @NewLineChar AS       VARCHAR(2)
DECLARE @HorizontalTabChar AS VARCHAR(1)
DECLARE @SQuoteFront AS       VARCHAR(15)
DECLARE @SQuoteBack AS        VARCHAR(15)
DECLARE @Comma AS             VARCHAR(150)
SET @SQuoteFront       = '''''''''+'
SET @SQuoteBack        = '+'''''''''
SET @Comma             = ''',''' + ', '
SET @TableName         = 'NetworkPath'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)
SELECT   @ColNames     = ( COALESCE (@ColNames + ( '''' + column_name + '''' + ', ' ), '') ),
         @InsColNames  = (
         CASE
                  WHEN
                           (
                                    data_type = 'bit'
                           OR       data_type = 'bigint'
                           OR       data_type = 'decimal'
                           OR       data_type = 'float'
                           OR       data_type = 'int'
                           OR       data_type = 'money'
                           OR       data_type = 'numeric'
                           OR       data_type = 'tinyint'
                           OR       data_type = 'image'
                           )
                  THEN COALESCE (@InsColNames, '') + ( 'CAST( ' + column_name + ' AS VARCHAR)' + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + '''' + ',' + '''' + ',' )
                  ELSE COALESCE (@InsColNames, '') + ( @SQuoteFront + 'CAST( ' + column_name + ' AS VARCHAR('+ CAST(character_maximum_length AS VARCHAR) +') )' + @SQuoteBack + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + @Comma )
         END )
FROM     information_schema.columns
WHERE    table_name = @TableName
ORDER BY ordinal_position ASC
--Remove comma(,) at the end
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)
--PRINT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )'
--Generate Inserte statement for the given table
SELECT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )' AS InsertStatement
--PRINT 'SELECT '+  @InsColNames + ' FROM ' + @TableName
--Remove ,',', at the end
SET @InsColNames = LEFT(@InsColNames, LEN(@InsColNames) - 5)
PRINT CHAR(13)                                          + CHAR(10)
SELECT @InsColNames
/*
PRINT 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames +
''' UNION ALL' +
'''' + CHAR(13) + CHAR(10) + 'FROM ' + @TableName
*/
EXEC( 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames + ''' UNION ALL' + '''' + 'FROM ' + @TableName )

Reference

Tuesday, October 19, 2010

Process Comma Separated Values for Insert Delete and Update into Table

Download Full Script

Thursday, September 2, 2010

Collapsible Div

function ToggleCollapsible(ControlIdToShow, SaveStateField, ControlFireID) {
    var control = document.all[ControlIdToShow].style;
    var expandstate = document.all[SaveStateField];

    var ControlFireID = document.getElementById(ControlFireID);

    if (control.display == 'none') {
        control.display = '';
        expandstate.value = 'true';

        if (ControlIdToShow == "ToShow") {
            //showImageID.style.display = "none";
            //hideImageID.style.display = "block";
            //On button click change the image
            ControlFireID.src = "Collapsible/collapse.gif";
            ControlFireID.title = "Hide";
        }
    }
    else {
        control.display = 'none';
        expandstate.value = 'false';

        if (ControlIdToShow == "ToShow") {
            //showImageID.style.display = "block";
            //hideImageID.style.display = "none";
            //On button click change the image
            ControlFireID.src = "Collapsible/expand.gif";
            ControlFireID.title = "Show";
        }
    }

    return false;
}

<input type="hidden" name="CollapsiblePanelHidden" value="True" />
<input id="imgBtnShow" name="imgBtnShow" type="image" src="Collapsible/expand.gif"
onclick="return ToggleCollapsible ('ToShow', 'CollapsiblePanelHidden', 'imgBtnShow')"
title="Show" style="display: block;" />
<div id="ToShow" style="display: none;">
<table id="ShowTable">
<tr>
<td>
<a id="ShowTableContent"
title="Click to expand/collapse" onclick="return ToggleCollapsible ('PanelToShowOrHide', 'CollapsiblePanelHidden', 'imgBtnShow', 'imgBtnHide')"
href="#">Collapsible panel (click to expand).</a></td>
</tr>
<tr>
<td id="PanelToShowOrHide" style="width: 100%; display: none; color: Red;">
http://asp-tech.blogspot.com/
</td>
</tr>
</table>
</div>

Wednesday, September 1, 2010

Generate CSS DropDown Menu from DataBase

IF OBJECT_ID (N'dbo.MenuMain', N'U') IS NOT NULL
 DROP TABLE dbo.MenuMain;
GO

CREATE TABLE dbo.MenuMain(ID INT, [Name] VARCHAR(50),[Link] VARCHAR(50), [Title] VARCHAR(50), ParentID INT, [Order] INT)

INSERT INTO dbo.MenuMain

SELECT 1, 'CSS Drop Down Menus', NULL, NULL, 0, 1 UNION ALL
SELECT 2, 'Vertical CSS Pop-Out Menu', NULL, NULL, 0, 2 UNION ALL
SELECT 3, 'CSS Hover Navigation',     '#', 'SEO Consultants Directory', 2, 1 UNION ALL
SELECT 4, 'Horizontal Drop & Pop Menu', NULL, NULL, 0, 3 UNION ALL
SELECT 5, 'SEO Consultants Sample',     '#', 'SEO Consultants Vertical Example', 2, 2 UNION ALL
SELECT 6, 'Tanfa Demo example',         '#', 'Complete Example', 5, 1 UNION ALL
SELECT 7, 'Stage 1',                    '#', 'Vertical Menu - Page 1', 6, 1 UNION ALL
SELECT 8, 'Stage 2',                    '#', 'Vertical Menu - Page 2', 6, 2 UNION ALL
SELECT 9, 'Stage 3',                    '#', 'Vertical Menu - Page 3', 6, 3 UNION ALL
SELECT 10, 'Stage 4',                   '#', 'Vertical Menu - Page 4', 6, 4 UNION ALL
SELECT 11, 'Stage 5',                   '#', 'Vertical Menu - Page 5', 6, 5

SELECT   ID    ,
         [Name],
		 [Link],
		 [Title],
         CASE WHEN ParentID = 0
              THEN NULL
              ELSE ParentID
         END AS ParentID,
         [Order]
FROM     dbo.MenuMain
ORDER BY ParentID,
         [Order]

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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;
using System.Data.SqlClient;
using System.Text;

public partial class HorizontalCssMenu : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //http://ago.tanfa.co.uk/css/examples/menu/tutorial-h.html#hs7
        //http://www.alistapart.com/articles/horizdropdowns/

        if (!IsPostBack)
        {
            LoadMenu();
        }
    }

    private void LoadMenu()
    {
        DataTable dtMenu = LoadData();
        
        StringBuilder sbMenu = new StringBuilder();

        foreach (DataRow dr in dtMenu.Rows)
        {
            if (string.IsNullOrEmpty(Convert.ToString(dr["ParentID"]))
                || Convert.ToString(dr["ParentID"]) == "0")
            {
                if (sbMenu.Length != 0)
                    sbMenu.Append(Environment.NewLine);
                sbMenu.Append("" + Environment.NewLine);                 sbMenu.Append("
");

sbMenu.Append("" + Convert.ToString(dr["Name"]) + "
" + Environment.NewLine);

sbMenu.Append("
" + Environment.NewLine + "");
            }
            else
            {
                StringBuilder sbTemp = new StringBuilder();
                if (Convert.ToInt32(dr["Order"]) == 1)
                    sbTemp.Append(Environment.NewLine + "");                 sbTemp.Append(Environment.NewLine + "
");

sbTemp.Append("" + Convert.ToString(dr["Name"]) + "");

sbTemp.Append(Environment.NewLine + "
");                 if (Convert.ToInt32(dr["Order"]) == 1)                     sbTemp.Append(Environment.NewLine + "");

                string id = "ID=\"" + Convert.ToString(dr["ParentID"]) + "\"";
                int length = sbMenu.ToString().IndexOf(id);
                if (length != -1)
                {
                    string Previous = sbMenu.ToString().Substring(0, length);
                    string Next = sbMenu.ToString().Substring(length);
                    if (Convert.ToInt32(dr["Order"]) == 1)
                        sbMenu.Insert((Previous.Length + Next.IndexOf("
")), sbTemp);
                    else
                        sbMenu.Insert((Previous.Length + Next.IndexOf("")), sbTemp);
                }
                else
                    sbMenu.Append(sbTemp);
            }
        }

        if (sbMenu.ToString().Length > 0)
        {
            menu.InnerHtml = sbMenu.ToString();
        }
    }

    public DataTable LoadData()
    {
        DataSet dsUserMenu = null;
        try
        {
            //Connection string from Web.Config
            string sDBConnection = "SERVER=;DATABASE=;UID=;pwd=";
            string[] sTableName = { "Menu" };

            string sQuery = @"SELECT     ID    ,
                                         [Name],
		                                 [Link],
		                                 [Title],
                                         CASE WHEN ParentID = 0
                                              THEN NULL
                                              ELSE ParentID
                                         END AS ParentID,
                                         [Order]
                                FROM     dbo.MenuMain
                                ORDER BY ParentID,
                                         [Order]";

            dsUserMenu = new DataSet();

            DBHelper.FillDataset(sDBConnection, CommandType.Text, sQuery, dsUserMenu, sTableName);

            dsUserMenu.DataSetName = "Menus";
            DataRelation objMenuRelation = new DataRelation("ParentChild",
                    dsUserMenu.Tables["Menu"].Columns["ID"],
                    dsUserMenu.Tables["Menu"].Columns["ParentID"],
                    false);
            objMenuRelation.Nested = true;
            dsUserMenu.Relations.Add(objMenuRelation);
            return dsUserMenu.Tables[0];
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

Download .cs, .aspx, .css, .sql file form below
HorizontalCssMenu.zip

References
ago.tanfa.co.uk
Example
alistapart