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//Here i’m updating the login status of user when user closed the browser.
//Write a method to update the status
protected void Inactive()
{
DAL.Updatestatus(Session["UserName"].ToString(), false);
}
//Event for logout
protected void Button1_Click(object sender, EventArgs e)
{
Inactive();
}
//Javascript method to capture the browser close event.
//If user clicks on browser close document readystate is ‘Complete’ else ‘Loading’
<script type=”text/javascript”>
function abc()
{
var status = document.activeElement;
if(status ==null)
{
document.getElementById(‘<%=Button1.ClientID %>’).click();}
}
</script>
//call the javascript method on page unload.
<body onunload=”abc();” >
Redirect page with out flickering in donnet
October 29, 2009Redirecting from one page to another will get postback flicker. If use the following code it will not get flicker.
<script type=”text/javascript”>
function DoAsyncRequest(){
ajaxObj.Open(
var ajaxObj = new ActiveXObject(“Microsoft.XMLHTTP”); “POST”, “http://localhost:5924/Charts/default.aspx”, false); //ajaxObj.Open(“POST”, “http://www.microsoft.com”, false);
ajaxObj.SetRequestHeader(
ajaxObj.Send();
{
document.write(ajaxObj.responseText);
}
}
“Content-Type”, “application/x-www-form-urlencoded”); if (ajaxObj.status == 200)
</
script>
// Code behind
Button2.OnClientClick =
“javascript:DoAsyncRequest();return false;”;
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);
