sql query to display parent name as manager if parent id is null

select e2.id,e2.name,isnull(e1.name,'Manager') as parentname
from emp e1
right outer join emp e2 on e2.parentId=e1.Id

sql query

select group_product.*,product.*,(select Sum(user_bid.bid_points) from user_bid where user_bid.product_id=group_product.product_id)
as totbid
from group_product,product
where group_product.product_id=product.product_id

Get driving directions on map.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <title></title>
    <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2">
    </script>
    <script type="text/javascript">
        var map = null;
        function GetMap() {
            map = new VEMap('myMap');
            map.LoadMap(new VELatLong(47.62054, -122.34947), 16, VEMapStyle.Road, false);
            var pinpoint = map.GetCenter();
            shape = new VEShape(VEShapeType.Pushpin, pinpoint);
            shape.SetTitle("Fourth Coffee Company");
            shape.SetDescription("This is where we are located!");
            map.AddShape(shape);
        }

        function GetDirections() {
            var what = document.getElementById('from').value;
            var options = new VERouteOptions();
            options.DrawRoute = true;
            options.SetBestMapView = false;
            options.RouteCallback = onGotRoute;
            map.GetDirections([what, map.GetCenter()], options);
        }

        function onGotRoute(route) {
            var legs = route.RouteLegs;
            var turns = "Total distance: " + route.Distance.toFixed(1) + " mi\n";
            var numTurns = 0;
            var leg = null;
            for (var i = 0; i < legs.length; i++) {
                leg = legs[i];
                var turn = null;
                for (var j = 0; j < leg.Itinerary.Items.length; j++) {
                    turn = leg.Itinerary.Items[j];
                    numTurns++;
                    turns += numTurns + ".\t" + turn.Text + " (" + turn.Distance.toFixed(1) + " mi)\n";
                }
            }
            document.getElementById("directions").value = turns;
        }
    </script>
</head>
<body onload="GetMap()">
    <h5>
        We are located in Downtown Seattle by the Seattle Space Needle</h5>
    <div id='myMap' style="position: relative; width: 600px; height: 400px;">
    </div>
    <br />
    <form name="form" action="" method="post">
    <input id="from" type="text" value="" name="From" />
    <input id="findit" type="button" value="Find It!" name="find" onclick="GetDirections();" />
    <br />
    <br />
    Your directions are:<br />
    <textarea id="directions" cols="50" rows="20" value=""></textarea>
    </form>
</body>
</html>

prompt a user at the time he leaves the current page using javasript.

Sometimes it happens that we have to prompt and ask the user that he is really willing to leave the current page or not. In my example, I have a survey on my site. when the user is in the progress of survey,suddenly he clicks on a link,that causes him to leave the page. At this time, I have to store its survey response to my database. Here is a simple javascript event to handle this situation.

  window.onbeforeunload = function () {

        return doSubmit(); //here doSubmit is my ajax method,that makes an ajax call and save the data.

    }

Or sometimes we have to clear the browser data when user leaves the page. This is a simple block of javascript.

Have fun.

Get Location Info from an IP address

First of all,you need to get an API key to use this service. You will get it free from http://ipinfodb.com/
After generating your API key,you need to call a service url with the IP (that you want to look up). The Response by the requested service returns in XML format.
Here is a simple running code.


 private DataTable GetLocation(string ipaddress)
    {
        DataTable dt = new DataTable();
        WebRequest wreq = WebRequest.Create("http://api.ipinfodb.com/v2/ip_query.php?key=<your key goes here>&timezone=true&ip=" + ipaddress);
        WebProxy proxy = new WebProxy("http://api.ipinfodb.com/v2/ip_query.php?key=<your key goes here>&timezone=true&ip=" + ipaddress, true);


        wreq.Proxy = proxy;
        wreq.Timeout = 5000 ;
        WebResponse wres = wreq.GetResponse();
        XmlTextReader xmlRead = new XmlTextReader(wres.GetResponseStream());
        DataSet ds = new DataSet();
        ds.ReadXml(xmlRead);
        return ds.Tables[0];


    }


To call above method,

 DataTable dt = GetLocation("117.194.12.179");


Enjoy.

paging in sql server stored procedure

CREATE  PROCEDURE dbo.PagingTest
(
    @PageNumber int,
    @PageSize int
)
AS


DECLARE @FirstId int, @FirstRow int


SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow


-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.


SELECT   @FirstId = [Id]
FROM     dbo.Country
ORDER BY [Id]


SET ROWCOUNT @PageSize


SELECT   *
FROM     dbo.Country
WHERE    [Id] >= @FirstId
ORDER BY [Id]


SET ROWCOUNT 0
GO 


===================================================
Just pass the page number and page size to the procedure.

javascript to select all checkboxes by parent checkbox and uncheck parent one if one of the child is unchecked

<script>
window.onload=function(){
            var cbChild = $("[id^='chkOpportunity_']");
                 cbChild.onchange=function(){
                         checkParent();
                  }  
}
$(document).ready(function () {
            checkParent();

          });
        function checkParent() {
            var cbkParent = document.getElementById('chkAll');
            var cbChild = $("[id^='chkOpportunity_']");
            for (var i = 0; i < cbChild.length; i++) {
                if (cbChild[i].checked) {
                    cbkParent.checked = true;
                }
                else {
                    cbkParent.checked = false;
                    break;
                }
            }
        }
</script>
<input type='checkbox' id='chkAll' />

<input type='checkbox' id=' chkOpportunity_1 ' />
<input type='checkbox' id=' chkOpportunity_2 ' />
<input type='checkbox' id=' chkOpportunity_3 ' />
<input type='checkbox' id=' chkOpportunity_4 ' />
<input type='checkbox' id=' chkOpportunity_5 ' />
<input type='checkbox' id=' chkOpportunity_6 ' />