Last 5 Received date in Item ledger F4111 JD Edwards

drop table jde_rk.dbo.max250date
select distinct illitm,ilmcu,max(iltrdj)iltrdj into jde_rk.dbo.max250date from proddta.f4111
where ildcto in('oh','ot') and ildct='ov' and ilkcoo=250 --and illitm='1618687'
group by illitm,ilmcu
order by iltrdj

drop table jde_rk.dbo.max250date1
select a.illitm,a.ilmcu, max(b.iltrdj)iltrdj into jde_rk.dbo.max250date1 from proddta.f4111 b
left outer join jde_rk.dbo.max250date a on a.illitm=b.illitm and a.ilmcu=b.ilmcu
where a.iltrdj >b.iltrdj
group by a.illitm,a.ilmcu
order by iltrdj desc

drop table jde_rk.dbo.max250date2
select a.illitm,a.ilmcu, max(b.iltrdj)iltrdj into jde_rk.dbo.max250date2 from proddta.f4111 b
left outer join jde_rk.dbo.max250date1 a on a.illitm=b.illitm and a.ilmcu=b.ilmcu
where a.iltrdj >b.iltrdj
group by a.illitm,a.ilmcu
order by iltrdj desc

drop table jde_rk.dbo.max250date3
select a.illitm,a.ilmcu, max(b.iltrdj)iltrdj into jde_rk.dbo.max250date3 from proddta.f4111 b
left outer join jde_rk.dbo.max250date2 a on a.illitm=b.illitm and a.ilmcu=b.ilmcu
where a.iltrdj >b.iltrdj
group by a.illitm,a.ilmcu
order by iltrdj desc

drop table jde_rk.dbo.max250date4
select a.illitm,a.ilmcu, max(b.iltrdj)iltrdj into jde_rk.dbo.max250date4 from proddta.f4111 b
left outer join jde_rk.dbo.max250date3 a on a.illitm=b.illitm and a.ilmcu=b.ilmcu
where a.iltrdj >b.iltrdj
group by a.illitm,a.ilmcu
order by iltrdj desc

Find Nth ( 2nd, 3rd max ) maximum value in SQL Server

Find Nth maximum value in SQL Server

Use Pubs
Go

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)
Go

Insert into Employee values (1,'harry',3500)
Insert into Employee values (2,'jack',2500)
Insert into Employee values (3,'john',2500)
Insert into Employee values (4,'xavier',5500)
Insert into Employee values (5,'steven',7500)
Insert into Employee values (6,'susana',2400)
Go

 

 

Select * From Employee E1 Where
    (0) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

Select * From Employee E1 Where
    (1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
....

....
http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server

Distance Calculation Between Two Address using Free API , c#, ASP.Net 2.0

Distance calculation Between two address using API , c#,ASP.Net 2.0

 

Distance calculation Between two address using API , c#,ASP.Net 2.0

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="distcalcul.aspx.cs" Inherits="distcalcul" %>

<!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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table align="center">
            <tr>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                    <asp:Label ID="Label8" runat="server" Text="From Address"></asp:Label></td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label1" runat="server" Text="Address1"></asp:Label></td>
                <td style="width: 100px">
                    <asp:TextBox ID="TextBox1" runat="server">71 Fortune Drive</asp:TextBox></td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label2" runat="server" Text="City:"></asp:Label></td>
                <td style="width: 100px">
                    <asp:TextBox ID="TextBox2" runat="server">Irvine</asp:TextBox></td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label3" runat="server" Text="State"></asp:Label></td>
                <td style="width: 100px">
                    <asp:TextBox ID="TextBox3" runat="server">CA</asp:TextBox></td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px; height: 21px">
                </td>
                <td style="width: 100px; height: 21px">
                    <asp:Label ID="Label9" runat="server" Text="To Address"></asp:Label></td>
                <td style="width: 100px; height: 21px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px; height: 21px">
                    <asp:Label ID="Label4" runat="server" Text="Address:"></asp:Label></td>
                <td style="width: 100px; height: 21px">
                    <asp:TextBox ID="TextBox4" runat="server">833 W. Torrance Blvd.</asp:TextBox></td>
                <td style="width: 100px; height: 21px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label5" runat="server" Text="City"></asp:Label></td>
                <td style="width: 100px">
                    <asp:TextBox ID="TextBox5" runat="server">Torrance</asp:TextBox></td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label6" runat="server" Text="State"></asp:Label></td>
                <td style="width: 100px">
                    <asp:TextBox ID="TextBox6" runat="server">CA</asp:TextBox></td>
                <td style="width: 100px">
                    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Submit" /></td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label10" runat="server"></asp:Label></td>
                <td style="width: 100px">
                    <asp:Label ID="Label7" runat="server" Width="76px"></asp:Label></td>
                <td style="width: 100px">
                </td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html>

code Behind

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;

public partial class distcalcul : System.Web.UI.Page
{
    double flatitude = 0;
    double flongitude = 0;
    double tlatitude = 0;
    double tlongitude = 0;
    
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string street=TextBox1.Text;
        string city = TextBox2.Text;
        string state = TextBox3.Text;
       
        fdistcal(street,city, state);

        string tstreet = TextBox4.Text;
       string tcity = TextBox5.Text;
       string tstate = TextBox6.Text;
      tdistcal(tstreet, tcity, tstate);

      distanceCal(flatitude, flongitude, tlatitude, tlongitude, 'M');
      //Response.Write(distanceCal(flatitude, flongitude, tlatitude, tlongitude, 'K') * 0.62);
      double totdist = distanceCal(flatitude, flongitude, tlatitude, tlongitude, 'K') * 0.62;
   //ToString("#0.00"));

      Label7.Text = totdist.ToString("#0.000");
      Label10.Text = "Distance(Miles)";
       
    }

    void fdistcal(string street, string city, string state)
    {
        string geocoderUri = string.Format("http://rpc.geocoder.us/service/rest?address={0},{1},{2}", street, city, state);
        XmlDocument geocoderXmlDoc = new XmlDocument();
        geocoderXmlDoc.Load(geocoderUri);
        XmlNamespaceManager nsMgr = new XmlNamespaceManager(geocoderXmlDoc.NameTable);
        nsMgr.AddNamespace("geo", @"http://www.w3.org/2003/01/geo/wgs84_pos#");
        string sLong = geocoderXmlDoc.DocumentElement.SelectSingleNode(@"//geo:long", nsMgr).InnerText;
        string sLat = geocoderXmlDoc.DocumentElement.SelectSingleNode(@"//geo:lat", nsMgr).InnerText;
        double latitude = Double.Parse(sLat);
        double longitude = Double.Parse(sLong);
    //   Response.Write("Latitude: " + latitude + "<br /> Longitude: " + longitude);
        flatitude=latitude;
        flongitude=longitude;
  
    }
    void tdistcal(string street, string city, string state)
    {
        string geocoderUri = string.Format("http://rpc.geocoder.us/service/rest?address={0},{1},{2}", street, city, state);
        XmlDocument geocoderXmlDoc = new XmlDocument();
        geocoderXmlDoc.Load(geocoderUri);
        XmlNamespaceManager nsMgr = new XmlNamespaceManager(geocoderXmlDoc.NameTable);
        nsMgr.AddNamespace("geo", @"http://www.w3.org/2003/01/geo/wgs84_pos#");
        string sLong = geocoderXmlDoc.DocumentElement.SelectSingleNode(@"//geo:long", nsMgr).InnerText;
        string sLat = geocoderXmlDoc.DocumentElement.SelectSingleNode(@"//geo:lat", nsMgr).InnerText;
        double latitude = Double.Parse(sLat);
        double longitude = Double.Parse(sLong);
        //Response.Write("Latitude: " + latitude + "<br /> Longitude: " + longitude);
        tlatitude = latitude;
        tlongitude = longitude;
       

 

    }

    private double distanceCal(double Lat1, double Long1, double Lat2, double Long2, char unit)
    {
        double dDistance = Double.MinValue;
        double dLat1InRad = Lat1 * (Math.PI / 180.0);
        double dLong1InRad = Long1 * (Math.PI / 180.0);
        double dLat2InRad = Lat2 * (Math.PI / 180.0);
        double dLong2InRad = Long2 * (Math.PI / 180.0);

        double dLongitude = dLong2InRad - dLong1InRad;
        double dLatitude = dLat2InRad - dLat1InRad;

        // Intermediate result a.
        double a = Math.Pow(Math.Sin(dLatitude / 2.0), 2.0) +Math.Cos(dLat1InRad) * Math.Cos(dLat2InRad) *Math.Pow(Math.Sin(dLongitude / 2.0), 2.0);

        // Intermediate result c (great circle distance in Radians).
        double c = 2.0 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1.0 - a));

        // Distance.
        // const Double kEarthRadiusMiles = 3956.0;
        const Double kEarthRadiusKms = 6376.5;
        dDistance = kEarthRadiusKms * c;

        return dDistance;

        //return (dist);
    }

    

}

 

Re: Get Longitude and Latitude from an Address sample, C# asp.net 2.0,3.5

using

System;

using

System.Data;

using

System.Configuration;

using

System.Collections;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

using

System.Xml;

public

partial class Default2 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

string street = "1600 Pennsylvania Ave";

string city = "Washington";

string state = "DC";

//string zip = "20500";

string geocoderUri = string.Format("http://rpc.geocoder.us/service/rest?address={0},{1},{2}", street, city, state);

XmlDocument geocoderXmlDoc = new XmlDocument();

geocoderXmlDoc.Load(geocoderUri);

XmlNamespaceManager nsMgr = new XmlNamespaceManager(geocoderXmlDoc.NameTable);

nsMgr.AddNamespace(

"geo", @"http://www.w3.org/2003/01/geo/wgs84_pos#");

string sLong = geocoderXmlDoc.DocumentElement.SelectSingleNode(@"//geo:long", nsMgr).InnerText;

string sLat = geocoderXmlDoc.DocumentElement.SelectSingleNode(@"//geo:lat", nsMgr).InnerText;

double latitude = Double.Parse(sLat);

double longitude = Double.Parse(sLong);

Response.Write(

"Latitude: " + latitude + "<br /> Longitude: " + longitude);

}

}

Delete duplicate records SQL-SERVER

select count(*)cnt,ibitm into jde_rk.dbo.ttt_11 from jde_rk.dbo.tttt_4102 group by ibitm  having count(*)>1

DELETE jde_rk.dbo.tttt_4102 

FROM jde_rk.dbo.tttt_4102 a, jde_rk.dbo.ttt_11 b

WHERE a.ibitm=b.ibitm

AND t1.col2 = holdkey.col2

Open Window on click (Select) on gridview asp.net c# 2.0

protected

void gridViewMaster_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

e.Row.Attributes[

"onmouseover"] = "this.style.cursor='hand';this.style.textDecoration='underline';";

e.Row.Attributes[

"onmouseout"] = "this.style.textDecoration='none';";

e.Row.Attributes[

"onclick"] = ClientScript.GetPostBackClientHyperlink(this.GridView1, "Select$" + e.Row.RowIndex);

//e.Row.Attributes["onclick"] = ClientScript.GetPostBackClientHyperlink(this.GridView1, "Select$" + e.Row.RowIndex);

e.Row.Attributes.Add(

"onclick", "window.navigate('editrawdata.aspx?LeadID=" + DataBinder.Eval(e.Row.DataItem, "UKID") + "','myprofilewindow','width=400,height=600,toolbar=0,resizable=0');");

}

Dynamic CssClass for textbox c# code behind

 

txtOwnerFirstName.CssClass =

"txtbxrep";

 

ListView Sample - Asp.net 3.5 C# vb with datapager

When I first did program got following error:

An item placeholder must be specified on ListView 'ListView1'. Specify an item placeholder by setting a control's ID property to "itemPlaceholder". The item placeholder control must also specify runat="server".

after google many site got help and created first ListView program using sql-server.

 

 

 

<%

@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!

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>Untitled Page</title>

</

head>

<

body>

<form id="form1" runat="server">

<div>

<asp:ListView ID="ListView1" runat="server" DataSourceID="SqlDataSource1" ItemPlaceholderID="LeadRep">

<LayoutTemplate>

<div id="LeadRep" runat="server"></div>

</LayoutTemplate>

<ItemTemplate>

<div><%# DataBinder.Eval(Container.DataItem,"LeadType") %></div>

<div><%# DataBinder.Eval(Container.DataItem,"HomePhone") %></div>

</ItemTemplate>

</asp:ListView>

<asp:DataPager ID="DataPager1" runat="server" PagedControlID="ListView1" PageSize="5">

<

fields><asp:nextpreviouspagerfield ButtonType="Button" ShowFirstPageButton="True" ShowNextPageButton="False" ShowPreviousPageButton="False" /><asp:numericpagerfield />

<

asp:nextpreviouspagerfield ButtonType="Button" ShowLastPageButton="True" ShowNextPageButton="False"

ShowPreviousPageButton

="False" />

</

fields>

</

asp:DataPager>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:SalesforceConnectionString %>"

SelectCommand="SELECT * FROM [SF_TranData]"></asp:SqlDataSource>

</div>

</form>

</

body>

</

html>

Disable Mouse While Processing Data C# asp.net vb 2.0

Works fine with master file also...

<html>
<head>
   <style type="text/css">
      .LockOff {
         display: none;
         visibility: hidden;
      }

      .LockOn {
         display: block;
         visibility: visible;
         position: absolute;
         z-index: 999;
         top: 0px;
         left: 0px;
         width: 105%;
         height: 105%;
         background-color: #ccc;
         text-align: center;
         padding-top: 20%;
         filter: alpha(opacity=75);
         opacity: 0.75;
      }
   </style>

   <script type="text/javascript">
      function skm_LockScreen(str)
      {
         var lock = document.getElementById('skm_LockPane');
         if (lock)
            lock.className = 'LockOn';

         lock.innerHTML = str;
      }
   </script>
</head>
<body>
   <h2>Overlay Test Page</h2>
   <p>
      Please enter your name:
      <input type="text" name="name" />
   </p>
   <p>
      <input type="submit" value="Click Me!" onclick="skm_LockScreen('We are processing your request...');" />
   </p>

   <div id="skm_LockPane" class="LockOff"></div>
</body>
</html>

 

http://aspnet.4guysfromrolla.com/articles/121708-1.aspx

 

Count Record using asp.net C# , count(*)

 SqlConnection cnn = new SqlConnection("server=DBSERVER;User id=USER;password=PASSWORD;database=DB");
  SqlCommand cmd = new SqlCommand("Select count(*) from abctest", cnn);
  cnn.Open();
  Response.Write(cmd.ExecuteScalar().ToString());

Accesss database Error Field 'Database field' cannot be a zero-length string.

You can receive this error message if you modify the PDshop database column properties incorrectly. Most PDshop database fields (aka columns) should allow a zero-length (NULL) value.

To resolve this error, Using the Microsoft Access program, go into "Design View" for the table mentioned in the error. You would need to change the column properties for the field named in the error. For example, change the "General" setting for the column/field, the setting to change is 'Allow Zero Length', the value should be set to 'Yes'.

Open a popup Window from C# code behind asp.net 2.0

open a popup window from C# code behind

Sample:

    protected void LinkButton1_Click(object sender, EventArgs e)

    {

        //File Receiveed/Assign to Processor

        string url = "uplfile.aspx?strtxt=File Receiveed/Assign to Processor&caseno=" + Request["caseno"];

        ClientScript.RegisterStartupScript(this.GetType(), "newWindow", String.Format("<script>window.open('{0}');</script>", url));

        

        //Response.Redirect("uplfile.aspx?strtxt=File Receiveed/Assign to Processor&caseno=" + Request["caseno"]);

    }

        

Get Program Name in aspx page C#

System.IO.Path.GetFileName(Request.ServerVariables["SCRIPT_NAME"]

Download Windows 7 beta on January 9th' 2009

January 9th PST for a limited time to the first 2.5 million people who download the beta.

http://www.microsoft.com/windows/windows-7/

 

MacBook Wheel From Apple

Apple has done it again with the announcement of the MacBook Wheel - the first laptop to get rid of the keyboard.