To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

Correlated sub queries are more generalized to find n'th highest or nth lowest salary.

To Find Maximum Salary

SELECT * FROM `student` WHERE mark=(select max(mark) from student)

n means 1 st, 2nd, 3rd highest or lowest salary dont give n values,Give n-1 values
For eg
please give

(if you want 2nd highest salary give n=1
for 3rd n=2 like that.)

To find nth highest salary

SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

select top 2 * from Trainee where Salary in(select top 1 Salary from Depart)order by Salary DESC

To find 2nd lowest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

To find nth lowest salary

SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

SQL SERVER List All Tables of Database

SQL SERVER List All Tables of Database

USE YourDBName
GO
SELECT *
FROM sys.Tables
GO

Count number of tables in a SQL Server database

Count number of tables in a SQL Server database

USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

Coding to get nth record in sql server

Coding to get nth record in sql server


for 10 th record

select top 1 * from (select top 10 * from dEmployeenew order by 1 desc)ac

choose 10 with nth

Option Explicit statement and Option Strict Statement

Option Explicit statement and Option Strict Statement

Option Explicit Statement

The Option Explicit has two modes. On and Off mode.

If Option Explicit mode in ON , you have to declare all the variable before you use it in the program . If not , it will generate a compile-time error whenever a variable that has not been declared is encountered .If the Option Explicit mode is OFF , Vb.Net automatically create a variable whenever it sees a variable without proper declaration.
By default the Option Explicit is On

Option Strict Statement
Visual Basic allows conversions of many data types to other data types. Data loss can occur when the value of one data type is converted to a data type with less precision or smaller capacity. A run-time error occurs if such a narrowing conversion fails. Option Strict ensures compile-time notification of these narrowing conversions so they can be avoided.
On
Optional. Enables Option Strict checking.
Off
Optional. Disables Option Strict checking. If On or Off is not specified, the default is Off.

Difference Between Int32.Parse(), Convert.ToInt32(), and Int32.TryParse()

Difference Between Int32.Parse(), Convert.ToInt32(), and Int32.TryParse()


Int32.parse(string)
Int32.Parse (string s)
method converts the string representation of a number to its 32-bit signed integer equivalent. When s is a null reference, it will throw ArgumentNullException. If s is other than integer value, it will throw FormatException. When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException. For example:

string s1 = "1234";
string s2 = "1234.65";
string s3 = null;
string s4 = "123456789123456789123456789123456789123456789";

int result;
bool success;

result = Int32.Parse(s1); //-- 1234
result = Int32.Parse(s2); //-- FormatException
result = Int32.Parse(s3); //-- ArgumentNullException
result = Int32.Parse(s4); //-- OverflowException

Convert.ToInt32(string)

Convert.ToInt32(string s) method converts the specified string representation of 32-bit signed integer equivalent. This calls in turn Int32.Parse () method. When s is a null reference, it will return 0 rather than throw ArgumentNullException. If s is other than integer value, it will throw FormatException. When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException.

For example:
result = Convert.ToInt32(s1); //-- 1234
result = Convert.ToInt32(s2); //-- FormatException
result = Convert.ToInt32(s3); //-- 0
result = Convert.ToInt32(s4); //-- OverflowException

Int32.Parse(string, out int) method converts the specified string representation of 32-bit signed integer equivalent to out variable, and returns true if it is parsed successfully, false otherwise. This method is available in C# 2.0. When s is a null reference, it will return 0 rather than throw ArgumentNullException. If s is other than an integer value, the out variable will have 0 rather than FormatException. When s represents a number less than MinValue or greater than MaxValue, the out variable will have 0 rather than OverflowException. For example:

success = Int32.TryParse(s1, out result); //-- success => true; result => 1234
success = Int32.TryParse(s2, out result); //-- success => false; result => 0
success = Int32.TryParse(s3, out result); //-- success => false; result => 0
success = Int32.TryParse(s4, out result); //-- success => false; result => 0
Convert.ToInt32 is better than Int32.Parse since it returns 0 rather than an exception. But again, according to the requirement, this can be used. TryParse will be the best since it always handles exceptions by itself.

Java script validations

Java script validations
<--script type ="text/javascript">

//Validating the user inpus in the form
function Validate() {
if (document.getElementById("<%=txtName.ClientID%>").value == "")
{
alert("Name is a mandatory field and it can not be empty");
document.getElementById("<%=txtName.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=txtAddress.ClientID%>").value == "")
{
alert("Address is a Mandatory field and it can not be empty");
document.getElementById("<%=txtAddress.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=txtLandMark.ClientID%>").value == "")
{
alert("LandMark is Mandatory field and it can not be empty");
document.getElementById("<%=txtLandMark.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=txtDOB.ClientID%>").value == "")
{
alert("Date of Birth is a Mandatory field and it can not be empty");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=ddlTitle.ClientID%>").value == "-select-")
{
alert("please select the title field");
document.getElementById("<%=ddlTitle.ClientID%>").focus();
return false;
}

}

/* address validation function */
function addressCheck()
{

var msg = " Address field can accept only \n alpha numerics \n white spaces and the following special characters \n";
msg = msg+ " . / # - ,";
var a = 1;
var iChars = "~`!@$%^&*()+=[]\';{}|\":<>?";
var txtval = '';
var strField = document.getElementById("<%=txtAddress.ClientID%>").value;
for (var i = 0; i < strField.length; i++)
{
if (iChars.indexOf(strField.charAt(i)) != -1)
{
a = 1;
break;
}
else
a = 2;
}

if (a == 1)
{
alert(msg);
document.getElementById("<%=txtAddress.ClientID%>").focus();
return false;
}
else if (a == 2)
{
return true;
}

}

/* land mark validation function */
function landMarkCheck()
{
var msg = " LandMark field can accept only \n alpha numerics \n white spaces and the following special characters \n";
msg = msg + " . ,";
var a = 1;
var iChars = "~`!#@$%^&*()+=-[]\\\';/{}|\":<>?";
var txtval = '';
var strField = document.getElementById("<%=txtLandMark.ClientID%>").value;
for (var i = 0; i < strField.length; i++) {
if (iChars.indexOf(strField.charAt(i)) != -1)
{
a = 1;
break;
}
else
a = 2;
}

if (a == 1)
{
alert(msg);
document.getElementById("<%=txtLandMark.ClientID%>").focus();
return false;
}
else if (a == 2) {
return true;
}
}
/* date validation */
function dateCheck() {
var s = document.getElementById("<%=txtDOB.ClientID%>").value;
var y = s.substring(6, 10);
var d = s.substring(0, 2);
var m = s.substring(3, 5);
var cdate = new Date();
var yy = cdate.getFullYear() - 1;
//checking the date is between 1910 to current date
if ((y >= 1900) && (y <= yy))
{
return true;
}
else if(y==yy+1)
{
if (m <= cdate.getMonth() + 1)
{
if (m == cdate.getMonth() + 1)
{
if (d <= cdate.getDate())
{
if (d == cdate.getDate());
return true;
}
else {
alert("Date of birth should be from 1910 to current date and also in the specified format ");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;
}
}

}
else {
alert("Date of birth should be from 1910 to current date and also in the specified format");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;

}
}
else {
alert("Date of birth should be from 1910 to current date and also in the specified format");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;
}


}

<--script type="text/javascript">




1. write scripts with in tag
2. call the functions from code behind using script registration.

example:

page scripts of : add or edit page



----------------------------------------------------------------------------------------------------------

script registration in code behind for add or edit page

protected void Page_Load(object sender, EventArgs e)
{
//checking page is
if (!Page.IsPostBack)
{
//here we can add the jscript functions to the required control's events
btnSubmit.Attributes.Add("OnClick", "return Validate();");
txtAddress.Attributes.Add("onblur", "return addressCheck();");
txtLandMark.Attributes.Add("onblur", "return landMarkCheck();");
txtDOB.Attributes.Add("onblur", "return dateCheck();");
}
}

---------------------------------------------------------------------------------------------------------
page script for : modify page



---------------------------------------------------------------------------------------------------------

script registration in code behind for modify page


if (!Page.IsPostBack)
{
if (gvTestDetails.Rows.Count != 0)
{ btnDelete.Attributes.Add("OnClick", "return pop();"); }
else
{
btnDelete.Attributes.Add("OnClick", "return pop1();");
btnEdit.Attributes.Add("OnClick", "return pop2();");

}
}