Monday, April 30, 2012

What is the difference between Union and Union All Operator?

SQL UNION Operator:


The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

SQL UNION Example

Look at the following tables:

"Employees_Norway":

E_IDE_Name
01Hansen, Ola
02Svendson, Tove
03Svendson, Stephen
04Pettersen, Kari

"Employees_USA":

E_IDE_Name
01Turner, Sally
02Kent, Clark
03Svendson, Stephen
04Scott, Stephen

Now we want to list all the different employees in Norway and USA.
We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The result-set will look like this:

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen
Note: This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them will be listed. The UNION command selects only distinct values.

SQL UNION ALL Example

Now we want to list all employees in Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

Result
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

The main difference between Union and Union ALL operator is

Union operator will return distinct values but Union ALL returns all the values including duplicate values.
Filed under:

Custom validator with Client side and Server side Validation


The simple Example of Custom validator with Client side validation is shown as below.The Example is about the From Year should be Less than the To Year.

Client side

 <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" language="javascript">
        function CustomValidate(sender, args) {
            if (document.getElementById('ddlfromyear').selectedIndex > document.getElementById('ddltoyear').selectedIndex) {
                args.IsValid = false;
            }
            else {
                args.IsValid = true;
            }
        }

    </script>
</head>
<body>
    <form id="form1" runat="server" >
    <div>
    From:
    <asp:DropDownList ID="ddlfromyear" runat="server" ValidationGroup="aa"></asp:DropDownList>

    To:
    <asp:DropDownList ID="ddltoyear" runat="server" ValidationGroup="aa"></asp:DropDownList>

    <asp:CustomValidator ID="custvaddropdown" runat="server" ValidationGroup="aa" ClientValidationFunction="CustomValidate" ControlToValidate="ddlfromyear" ErrorMessage="From Year should be Less than the To Year." Text="From Year should be Less than the To Year."></asp:CustomValidator>
    <asp:Button ID="btntest" runat="server" Text="Test" ValidationGroup="aa" />

    </div>
     </form>
</body>
</html>

.aspx.cs Page code


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

        private void BindDropDown()
        {
            for (int i = 1990; i < 2012; i++)
            {
                ddlfromyear.Items.Add(i.ToString());
                ddltoyear.Items.Add(i.ToString());
            }
        }

The custom validation for server side :


I am showing you how to use custom validator with server side validation...
The Practical that i am showing you is about the textbox length should be eight character or more than that.if its length is less than 8 character then it will shown an error on Page.

.aspx Page

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Custom validator with ServerSide</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:CustomValidator ID="CustomValidator1" runat="server" OnServerValidate="TextValidate"
            ControlToValidate="TextBox1" ErrorMessage="Text must be 8 or more characters.">
        </asp:CustomValidator><br />
        <asp:Button ID="btnClick" runat="server" Text="Check" OnClick="btnClick_Click" />
    </div>
    </form>
</body>
</html>

.aspx.cs Page

namespace CustomValidator
{
    public partial class ServerSideCustomValidator : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void TextValidate(object source, ServerValidateEventArgs args)
        {
            if (args.Value.Length >= 8)
            {
                args.IsValid = true;
            }
            else
            {
                args.IsValid = false;
            }
          
        }

        protected void btnClick_Click(object sender, EventArgs e)
        {
            if (Page.IsValid)
            {
                Response.Redirect("~/ClientSideCustomValidator.aspx");
            }
        }
    }
}

Download sample from here......
Download
Filed under:

Validation For DropdownList with First Value is "Select" and How to use Javascript and Server Side validation in same Page.....


if you want to use both server side validation and javascript in one page then you have to include  Page_ClientValidate(); in Javascript otherwise it will not check the server side validation..In Actual senario browser check the server side validation and display the error but javascript will return the true value so it will go ahead with the next code...for Example

.aspx page code

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
         function validate() {
        
          
            if (document.getElementById('ddlfrommonth').selectedIndex >= document.getElementById('ddltomonth').selectedIndex) {
                alert("Error");
                return false;
            }
            else {
                Page_ClientValidate();
                return Page_IsValid;
            }
        }
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:TextBox ID="txtname" runat="server" ></asp:TextBox>
    <asp:RequiredFieldValidator ID="reqtxtname" runat="server"  ControlToValidate="txtname" ErrorMessage="name required" Text="name required"></asp:RequiredFieldValidator>
  
  
   From
    <asp:DropDownList ID="ddlfrommonth" runat="server">
    <asp:ListItem Selected="True" Text="Select"></asp:ListItem>
    <asp:ListItem  Text="January"></asp:ListItem>
    <asp:ListItem  Text="February"></asp:ListItem>
    <asp:ListItem  Text="March"></asp:ListItem>
    <asp:ListItem  Text="April"></asp:ListItem>
    <asp:ListItem  Text="May"></asp:ListItem>
    <asp:ListItem  Text="June"></asp:ListItem>
    <asp:ListItem Text="July"></asp:ListItem>
    <asp:ListItem Text="August"></asp:ListItem>
    <asp:ListItem Text="September"></asp:ListItem>
    <asp:ListItem Text="October"></asp:ListItem>
    <asp:ListItem Text="November"></asp:ListItem>
    <asp:ListItem Text="December"></asp:ListItem>
    </asp:DropDownList>
    <asp:RequiredFieldValidator ID="ReqFrommonth" runat="server" InitialValue="Select" ControlToValidate="ddlfrommonth" ErrorMessage="From Month Required" Text="*"></asp:RequiredFieldValidator>
    To
    <asp:DropDownList ID="ddltomonth" runat="server">
     <asp:ListItem Selected="True" Text="Select"></asp:ListItem>
    <asp:ListItem  Text="January"></asp:ListItem>
    <asp:ListItem  Text="February"></asp:ListItem>
    <asp:ListItem  Text="March"></asp:ListItem>
    <asp:ListItem  Text="April"></asp:ListItem>
    <asp:ListItem  Text="May"></asp:ListItem>
    <asp:ListItem  Text="June"></asp:ListItem>
    <asp:ListItem Text="July"></asp:ListItem>
    <asp:ListItem Text="August"></asp:ListItem>
    <asp:ListItem Text="September"></asp:ListItem>
    <asp:ListItem Text="October"></asp:ListItem>
    <asp:ListItem Text="November"></asp:ListItem>
    <asp:ListItem Text="December"></asp:ListItem>
    </asp:DropDownList>
    <asp:RequiredFieldValidator ID="Reqddltomonth" runat="server" InitialValue="Select" ControlToValidate="ddltomonth" ErrorMessage="To Month Required" Text="*"></asp:RequiredFieldValidator>
  
    <asp:Button ID="btntest" runat="server" Text="Validate"
            OnClientClick="return validate();" onclick="btntest_Click"/>
     </div>
    </form>
</body>
</html>

.aspx.cs page code

protected void btntest_Click(object sender, EventArgs e)
{
      Response.Redirect("~/test.aspx");
}

if you dont include the Page_ClientValidate and return Page_IsValid; in Page then it will simply redirect the test.aspx page without if you dont write anything in textbox..means require field for Textbox will not be executed....
Filed under:

How to Access the Property of aspx.cs page to javascript...



For Example Create a Web Page..

.aspx page code



<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        function GetValue() {
            var value = "<%=Name %>";
            alert(value);
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Button ID="btngetvalue" Text="GetValue" OnClientClick="GetValue();" runat="server"/>
    </div>
    </form>
</body>
</html>




aspx.cs Page code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Property
{
    public partial class Property : System.Web.UI.Page
    {
        private string _name;
      
        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }
       protected void Page_Load(object sender, EventArgs e)
       {
           Name = "Kartik";
       }
    }
}


Now run page and check the alert message.you will get the value of aspx.cs page propery.
Filed under:

Thursday, April 5, 2012

Difference between VARCHAR and NVARCHAR

The data type Varchar and Nvarchar are the sql server data types, both will used to store the string values.

The abbreviation for Varchar is Variable Length character String.
The abbreviation of Nvarchar is uNicode Variable Length character String.

The Varchar allocates single byte for a character. So can be used to store for 8000 characters in this datatype.

The NVarchar allocates two bytes for a character. Because this Nvarchar type allows to store the special characters. So can be used to store for 4000 characters in this types.

If you want to store more length of uNicode string, the you have to go for the TEXT and NTEXT data types. These are the BLOB( Binary Large Objects) datatypes.

Use Varchar and Nvarchar instead of TEXT and NTEXT as regular. The the later datatypes in unavoidable times. 
Filed under:

Wednesday, April 4, 2012

SQL Server Reset identity column value of table

Introduction: 
In this article I will explain how to reset identity column value of table in in SQL server.
Description:
After set identity property on particular column(StudentId) I inserted few records in Student table and that value(StudentId) automatically increase whenever I inserted data that would be like this.Here I have inserted some Name of students.



Now I am Going to deleted all existing records and then tried to insert new records in table.Now if you can see that identity column value starting from previous increased value Ex: Above table contains 5 records after delete all the records if I insert new record StudentId value will start from 6.
To reset identity column value and start value from “1” during insert new records we need to write query to reset identity column value. Check below Query

DBCC CHECKIDENT (Table_Name, RESEED, New_Reseed_Value) where
Table_Name is name of your identity column table

RESEED specifies that the current identity value should be changed.

New_Reseed_Value is the new value to use as the current value of the identity column.   
  
EX: DBCC CHECKIDENT ('Student', RESEED, 0)
Once we run the above query it will reset the identity column in Student table and starts identity column value from “1”
Filed under:

What is Json?Introduction about Json........


JSON: JavaScript Object Notation.
JSON is syntax for storing and exchanging text information. Much like XML.
JSON is smaller than XML, and faster and easier to parse.

  • JSON stands for JavaScript Object Notation
  • JSON is lightweight text-data interchange format
  • JSON is language independent *
  • JSON is "self-describing" and easy to understand

Example

{
"employees": [
{ "firstName":"John" , "lastName":"Doe" },
{ "firstName":"Anna" , "lastName":"Smith" },
{ "firstName":"Peter" , "lastName":"Jones" }
]
}

The employees object is an array of 3 employee records (objects).

Filed under:

How to Use String Builder in C#..

It is used to build a formatted string.Lets Look at the Example You will get the Clear Idea about it....


            string spaces = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Hello,");
            sb.AppendFormat("<br/>");
            sb.AppendFormat("<br/>{0}",spaces);
            sb.AppendFormat("A new  request is submitted. Details are as follows");
            sb.AppendFormat("<br/>{0}", spaces);
            sb.AppendFormat("<b>Page URL</b>  :  ");
          
            sb.AppendFormat("<br/>{0}", spaces);
            sb.AppendFormat("<b>FirstName</b>  :  ");
          
            sb.AppendFormat("<br/>{0}", spaces);
            sb.AppendFormat("<b>LastName</b>  :  ");
          
            sb.AppendFormat("<br/>{0}", spaces);
            sb.AppendFormat("<b>CompanyName</b>  :  ");
          
            sb.AppendFormat("<br/>{0}", spaces);
            sb.AppendFormat("<b>Phone</b>  :  ");
          
            sb.AppendFormat("<br/>{0}", spaces);
            sb.AppendFormat("<b>Email</b>  :  ");
          
Now If you Print sb in Page you will get Like this...
            Hello,
                     A new  request is submitted. Details are as follows
                     FirstName
                     LastName
                     CompanyName
                     Phone
                     Email
Filed under:

If and Else in SQL.....


CREATE PROCEDURE [dbo].[GetFilterData]
-- Add the parameters for the stored procedure here
@CourseLibraryCategoryID int,
@Search nvarchar(50),
@LanguageTypeId nvarchar(50)
AS
BEGIN
if @CourseLibraryCategoryID!=0
begin
select * from TrainingCourses TC LEFT JOIN TrainingSkins TS ON TC.[SkinID] = TS.[SkinID]   where CourseID in(select CourseID from CourseCategory where CategoryID=@CourseLibraryCategoryID)
end

else if @LanguageTypeId!=''
begin
select * from TrainingCourses TC LEFT JOIN TrainingSkins TS ON TC.[SkinID] = TS.[SkinID] where   TC.[LanguageTypeID]= @LanguageTypeId
        end

else
begin
select * from TrainingCourses TC LEFT JOIN TrainingSkins TS ON TC.[SkinID] = TS.[SkinID] where  TC.[CourseName] Like '%'+@Search + '%'
     end
END

Filed under:

Subscribe to: Posts (Atom)