Thursday, 19 December 2013

Search Box

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Search Box</title>

<script src="../Resources/JSFiles/jquery-1.4.1.min.js" type="text/javascript"></script>
 <style type="text/css">
        .highlighted
        {
            background-color: yellow;
        }
        #search
        {
            width: 190px;
            height: 20px;
            border: 0px solid gray;
            background-color: #EDEDED;
            color: Black;
            font-weight: normal;
            font-size: 14px;           
        }
        #spanID:focus
        {
            border-color: #668681;
            -webkit-box-shadow: inset 0px 0px 10px 0px #ddd;
            -moz-box-shadow: inset 0px 0px 10px 0px #ddd;
            box-shadow: inset 0px 0px 10px 0px #ddd;
        }
        #searchText
        {
            width: 20px;
            background-color: Transparent;
            vertical-align: middle;
        }
        #spanID
        {
            background-color: #EDEDED;
            border: 1px solid #808080;
            float: right;
            width: 210px;
        }
    </style>
    <script type="text/javascript">
       function searchAndHighlight(searchTerm, selector) {
    if(searchTerm) {
        //var wholeWordOnly = new RegExp("\\g"+searchTerm+"\\g","ig"); //matches whole word only
        //var anyCharacter = new RegExp("\\g["+searchTerm+"]\\g","ig"); //matches any word with any of search chars characters
        var selector = selector || "body";                             //default selector is body if none provided
        var searchTermRegEx = new RegExp(searchTerm,"ig");
        var matches = $(selector).text().match(searchTermRegEx);
        if(matches) {
            $('.highlighted').removeClass('highlighted');     //Remove old search highlights           

            $(selector).html($(selector).html()
                    .replace(searchTermRegEx, "<span class='highlighted'>"+searchTerm+"</span>"));
            if($('.highlighted:first').length) {             //if match found, scroll to where the first one appears
                $(window).scrollTop($('.highlighted:first').position().top);               
            }
            return true;
        }
        else{
        alert("No results found");
        }
    }
    return false;
}
    </script>


</head>
<body>
    <div class="content" id="bodyContainer">
        <h1>
            <span id="spanID" style="float: right;"><input type="text" id="search" placeHolder="search..." onblur="javascript: searchAndHighlight($(this).val(), '#bodyContainer');" /><input type="image" src="images/zoom_in.png" title="search..." id="searchText" name="searchText" onclick="searchAndHighlight($('#search').val(), '#bodyContainer')" /></span>
        </h1>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
    </div>
    </div>
</body>
</html>

Get Months between two dates

Select top 12* FROM fn_getMonth(1)




-- Author: Nitish Jha -> 19 Dec 13.
-- If 0 Then Jan, Feb etc Else 1 to 12. 
Create function fn_getMonth (@DispType tinyint)     
Returns @Months Table (Months varchar(20))     
Begin     
DECLARE @startdt DATETIME, @enddt DATETIME     
Set @startdt =  (Select top 1 ActualStartDate from sessionmaster Where isDefault='Yes')     
Set @enddt =  (Select top 1 ActualEndDate from sessionmaster Where isDefault='Yes')     
    if (@DispType=0)   BEGIN
INSERT INTO @Months VALUES (Left(DateName(mm,@startdt),3))     
--INSERT INTO @Months VALUES (Month(@startdt))   
   
WHILE @startdt < @enddt     
BEGIN     
 SET  @startdt = DATEADD(MONTH,1,@startdt)     
 INSERT INTO @Months VALUES (Left(DateName(mm,@startdt),3))     
  --INSERT INTO @Months VALUES (Month(@startdt))   
END 
END
ELSE
BEGIN
INSERT INTO @Months VALUES (Month(@startdt))   
   
WHILE @startdt < @enddt     
BEGIN     
 SET  @startdt = DATEADD(MONTH,1,@startdt)     
  INSERT INTO @Months VALUES (Month(@startdt))   
END 
END
   
Return     
End



Cheers!!!

Saturday, 26 October 2013

Query to remove duplicate records

CREATE TABLE [dbo].[ATTENDANCE](
    [EMPLOYEE_ID] [varchar](50) NOT NULL,
    [ATTENDANCE_DATE] [datetime] NOT NULL
) ON [PRIMARY]



INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A003',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A004',CONVERT(DATETIME,'01-01-12',5))


-- First of All Create Identity.
ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1) 

Select * FROM ATTENDANCE

-- Getting Duplicate Records.
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 
   
-- Deleting Duplicate Records.   
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Saturday, 19 October 2013

Connection with Access, Excel and MDF (SQL SERVER)

Instructions::
1. Access File Name --  AccessDB_2007.accdb (2007)  & AccesDB_2003.mdb (97-2003 format) and Table name is - 'MemberMaster'.
2. Excel File Name --  ExcelDB_2007.xlsx (2007)  & ExcelDB_2003.xls (97-2003 format) and Sheet name is - 'MemberMaster'.
3. MDF File --  Database name SQLDB. Table Name -- MemberMaster.

      <<<<<<      If getting error while copying MDF then use these query in SQL SERVER. You have to copy mdf to App_Data folder.      >>>>>>>
    
            --    1.    --
                    USE MASTER; // Yeah its Master Database.

                    -- Take database in single user mode -- if you are facing errors.  
                    -- This may terminate your active transactions for database

                    ALTER DATABASE SQLDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

                    -- Detach DB
                    EXEC MASTER.dbo.sp_detach_db @dbname = N'SQLDB'

            --    2.    --
                    -- Attach again
                    CREATE DATABASE [SQLDB] ON
                    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQLDB.mdf' ),
                    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQLDB_log.ldf' )
                    FOR ATTACH

                    use SQLDB
                    Select * FROM MemberMaster


Tables:: Database and Table names are written in Connections strings.






ASPX Page

    <asp:TextBox ID="uid" runat="server" />
    <asp:TextBox ID="pwd" runat="server" />
    <asp:Button ID="submit" runat="server" Text="Submit" onclick="submit_Click" />







 C#

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data;
using System.Data.OleDb; // For Access & Excel.
using System.Data.SqlClient; // For SQL SERVER.


public partial class Programfiles_Access_login : System.Web.UI.Page
{
    //string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DataBase\AccesDB_2003.mdb;";  //Access 2003.
    string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DataBase\AccessDB_2007.accdb"; //Access 2007.

    //string conExl = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DataBase\ExcelDB_2003.xls;Extended Properties=Excel 8.0;";    //MS Excel 2003.
    //string conExl = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DataBase\ExcelDB_2007.xlsx;Extended Properties=Excel 12.0;";    //MS Excel 2007.

    //string conSQL = @"Data Source=.;AttachDbFilename=SQLDB.mdf;User Instance=true; uid=sa;pwd=saa;"; // MDF Connection is best possible with SQL SERVER 2005.

  

    protected void Page_Load(object sender, EventArgs e)
    {
      
    }

    protected void submit_Click(object sender, EventArgs e)
    {
        #region AccessDB Connection
        if ((!string.IsNullOrEmpty(uid.Text)) && (!string.IsNullOrEmpty(pwd.Text)))
        {
            string qry = "Select * From MemberMaster where UserID='" + uid.Text + "' and Password='" + pwd.Text + "'";
            using (OleDbConnection con = new OleDbConnection(conn))
            {
                using (OleDbCommand cmd = new OleDbCommand(qry, con))
                {
                    con.Open();
                    OleDbDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (dr.HasRows == true)
                        {
                            Session["Name"] = dr["FullName"].ToString();
                            Response.Redirect("Welcomepage.aspx");
                        }
                    }
                    if (dr.HasRows == false)
                    {
                        Response.Write("Either ID Or Password Is Incorrect.");
                    }
                }
            }
        }
        #endregion

        #region Excel Connection
        //if ((!string.IsNullOrEmpty(uid.Text)) && (!string.IsNullOrEmpty(pwd.Text)))
        //{
        //    string ExlSheet = "MemberMaster";// Name of Excel Sheet.;
        //    string qry = "Select * From " + "[" + ExlSheet + "$]" + " where UserID='" + uid.Text + "' and Password='" + pwd.Text + "'";
        //    using (OleDbConnection con = new OleDbConnection(conExl))
        //    {
        //        using (OleDbCommand cmd = new OleDbCommand(qry,con ))
        //        {
        //            con.Open();
        //            OleDbDataReader dr = cmd.ExecuteReader();
        //            while (dr.Read())
        //            {
        //                if (dr.HasRows == true)
        //                {
        //                    Session["Name"] = dr["FullName"].ToString();
        //                    Response.Redirect("Welcomepage.aspx");
        //                }
        //            }
        //            if (dr.HasRows == false)
        //            {
        //                Response.Write("Either ID Or Password Is Incorrect.");
        //            }
        //        }
        //    }
        //}
        #endregion

        #region Connection MDF (SQL Server)
        //if ((!string.IsNullOrEmpty(uid.Text)) && (!string.IsNullOrEmpty(pwd.Text)))
        //{
        //    string ExlSheet = "MemberMaster";// Excel Sheet name;
        //    string qry = "Select * From " + "[" + ExlSheet + "$]" + " where UserID='" + uid.Text + "' and Password='" + pwd.Text + "'";
        //    using (SqlConnection con = new SqlConnection(conSQL))
        //    {
        //        using (SqlCommand cmd = new SqlCommand(qry, con))
        //        {
        //            con.Open();
        //            SqlDataReader dr = cmd.ExecuteReader();
        //            while (dr.Read())
        //            {
        //                if (dr.HasRows == true)
        //                {
        //                    Session["Name"] = dr["FullName"].ToString();
        //                    Response.Redirect("Welcomepage.aspx");
        //                }
        //            }
        //            if (dr.HasRows == false)
        //            {
        //                Response.Write("Either ID Or Password Is Incorrect.");
        //            }
        //        }
        //    }
        //}
        #endregion
    }
}


Cheers!!!

Calculate Age in SQL Server

-- select dbo.FN_CalculateAge('1/21/1990') doB   -- DD/MM/YYYY.
Create FUNCTION [dbo].[FN_CalculateAge](@dayOfBirth datetime)
RETURNS Varchar(300)
AS 
BEGIN 
 
 DECLARE @Age varchar(300)
 DECLARE @today datetime, @thisYearBirthDay datetime
 DECLARE @years int, @months int, @days int 
 SELECT @today = GETDATE() 

 SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth) 
 SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END) 
 SELECT @months = MONTH(@today - @thisYearBirthDay) - 1 
 SELECT @days = DAY(@today - @thisYearBirthDay) - 1 
 Set @Age=Cast(@years  As Varchar)+' years '+ Cast(@months  As Varchar)+' months '+Cast(@days As Varchar) +' days'
RETURN @Age 
END

Friday, 18 October 2013

Popup Calendar

Here I wrote about popup Calendar and how to find html contrl at server side( In C#).

1. Download ts_picker.js from here.
2. Create ASPX page with name 'Calendar.aspx'.
3. New Just copy and paste aspx and C# to your page from here.
4. Make sure you script path is correct.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Calendar.aspx.cs" Inherits="Programfiles_Access_Calendar" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Popup date picker</title>
    <script src="../../Resources/JSFiles/ts_picker.js" type="text/javascript"></script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <input type="text" name="txtFromDate" id="txtFromDate" runat="server" maxlength="10"
            size="8" readonly="readonly" />
        <a href="javascript:show_calendar('document.form1.txtFromDate',document.form1.txtFromDate.value);">
            <img src="../../Resources/JSFiles/cal.gif" width="20" height="18" border="0" align="middle"
                alt="click here to pick the date" /></a>
        <br />
        <asp:Button ID="btnAlert" runat="server" Text="Alert Date" OnClick="btnAlert_Click" />
    </div>
    </form>
</body>
</html>

----------   C#   ------------------


    protected void btnAlert_Click(object sender, EventArgs e)
    {
        string strResult = "", strFromDate = "";
        HtmlInputText txtFromDate = (HtmlInputText)this.FindControl("txtFromDate");

        string[] splitFromString = new string[] { "-", " " };    // Remove only - and space.
        //string[] splitFromString = new string[] { "-"}; // Remove only -.
        string[] splitFromDate = txtFromDate.Value.Split(splitFromString, StringSplitOptions.None);

        if (Convert.ToInt32(splitFromDate[1].ToString()) < 10)
            splitFromDate[1] = "0" + splitFromDate[1].ToString();
        if (Convert.ToInt32(splitFromDate[0].ToString()) < 10)
            splitFromDate[0] = "0" + splitFromDate[0].ToString();

        strFromDate = splitFromDate[2].ToString() + "-" + splitFromDate[1].ToString() + "-" + splitFromDate[0].ToString();
        Response.Write("<script>alert('" + Convert.ToString(strFromDate) + "');</script>");// YYYY-DD-MM.
    }



NOTE::
    * When you get downloaded zip file extract that and copy all files to your project. here when you click on Calendar icon then there will be a pop up Calendar open.
      there you can click on any date and that will be shown to textbox. there will date with time if you want only date then you can make changes to js file.
     
      there is "// print calendar footer" comment, you can chagne old code to this as below.
     
        // print calendar footer
        str_buffer +=
        "<form name=\"cal\">\n<tr><td colspan=\"7\" bgcolor=\"#87CEFA\">"+
        "<font color=\"White\" face=\"tahoma, verdana\" size=\"2\">"+
       
        //"Time: <input type=\"text\" name=\"time\" value=\""+dt2tmstr(dt_datetime)+ "\" size=\"8\" maxlength=\"8\"></font></td></tr>\n</form>\n" +
        "Time: <input type=\"text\" name=\"time\" value=\"\" size=\"8\" maxlength=\"8\"></font></td></tr>\n</form>\n" +
       
        "</table>\n" +
        "</tr>\n</td>\n</table>\n" +
        "</body>\n" +
        "</html>\n";
       
   
   
    Now your code work fine.
   
   
    Cheers!!

Thursday, 17 October 2013

Smart Page Scrolling


 Create a page Scrool.aspx

Copy this and paste.
Download Jquery

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Scrool.aspx.cs" Inherits="ProgramFiles_Login_Scrool" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Smart Scrolling: by Nitish Kumar</title>

    <script src="../../Resources/JSFiles/jquery-1.4.1.min.js" type="text/javascript"></script>
    <%-- 1.     It scroll window bottom to top directly--%>
   
    <%-- <script type="text/javascript">
        $(document).ready(function($){
            $(window).scroll(function(){
           
                if ($(this).scrollTop() < 500) {
                    $('#Icon') .fadeOut();
                } else {
                    $('#Icon') .fadeIn();
                }
            });
            $('#Icon').on('click', function(){
                $('html, body').animate({scrollTop:0}, 'fast');
                return false;
                });
        });
    </script>--%>


    <%-- 2.     It scroll window bottom to top but ony by one div.--%>
    <script type="text/javascript">
       $(document).ready(function($){
            var ar = new Array();
            var offset= 400;// distance form top.
            var duration=500;// time to fadeIn or fadeOut.
           
            $(window).scroll(function(){
                $("div[id*='divScr']").each(function() {// It will find divs whose name like 'divScr'.

                        var divLeft = $(this);
                        var position = divLeft.position().top - $(window).scrollTop();
                        if (position < 0)
                        {
                            ar.push(divLeft.attr('id'));
                        }
//                        else
//                        {
//                            ar.pop();
//                        }
                 });

                    if ($(this).scrollTop() >= offset) {
                        $('#Icon').fadeIn(duration);
                    }
                    else {
                        $('#Icon').fadeOut(duration);
                    }
             });

                $('#Icon').click(function(event) {
                    event.preventDefault();
                    var divLeftTopid =ar.pop();
                   
                    var divLeftTopPosition = $('#' + divLeftTopid).offset().top - 60;
                    $('html, body').animate({ scrollTop: divLeftTopPosition }, duration);
                    return false;
                });
       });
       
    </script>

    <style type="text/css">
        .divs
        {
            height: 500px;
        }
        #Icon
        {
            height: 30px;
            width: 30px;
            position: fixed;
            bottom: 20px;
            right: 0px;
            text-indent: -999px;
            display: none;
            background: url( "1381592590_27881.ico" ) no-repeat;  /* Note: Icon or Images size should be small as possible.  */
            -webkit-transition-duration: 0.4s;
            -moz-transition-duration: 0.4s;
            transition-duration: 0.4s;
        }
        #Icon:hover
        {
            -webkit-transform: rotate(360deg);
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div style="width: 100%; float: left;">
        <div class="divs" id="divScr1" style="border: 1px solid red; margin-bottom: 5px;">
            One
        </div>
        <div class="divs" id="divScr2" style="border: 1px solid blue; margin-bottom: 5px;">
            Two
        </div>
        <div class="divs" id="divScr3" style="border: 1px solid green; margin-bottom: 5px;">
            Three<img src="arrow_up_alt1-128.png" height="40px" width="40px" />
        </div>
        <div class="divs" id="divScr4" style="border: 1px solid gray; margin-bottom: 5px;">
            Four
        </div>
        <div class="divs" id="divScr5" style="border: 1px solid purple; margin-bottom: 5px;">
            Five
        </div>
    </div>
    <a href="#top" id="Icon" title="Back to top"></a>
    </form>
</body>
</html>


Cheers!!!