SELECT * FROM (SELECT @rownum:=@rownum+1 ‘rank’, p.* FROM tblcity p, (SELECT @rownum:=0) r WHERE isactive =TRUE ORDER BY cityname ASC ) t WHERE rank>0 AND rank<50
custom paging query in mysql
June 23, 2010Select Top 3 value in mysql
June 22, 2010SELECT * FROM ( SELECT * FROM tbldestination ORDER BY distance DESC LIMIT 2) a ORDER BY distance LIMIT 1
Capture Event, when browser closed
May 27, 2010
{
DAL.Updatestatus(Session["UserName"].ToString(), false);
}
//Event for logout
Inactive();
}
var status = document.activeElement;
if(status ==null)
{
}
</script>
//call the javascript method on page unload.
Redirect page with out flickering in donnet
October 29, 2009ajaxObj.Open(
}
dataadapter update method
February 21, 2009//Update the records using update method of dataadapter object
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString() );
cmd.Connection = con;
SqlCommand upCmd = new SqlCommand(“update authors set au_lname=@au_lname where au_id=@au_id”, con);
upCmd.Parameters.Add(“@au_lname”, SqlDbType.VarChar, 40, “au_lname”);
upCmd.Parameters.Add(“@au_id”, SqlDbType.VarChar , 11, “au_id”);
da.UpdateCommand = upCmd;
da.Update(ds);
Here parameter values “au_lname” and “au_id” are column names in dataset.
Sql server date time functionality
August 20, 2008Select * from users where convert(char,createddate,101)=’07/29/2008′
select cast(getdate() as char)
Resetting the table identity values
August 20, 2008To reset the identity value in database table
Syntax: DBCC checkident (tablename, reseed, 0)
DBCC checkident can reseed the value of the table. For e.g.: our table has 25 records with 25 as last identity. If we want next record to have identity has 35 we need to run following T-SQL
DBCC checkidetnt (tablename, reseed, 34)
Disadvantage: It will violate the uniqueness.
Copying one table values into another table.
August 20, 2008INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
–Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
IF it is in different database.
INSERT INTO SmithNephewStoreFront.dbo.category ( [Name],parentcategoryID)
SELECT category,subcatid
FROM endo.dbo.category
WHERE catid = 2
resetting the identity column in the database table .
August 20, 2008
DBCC CHECKIDENT (category, RESEED, 0)
Assign a value to a textbox which is in password mode
August 20, 2008Assign a value to a textbox which is in password mode
[ Assign password value to a text box]
txtPassword.Attributes.Add(“value”, password);