How to find second highest or maximum salary of Employee in SQL

In this section we will write SQL query to get second highest salary of Employee. Before writing query its good to be familiar with schema as well as data in table. Here is the Employee table we will be using this SQL example:


mysql> SELECT * FROM Employee;

+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1      | James    | 10      |   2000 |
| 2      | Jack     | 10      |   4000 |
| 3      | Henry    | 11      |   6000 |
| 4      | Tom      | 11      |   8000 |
+--------+----------+---------+--------+
 
4 rows IN SET (0.00 sec)

If you look data, you will find that second maximum salary in this case is 6000 and employee name is Henry. Now let’s see some SQL example to find out this second maximum salary.
Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery. To learn more about Subquery see correlate and non-correlate subquery in SQL

mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
1 row IN SET (0.00 sec)

If you look data, you will find that second maximum salary in this case is 6000 and employee name is Henry. Now let’s see some SQL example to find out this second maximum salary.
Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery. To learn more about Subquery see correlate and non-correlate subquery in SQL

 mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
 
1 row IN SET (0.00 sec)

Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:

mysql> SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
 
1 row IN SET (0.00 sec)

TOP keyword of Sybase and SQL Server database is used to select top record or row of any result set, by carefully using TOP keyword you can find out second maximum or Nth maximum salary as shown below.

SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC) AS emp ORDER BY salary ASC

Here is what this SQL query is doing : First find out top 2 salary from Employee table and list them in descending order, Now second highest salary of employee is at top so just take that value. Though you need to keep in mind of using distinct keyword if there are more than one employee with top salary, because in that case same salary will be repeated and TOP 2 may list same salary twice.

Second maximum salary using LIMIT keyword of MYSQL database

LIMIT keyword of MySQL database is little bit similar with TOP keyword of SQL Server database and allows to take only certain rows from result set. If you look at below SQL example, its very much similar to SQL Server TOP keyword example.

mysql> SELECT salary  FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;

+--------+
| salary |
+--------+
|   6000 |
+--------+
 
1 row IN SET (0.00 sec)

Insert multiple selected items of ListBox into SQL Server database

Introduction : -

In this tutorial, we will learn how to create a ListBox control at design-time as well as at run-time. We will also see how to create a multiple-column ListBox control with single and multiple selections. This article also covers most of the properties and methods of the ListBox control.

Description :-
  In my previous article i have used to save list box selected data in multiple column.
This example shows the basics on how to save multiple selected items from the ASP.Net ListBox control to the database in ASP.Net. Please note that this example requires a basic knowledge of ADO.NET.

STEP1: Setting up the User Interface (GUI)

For the simplicity of this demo, I just set up the web form like below:

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        Employee Names: <br />

        <asp:ListBox ID="ListBox1" runat="server" Height="149px" SelectionMode="Multiple" Width="113px">

        <asp:ListItem>chitranjna</asp:ListItem>

        <asp:ListItem>ranjan</asp:ListItem>

        <asp:ListItem>Rohan</asp:ListItem>

        <asp:ListItem>Sohan</asp:ListItem>

        <asp:ListItem>Ronak</asp:ListItem>

        <asp:ListItem>Ranu</asp:ListItem>

        <asp:ListItem>Raja</asp:ListItem>

        <asp:ListItem>papu</asp:ListItem>

        </asp:ListBox>    

    </div>

    <br />

    <asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />

    </form>

</body>

</html>

Notes: 

* Since the ListBox is intended for multiple item selections then we need to set the SelectionMode attribute of the ListBox to Multiple

* To do multiple Selections in the ListBox then just hold Ctrl key and select the items you want.
STEP 2: Creating a Simple Database Table

In this demo, we are going to store the selected employee names that is selected from the ListBox to the database. So let's now create a simple table that contains the following Column Names:
Note [-id  is auto increment]

Note: I set the Id to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

STEP 3: Declaring the necessary name spaces:

Be sure to add the following namespaces below:

using System.Data.SqlClient;

using System.Collections.Specialized;

using System.Text;

We need to declare the namespaces above so that we can use the SqlClient, StrngCollections and StringBuilder built-in methods in our codes later.

STEP4: Creating the Method for Multiple Inserts.

Here are the code blocks below:

private string GetConnectionString()

    {

        //Where DBConnection is the connetion string that was set up in the web config file

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"]

.ConnectionString;

    }

    private void InsertRecords(StringCollection sc)

    {

        SqlConnection conn = new SqlConnection(GetConnectionString());

        StringBuilder sb = new StringBuilder(string.Empty);

        foreach (string item in sc)

        {

            const string sqlStatement = "INSERT INTO Table1 (Employees) VALUES";

            sb.AppendFormat("{0}('{1}'); ", sqlStatement, item);

        }

        try

        {

            conn.Open();

            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Insert Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally

        {

            conn.Close();

        }

    }

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        StringCollection sc = new StringCollection();

        foreach (ListItem item in ListBox1.Items)

        {

            if (item.Selected)

            {

                sc.Add(item.Text);

            }

        }

        InsertRecords(sc);

    }

STEP5: Compile and Run the Application.

The page output would look something like below:

On Run Time

 On Selection The Name From the ListBox And Press Save Button

Genrate the Pupop After Insert The Record In the database image as shown below

listbox multiple selection asp.net

Introduction : -

A ListBox control provides an interface to display a list of items. Users can select one or more items from the list. A ListBox may be used to display multiple columns and these columns may have images and other controls.
In this tutorial, we will learn how to create a ListBox control at design-time as well as at run-time. We will also see how to create a multiple-column ListBox control with single and multiple selections. This article also covers most of the properties and methods of the ListBox control.

Description :-
 
This example shows the basics on how to save multiple selected items from the ASP.Net ListBox control to the database in ASP.Net. Please note that this example requires a basic knowledge of ADO.NET.

STEP1: Setting up the User Interface (GUI)

For the simplicity of this demo, I just set up the web form like below:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        #form1
        {
            text-align: center;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        Employee names: <br />

        <asp:ListBox ID="ListBox1" runat="server" Height="149px" SelectionMode="Multiple" Width="113px">

        <asp:ListItem>chitranjan</asp:ListItem>

        <asp:ListItem>ranjan</asp:ListItem>

        <asp:ListItem>Rohan</asp:ListItem>

        <asp:ListItem>Shohan</asp:ListItem>

        <asp:ListItem>Ronak</asp:ListItem>

        <asp:ListItem>Ranu</asp:ListItem>

        <asp:ListItem>Raja</asp:ListItem>

        <asp:ListItem>Papu</asp:ListItem>

        </asp:ListBox>  

    </div>

    <br />

    <asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />
    </form>
</body>
</html>

Notes: 

* Since the ListBox is intended for multiple item selections then we need to set the SelectionMode attribute of the ListBox to Multiple

* To do multiple Selections in the ListBox then just hold Ctrl key and select the items you want.
STEP 2: Creating a Simple Database Table

In this demo, we are going to store the selected employee names that is selected from the ListBox to the database. So let's now create a simple table that contains the following Column Names:
Note [-id  is auto increment]

Note: I set the Id to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

STEP 3: Declaring the necessary name spaces:

Be sure to add the following namespaces below:

using System.Data.SqlClient;

using System.Collections.Specialized;

using System.Text;

We need to declare the namespaces above so that we can use the SqlClient, StrngCollections and StringBuilder built-in methods in our codes later.

STEP4: Creating the Method for Multiple Inserts.

Here are the code blocks below:

protected void Button1_Click(object sender, EventArgs e)
    {
        StringCollection sc = new StringCollection();
        foreach (ListItem item in ListBox1.Items)
        {

            if (item.Selected)
            {

                sc.Add(item.Text);

            }

        }

        InsertRecords(sc);
    }


    private void InsertRecords(StringCollection sc)
    {
        SqlConnection conn = VoterConnectionDB.GetConnection();
        StringBuilder sb = new StringBuilder(string.Empty);
        foreach (string item in sc)
        {

            //For pass the more than one column then use this format
            const string sqlStatement = "INSERT INTO Table1 (city,contactno,Employees) VALUES";
            sb.AppendFormat("{0}('{1}','{2}','{3}');",sqlStatement,"indore","0000000000", item);
           // sb.AppendFormat("{0}('{1}','{2}','{3}');", sqlStatement, sqlStatement[0], sqlStatement[1], item);
           // sb.AppendFormat("{0}('{1}','{2}','{3}');", sqlStatement, txtcity_txt, txt_contact.Text, item);
        }

        try
        {
         SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
        }

        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            conn.Close();
        }

    }

STEP5: Compile and Run the Application.

The page output would look something like below:

On Run Time

 On Selection The Name From the ListBox And Press Save Button

Genrate the Pupop After Insert The Record In the database image as shown below

Download sample code attached

WCF Service Example Step By Step For Beginners

Data Contracts and Service Contracts (WCF)

Introduction : -

A WCF Service is comprised of the following major components. The diagram below shows how the components are related to each other:

• Service Contract
• Operation Contract
• Data Contract
• Data Member

ServiceContract

A ServiceContract is a method that is exposed in a WCF Service, which a client calls to execute the service. The ServiceContract almost always has a return value exposed to the client to check if it was successful, or to see if there are any results. A ServiceContract uses Data Contracts to transfer data to and from the client. It can also use primitive data types to transfer data.

// Student ServiceContract
[ServiceContract]
public interface IStudentService
{
    // Define the OperationContact here….
}

Operation Contract

An operation contract defines the methods of the service that are accessible by external systems. The OperationContract attribute needs to be applied for all these methods, these are also like web methods in a web service. Operation contracts are defined as follows:

// Student ServiceContract
[ServiceContract]
public interface IStudentService
{
 //Define the GetStudentFullName OperationContact here
    [OperationContract]
    String GetStudentFullName (int studentId); 

//Define the GetStudentInfo OperationContact here….
    [OperationContract]
    StudentInformation GetStudentInfo (int studentId);

}

Data Contract

A DataContract is a formal agreement between a service and a client on how data will be exchanged. It also describes how data is to be serialized. A DataContract is used when complex data types are needed to be exchanged between the server and client. A class has to be marked with the DataContract attribute for it to be serialized and exposed in the WCF Service. Any class that does not have the DataContract attribute is not accessible in the WCF Service.
Data Contracts are used by Service Contracts like primitive data types. DataContract objects can be passed as parameters for Service Contracts or returned as values for the Service Contracts.
For more Example you can refer to the article: Using Data Contracts

[DataContract]
public class StudentInformation
{
    // Define the Datamembers here….
}

Data  Member

A data member specifies the type which is part of a data contract used as a composite type member of the contract. To define a data member, apply the DataMember attribute to the fields that must be serialized. The DataMember attribute can be applied to private properties, but they will be serialized and deserialized, and will be accessible to the user or process. The code below shows how to define a data member in a data contract:

[DataContract]
public class StudentInformation
{
      _studentId = studId;

    [DataMember]
    public int StudentId
    {
        get { return _studentId; }
        set { _studentId = value; }
    }

}

Creating WCF Applications with Visual Studio 2010

WCF Web Services

Categories : - Introduction to WCF  , Differences Between WCF and ASP.NET Web Services,
Basic Step by Step WCF WebService ,

 
Introduction : -Here I will explain what WCF (Windows communication foundation) is, uses of windows communication foundation and how to create and use windows communication foundation in c#.


Description : - In my previous article i have explained Basic Step by Step WCF WebService   in Asp.net. Know i am create a wcf webservice which return the JSON Data.

CREATE A WCF WEB  SERVICE


1. Start Visual Studio 2010

2. Create a new project 

Click on File \ New \ Project.

Make sure WCF is selected in the left part of the window, then select the project type WCF Service Application.

In the Name textbox, type in JSONWebService.

We're going to create a .Net Framework 4.0 web service, so make sure this is selected at the top of the dialog.

Then click on OK.

To do this, we need to make some changes to the sample code. First, we need to change GetData's parameter type from "int" to "string"

Open up the Service1.svc.cs file, and change the "value" parameter to be a string:

Next.....

Javascript validation for Gridview in edit mode

Categories :- Javascript validation for Gridview footer textbox , Advantages of wcf
 
Introdouction : -

In my Article I am explain how  in can use java script for  validate text box in footer row and check text box in now then generate  the alert please insert Record using  ASp.Net 

Description : -

In My previous Post I have Explained Javascript validation for Gridview footer textbox  in ASp.Net  .There is good source code of  WCF . Now  Java script Validation for Gridview for GridView in Edit Mode.

Here gridview have dropdownlist and two textbox.This javascript validate these fields in edit mode.

function ValidateGridEditMode()
{
var Gid = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer').rows.length;
var i;
for(i=2; i <=Gid; i++)
{
if(i<10)
{
ddlCustLocation = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl0'+i+'_ddlCustLocation');
txtCustCode = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl0'+i+'_txtCustCode');
txtCustName = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl0'+i+'_txtCustName');
}

else
{
ddlCustLocation = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl'+i+'_ddlCustLocation');
txtCustCode = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl'+i+'_txtCustCode');
txtCustName = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustome
r_gvCustomer_ctl'+i+'_txtCustName');
}


ddlCustLocation != null || txtCustCode != null || txtCustName != null)
{
ddlCustLocation == 0)
{
alert("Please Select Location..");
ddlCustLocation.focus();
return false;
}
else
{
if(txtCustCode.value == 0)
{
alert("Please enter Customer Code..");
txtCustCode.focus();
return false;
}
else
{
if(txtCustName.value == "")
{
alert("Please enter Customer Name ..");
txtCustName.focus();
return false;
}
}
}
}
}
return true;
}

Javascript validation for Gridview footer textbox

Introdouction : -

In my Article I am explain how  in can use java script for  validate text box in footer row and check text box in now then generate  the alert please insert Record using  ASp.Net 

Description : -

In My previous Post I have Explain how i can use WCF  . In this article i am explain how  i can validate textbox within grid view and generate the java script validation.

javascript validation function as shown below if you are not use the site  maste page then write this code within <head> tag other wise write this code in
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">

Your javascript code is here

</asp:Content>

javascript validation function as shown below

With in site master page

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">

<script type="text/javascript">
    function ValidateGridview() {
        titlename = document.getElementById('<%=((TextBox)grd_party_influenc.FooterRow.FindControl("txt_f_title")).ClientID%>');
        if (titlename.value ==0) {
            alert("Please Insert The Title ....");
            titlename.focus();
            return false;
        }
       // return true;
    }
  
</script></asp:Content>

Use this JavaScript validation within <head> tag

<head>

<script type="text/javascript">
    function ValidateGridview() {
        titlename = document.getElementById('<%=((TextBox)grd_party_influenc.FooterRow.FindControl("txt_f_title")).ClientID%>');
        if (titlename.value ==0) {
            alert("Please Insert The Title ....");
            titlename.focus();
            return false;
        }
       // return true;
    }
  
</script>

</heaad>

Use Java Script Validation Function as shown Below 

  <FooterTemplate>
                        <asp:LinkButton ID="lnk_btn_insert" runat="server" CommandName="Insert" OnClientClick="ValidateGridview()">Insert</asp:LinkButton>
                    </FooterTemplate>

 

Image As Shown Below

Asp.net Interview Question And Answer

gridview rowcommand object reference not set to an instance of an object

Introduction : -

I am using a gridview with objectdatasource .I have select the data from object datasource but when i insert form gridview i got the error gridview rowcommand object reference not set to an instance of an object.

 Error Page is Here

My gridvew show the all the record from the database as shown in below figure.

Insert the values in textbox

When I insert data in textbox of gridview end click on Insert link then i got the error

Solution :-I am not bind the insert command through the ObjectDataSource like this as shown in figure 

Bind the onbect Data Source Like This Figure
 

Click Next

Click On Fnish Button and Run the Project and you are not getting any error .Data is inserting using gridview successfully as shown  below   figure.

Basic Step by Step WCF WebService

Categories :- create restful wcf service api using post ,   Introduction of WCF web service


Introdution- : -
 In my Previous Article I have create restful wcf service api using post and Introduction of WCF web service  In this post i am explain how i can create a WCF service in asp.net and call through windows from

Description : -

Creating a simple 'Hello World'-like WCF service is very simple with Visual Studio 2010. Open Visual Studio 2010 and select New => Project => WCF Service. Name it for example, HelloWorldWCF. It will initially create 2 files for you (IService.cs and IService.svc). Delete them.
Right-click on your project and add a new WCF Service named HW.svc. Your project should look something like this.

Open the IService1.cs  file and modify it so it looks like this.

namespace HelloWorld
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.
    [ServiceContract]
    public interface IService1
    {

        [OperationContract]
        string GetData(int value);

        [OperationContract]
        CompositeType GetDataUsingDataContract(CompositeType composite);
     
        [OperationContract]
        float AccountBalance(int AccountNumber);

        // TODO: Add your service operations here
    }


    // Use a data contract as illustrated in the sample below to add composite types to service operations.
    [DataContract]
    public class CompositeType
    {
        bool boolValue = true;
        string stringValue = "Hello ";

        [DataMember]
        public bool BoolValue
        {
            get { return boolValue; }
            set { boolValue = value; }
        }

        [DataMember]
        public string StringValue
        {
            get { return stringValue; }
            set { stringValue = value; }
        }
    }
}

Then open the Service1.svc.cs file which is the code-behind for the HW.svc file. Modify it so it looks like this. And write the code as shown below

namespace HelloWorld
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    public class Service1 : IService1
    {

        public float AccountBalance(int AccountNumber)
        {
            //access a secured database for this.
            if (AccountNumber == 786)
            {
                return 56789.0F;

            }
            else
            {
                return 0.0F;
            }

        }
        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }

        public CompositeType GetDataUsingDataContract(CompositeType composite)
        {
            if (composite == null)
            {
                throw new ArgumentNullException("composite");
            }
            if (composite.BoolValue)
            {
                composite.StringValue += "Suffix";
            }
            return composite;
        }
    }
}

That's it. You have just created your first WCF service. To run it, right-click on the IService1.cs  and select 'Set as Start Page' and then push F5. The WCF Testclient opens up. Double click on the method you just created, enter in a value for the parameter and select the call button.Image As Shown Below

Press F5 for execute the service and you get he following screen

Second Screen Is

Check You WCF Service As shown in figure

Consuming the WCF service from a Windows Form application is just as easy as creating it. In the WCF Testclient, note the http address and port that Visual Studio is using to run you WCF webservice. It may look like this (http://localhost:4428/Service1.svc). You will need this url from within the Windows Form to add a reference to the WCF service.

Open another instance of Visual Studio 2010. Leave the instance you used to created the service open and the personal web service running. If you close it down, you will not be able to access the url above that is running the service.

In the new instance of Visual Studio 2010, select New => Project => Windows Form Application. Add a button and a label to the form. In the Solution explorer, right-click references and select Add Web Resource and enter the url we noted from the WCF Testclient, then Go. Give the service a logical namespace and select ok. In this example I named it AccountBalance.

 Add On Button and One Label on the form as shown below(in this form label is used for display the values)

Write Click on callwcfservice and Add Service Reference As Shown Below

Type The WFC Service Name and click ok as shown below fogure

Your solution explorer should look something like this.

  Lastly, add the code to the button clicked event to call the WCF service.

private void button1_Click(object sender, EventArgs e)
        {
            AccountBalance.Service1Client client =new AccountBalance.Service1Client();
            double message = client.AccountBalance(786);
            lbl_value.Text = message.ToString();

        }

Run the Form and click on Button then You will Get the output of the web service .

If you are like this article then please give me you valuable comment related to this post and mail me if you get any error .

Download sample code attached