Friday, 23 September 2016

How to drop or delete all tables from database with one SQL query

How to drop all tables from database with one SQL query?

You can write query like this:

USE Databasename

SELECT  'DROP TABLE ' + name + ';'
FROM    sys.tables
You can copy and paste into a new SSMS window to run it.
Posted by at No comments:

Tuesday, 6 September 2016

How to Find Nth/Second Highest and Lowest Salary in SQL


We will explore the answer with a series of scenarios and questions asked in relation to finding the highest, lowest, and nth highest salary.

+----+---------+-----------+
| Id | Amount  |  Name     |
+----+---------+-----------+
| 1  | 1100    |   Peter   |
| 2  | 1200    |   Sachin  |
| 3  | 1300    |   Roberts |
| 4 | 1400   |   Shyam  |
+----+---------+-----------+

suppose table name is tbl_TeacherPay

(A) Find the Employee with the Highest Salary
SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount Desc) as Salary_Order
from   tbl_TeacherPay
) DT


WHERE DT. Salary_Order = 1 ;

(B)Finding the Employee with the Nth Highest Salary

So Query is

SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount Desc) as Salary_Order
from   tbl_TeacherPay
) DT
WHERE DT. Salary_Order = 3 ;


Result

+----+---------+-----------+
| Id | Amount  |  Name     |
+----+---------+-----------+
| 1  | 1200    |  Sachin   |
+----+---------+-----------+

(C)Finding the Employee with the Lowest Salary
SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount asc) as Salary_Order
from   tbl_TeacherPay
) DT
WHERE DT. Salary_Order = 1 ;


(D)Find the Employee with the Highest Salary When There Is a Tie (Two employees both have the highest salary and the number is the same)
SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount desc) as Salary_Order
from   tbl_TeacherPay
) DT
WHERE DT. Salary_Order = 1 ;

Posted by at No comments:

Second Highest Salary in MySQL and SQL Server

Write a SQL query to get the second highest salary from the table.
+----+---------+
| Id | Amount  |
+----+---------+
| 1  | 1100    |
| 2  | 1200    |
| 3  | 1300    |
+----+---------+

For example, given the above a table , the second highest salary is 1200. If there is no second highest salary, then the query should return NULL.
suppose table name is tbl_TeacherPay


So Query is


SELECT TOP 1 Amount FROM
( SELECT TOP (2) Amount FROM tbl_TeacherPay
ORDER BY AmountDESC)
AS tbl_TeacherPay order by Amount asc


Result

+----+---------+
| Id | Amount  |
+----+---------+
| 1  | 1200    |
+----+---------+


Posted by at No comments:

Wednesday, 17 August 2016

Single Stored Procedure for Select Insert Update Delete in SQL Server

Single Stored Procedure for Select Insert Update Delete in SQL Server

I have made use of the following table tbl_course










The following stored procedure will be used to perform Select, Insert, Update and Delete operations on the Customers table of the SQL Server database.


Create PROCEDURE [dbo].[AddCourse]
(
    
    @id VARCHAR(50)=null,
    @Name   VARCHAR(200)=null,
@Duration   VARCHAR(200)=null,
@Fee   VARCHAR(200)=null,
@StatementType nvarchar(20) = ''
)
AS
BEGIN
IF @StatementType = 'Insert'
BEGIN
insert into tbl_course values(@Name, @Duration,@Fee)   
END
 
IF @StatementType = 'Select'
BEGIN
select * from tbl_course order by id desc
END 

IF @StatementType = 'SelectId'
BEGIN
select * from tbl_course where id = @id
END  

IF @StatementType = 'Search'
BEGIN
select * from tbl_course where Name like '%'+@Name+'%'
END  

IF @StatementType = 'Update'
BEGIN
UPDATE tbl_course SET
            Name =  @Name
, Duration=@Duration,
Fee = @Fee
      WHERE id = @id
END
IF @StatementType = 'Delete'
BEGIN
DELETE FROM tbl_course WHERE  id = @id
END
end
Posted by at No comments:

Tuesday, 5 July 2016

how to create masked textbox in asp.net or how to set ajax maskededitextender in asp.net

How to use AJAX MaskedEditExtender in ASP.NET for time

 MaskedEditExtender is part of rich AJAXControlToolKit. It is used to restrict the user to enter values in a specific format. There are five mask types available in MaskedEditExtender to limit user to specific format. It includes Time, Date, Number, DateTime and None. You can provide MaskType as “None” and can provide your own format. Mask property is used to set the format for all these mask types.

 For this u should be add ajaxtoolkit dll on your application

 1. Open web form and add a ScriptManager control from “AJAX Extensions” tab.
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

2. Add TextBox controls and add four MaskedEditExtender and four MaskedEditValidator from “AjaxControlToolkit” in your web form. Here is code for entire aspx page.


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
 
<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
 <asp:MaskedEditExtender ID="MaskedEditExtender1" runat="server" CultureName="en-GB" TargetControlID="txtTimeSpent"  Mask="99:99" MaskType="Time" AcceptNegative="None"/> 

                                        <asp:MaskedEditValidator id="MaskedEditValidator1" runat="server" ControlExtender="MaskedEditExtender1" EmptyValueMessage="Please enter Time" InvalidValueMessage="Invalid Time" ControlToValidate="txtTimeSpent" ValidationGroup="s" />  
</div>
</form>
</body>
</html>
Posted by at No comments:

Wednesday, 8 June 2016

jQuery jump or scroll to certain position, div or target on the page from button onclick

how to scroll page onclick of link button

hello,
First of all you should be add this Code and jquery pulgin under head tag.
 <script src="//ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <script>
        $(function () {
            $('a[href*="#"]:not([href="#"])').click(function () {
                if (location.pathname.replace(/^\//, '') == this.pathname.replace(/^\//, '') && location.hostname == this.hostname) {
                    var target = $(this.hash);
                    target = target.length ? target : $('[name=' + this.hash.slice(1) + ']');
                    if (target.length) {
                        $('html, body').animate({
                            scrollTop: target.offset().top
                        }, 1000);
                        return false;
                    }
                }
            });
        });

</script>

 And then after modify below code according to your need.

 <a hef="#Section1">Section1</a><br>
<a hef="#Section2">Section2</a><br>
<div id="Section1" style="height:300px;background-color:#f89f9f">Hello</div>
<div id="Section2" style="height:300px;background-color:#8986fe">Vineet</div>
Posted by at No comments:

Tuesday, 3 May 2016

Implement check all checkbox functionality in ASP.Net GridView control using JavaScript

Implement check all checkbox functionality in ASP.Net GridView control using JavaScript

database Structure of tbl_login 





















First add below script between <head> tag

<script type="text/javascript" language="javascript">
        function CheckAllEmp(Checkbox) {
            var GridVwHeaderChckbox = document.getElementById("<%=grdata.ClientID %>");
            for (i = 1; i < GridVwHeaderChckbox.rows.length; i++) {
                GridVwHeaderChckbox.rows[i].cells[0].getElementsByTagName("INPUT")[0].checked = Checkbox.checked;
            }
        }
    </script> 

Note: where grdata is gridview id.

Now add gridview in your aspx page

<asp:GridView runat="server" ID="grdata" AutoGenerateColumns="false" DataKeyNames="UserName" class="table table-striped table-bordered table-hover">
 <Columns>
    <asp:TemplateField>
        <HeaderTemplate>
              <asp:CheckBox ID="chkHeader" runat="server" onclick="CheckAllEmp(this);" />
        </HeaderTemplate>
     <ItemTemplate>
        <asp:CheckBox runat="server" ID="chck" />
      </ItemTemplate>
  </asp:TemplateField>
   <asp:TemplateField HeaderText="Name">
        <ItemTemplate>
           <asp:Label runat="server" ID="lblName" Text='<%#Eval("Name") %>'></asp:Label>
         </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Mobile">
     <ItemTemplate>
       <asp:Label runat="server" ID="lblMobile" Text='<%#Eval("Mobile") %>'></asp:Label>
       </ItemTemplate>
   </asp:TemplateField>
   <asp:TemplateField HeaderText="ID">
       <ItemTemplate>
         <asp:Label runat="server" ID="lblid" Text='<%#Eval("UserName") %>'></asp:Label>
       </ItemTemplate>
   </asp:TemplateField>
   <asp:TemplateField HeaderText="Address">
       <ItemTemplate>
      <asp:Label runat="server" ID="lblAddress" Text='<%#Eval("Address") %>'></asp:Label>
    </ItemTemplate>
   </asp:TemplateField>
  <asp:TemplateField HeaderText="Email">
    <ItemTemplate>
       <asp:Label runat="server" ID="lblEmail" Text='<%#Eval("emailid") %>'></asp:Label>
     </ItemTemplate>
 </asp:TemplateField>
</Columns>

</asp:GridView>

In .CS FILE add this code for bind gridview 


void bind()
    {
        try
        {
            Connection.con.Open();
            SqlCommand cmd = new SqlCommand("select * from tbl_Login where Role ='Sr Coordinator' order by id desc", Connection.con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdata.DataSource = dt;
                grdata.DataBind();
            }
            else
            {
                dt.Rows.Add(dt.NewRow());
                grdata.DataSource = dt;
                grdata.DataBind();
                int count = grdata.Rows[0].Cells.Count;
                grdata.Rows[0].Cells.Clear();
                grdata.Rows[0].Cells.Add(new TableCell());
                grdata.Rows[0].Cells[0].ColumnSpan = count;
                grdata.Rows[0].Cells[0].Text = "There is no record in your DataBase..";
            }
        }
        catch (Exception ex)
        {
           
        }
        finally
        {
            Connection.con.Close();
        }
    }

Now i told you how to hold  value in datatable of selected column of gridview


Use the following method code of hold data in datatable which are use easily  

  public void getSelectRows()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[5] { new DataColumn("EmployeeID"), new DataColumn("Name"), new DataColumn("Mobile"), new DataColumn("Email"), new DataColumn("Address")});
        foreach (GridViewRow row in grdata.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                CheckBox chkRow = (row.Cells[0].FindControl("chck") as CheckBox);
                Label lblName1 = (row.Cells[0].FindControl("lblName") as Label);
                Label lblId = (row.Cells[0].FindControl("lblid") as Label);
                Label lblmobile = (row.Cells[0].FindControl("lblMobile") as Label);

                Label lblAdd = (row.Cells[0].FindControl("lblAddress") as Label);
                Label lblEmail = (row.Cells[0].FindControl("lblEmail") as Label);
             

                if (chkRow.Checked)
                {
                  
                    string EmployeeID = lblId.Text;
                    string Name = lblName1.Text;
                    string Mobile = lblmobile.Text;
                    string Address = lblAdd.Text;
                    string email = lblEmail.Text;
                
                    //string Date = DateTime.Now.ToString("dd/MMM/yyyy");

                    dt.Rows.Add(EmployeeID, Name, Mobile, email, Address);
                }
            }
        }
    }

Above code use can use as select all checkbox in gridview in asp.net.
Posted by at No comments:
Subscribe to: Posts (Atom)