Monday, February 23, 2009

How to get Nth maximum value using sql query?

Please refer
To find 2nd maximum value using sql query in Prog. Finidngs .

I would like to take the same table considered in above link to find Nth maximum value.

Let us consider a database table named “Test” and which has a column named “C1”.
Let the data type of C1 be Number
Script:-

CREATE TABLE test
(
c1 NUMBER
)
I have inserted some random data using
INSERT INTO TEST
SELECT ROUND(dbms_random.value(1,1000),0) FROM dual
Multiple times to insert multiple records.
I have inserted 66 records into this test table



Data (first 30 records)



So we can observe that there are many number of records among which the record with maximum value of C1 with my test data is
select max(c1) from test
983




Now my intention is to find a record whose c1 value should be the highest nth value.
Query:-
I would like to get the 10th highest value of C1 from test table highlighted in below screen shot.
SELECT * FROM TEST ORDER BY c1 DESC




So,
SELECT c1 FROM
(SELECT ROWNUM AS MAXINDEX,c1 FROM
(SELECT * FROM TEST ORDER BY c1 DESC)) WHERE MAXINDEX=10
helps me to get the Nth maximum value.
Innermost query gives me set of records from Test table in descending order of C1 column, the next inner query helps to identify the ordered records based on “Rownum”.
Using this rownum, external query can identify specific record at Nth position.
My test data which has 10th maximum value is
853
So i am getting 10th highest value from the column of the table.


How to get second maximum value using sql query?

Let us consider a database table named “Test” and which has a column named “C1”.
Let the data type of C1 be Number
Script:-

CREATE TABLE test
(
c1 NUMBER
)
I have inserted some random data using
INSERT INTO TEST
SELECT ROUND(dbms_random.value(1,1000),0) FROM dual
Multiple times to insert multiple records.
I have inserted 66 records into this test table



Data (first 30 records)



So we can observe that there are many number of records among which the record with maximum value of C1 with my test data is
select max(c1) from test
983




Now my intension is to find a record whose c1 value is second highest value.
Query:-
Inner query gives the maximum value available which is 983, so now i am saying to find max value again but whose value should be less than 983 (value from inner query).
Obviously, it is next maximum value.
SELECT MAX(c1) FROM TEST where c1<(SELECT MAX(c1) FROM TEST)
946
So i am getting second highest value from the column of the table.


Progress bar in windows applications .NET.

.NET frame work supports few classes that help to place Progress bar in User Interface of windows applications.




I am creating a windows form that displays this progress bar.
User interface (Windows form) :




Code :










using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
namespace DeleteSvn
{
public partial class frmPrgs : Form
{
public frmPrgs()
{
InitializeComponent();
}

private void btnStart_Click(object sender, EventArgs e)
{
//Get input counter value
intCounter = Convert.ToInt32(txtTime.Text);
//set this counter value as maximum value for the progress bae
prg.Maximum = intCounter;
//start a thread that sleeps for every 1 second and updates the progress bar
//to next step after each second
Thread objThread = new Thread(this.RunThread);
//start the thread
objThread.Start();
}
int intCounter = 0;
delegate void UpdPrcs(string strStatusint,int intStep);
private void UpdateProcess(string strStatus, int prcStep)
{
if (InvokeRequired)
{
BeginInvoke(new UpdPrcs(this.UpdateProcess), new object[] { strStatus,prcStep});
return;
}
//Updating the components available in wodnows form from the thread
prg.Value=prcStep;
lblStaus.Text = "Status : " + strStatus;
if (prcStep == intCounter)
{
prg.Value = 0;
lblStaus.Text = "Done!";
MessageBox.Show("Done!");
}
}
private void RunThread()
{
int i = 0;
//increment counter and update UI with the status of counter and progress bar
while (i < intCounter)
{
i++;
UpdateProcess(i + " / " + intCounter, i);
Thread.Sleep(1000);
}

}
}
}
Output:-

When I click on start button after giving value as 10,
So it increments progress bar for each second and when time reaches 10 seconds progress bar is done with 100% and displays message box.










How to implement sorting using .NET Grid view.

ASP.NET Grid view control helps developer to implement technique of sorting a column very easily and effectively.
Grid view has an attribute saying “AllowSorting” which is Boolean and when set to true enables sorting functionality for the grid.
I would like to create a asp.net page that has
1) Text box (Expecting count from user to generate random number from 1 to 100).
2) Button (User when clicks on the button after entering count in the text box, it generates random numbers and bind to grid view).
3) Grid view to display generated random numbers and provide sorting technique.
My aspx page



Code behind page

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;

public partial class Sorting : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
private void GenerateNumbersAndBindGrid()
{

}
protected void grdSort_Sorting(object sender, GridViewSortEventArgs e)
{
BindGrid(e.SortExpression,(e.SortDirection==SortDirection.Ascending)?"Asc":"Desc");
}
private void BindGrid(string strSortExp,string strSortDire)
{
int intCount = Convert.ToInt32(txtCount.Text);
DataTable dt = Session["DT"] as DataTable;
if (dt == null)
{
Random objRandom = new Random();
DataRow dRow = null;
dt = new DataTable();
dt.Columns.Add(new DataColumn("Num", System.Type.GetType("System.Int32")));
for (int i = 1; i < intCount; i++)
{
dRow = dt.NewRow();
dRow["Num"] = objRandom.Next(100);
dt.Rows.Add(dRow);
}
Session["DT"] = dt;
}
DataView objView = new DataView(dt);
objView.Sort= strSortExp + " " + strSortDire;
grdSort.DataSource = objView;
grdSort.DataBind();
}
private void BindGrid()
{
int intCount = Convert.ToInt32(txtCount.Text);
DataTable dt = Session["DT"] as DataTable;
if (dt == null)
{
Random objRandom = new Random();
DataRow dRow = null;
dt = new DataTable();
dt.Columns.Add(new DataColumn("Num", System.Type.GetType("System.Int32")));
for (int i = 1; i < intCount; i++)
{
dRow = dt.NewRow();
dRow["Num"] = objRandom.Next(100);
dt.Rows.Add(dRow);
}
Session["DT"] = dt;
}
grdSort.DataSource = dt;
grdSort.DataBind();
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
Session["DT"]=null;
BindGrid();
}
}
Aspx page:

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

<!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>Sorting</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table><tr><td>
Count :
</td> <td><asp:TextBox ID="txtCount" runat="server"></asp:TextBox></td></tr>
<tr><td> </td><td><asp:Button ID="btnGenerate" runat="server" Text="Generate" OnClick="btnGenerate_Click" /></td></tr>
<tr><td colspan="2">
<asp:GridView ID="grdSort" runat="server" AllowSorting="true" OnSorting="grdSort_Sorting">

</asp:GridView>
</td></tr>
</table>
</div>
</form>
</body>
</html>

Output:-









How to read entity value from Xml File .NET?

Please refer regarding entities at
How to create an entity in xml file at Prog. Findings
How to create an entity in xml file using .NET functions at Prog. Findings

As said about entities in above links, each entity defined in an xml file can be identified and referred using entity name.
Ex:-


&Entity1; in an inner text of a xml tag says Desc when it is loaded.

So to read these entity values we can refer them using there respective names.
Below function is to read entity value for a given entity name.
public string GetEntityValue(string strEntityName)
{
if (_xmlDocument != null)
{
return _xmlDocument.DocumentType.Entities.GetNamedItem(strEntityName).InnerText;
}
return String.Empty;
}

Below function is to get Entity name based upon given entity value.

public string GetEntityName(string strEntityValue)
{
string strEntityName = String.Empty;
if (_xmlDocument != null)
{
for (int i = 0; i < _xmlDocument.DocumentType.Entities.Count; i++)
{
if(_xmlDocument.DocumentType.Entities.Item(i).InnerText.Trim()==strEntityValue.Trim())
return _xmlDocument.DocumentType.Entities.Item(i).Name;
}
}
return strEntityName;
}
Below function is to get a lost all available entities with in an xml file.

public ArrayList GetEntityNames()
{
ArrayList objListEntities = new ArrayList();
if(_xmlDocument!=null)
{
for (int i = 0; i < _xmlDocument.DocumentType.Entities.Count; i++)
{
objListEntities.Add(_xmlDocument.DocumentType.Entities.Item(i).Name);
}
objListEntities.Sort();
}
return objListEntities;
}

This function returns array list which can further be used to bind list of values to drop down list.

Friday, February 20, 2009

How to create entity in an Xml file .NET?

Please refer
Entity in Prog.Findings

.NET frame work has provided classes and methods which can be used to create entities in an xml file. I mean to create or delete or update an entity.

Below function is to delete an entity that exists in an xml file.

public void DeleteEntity(string strEntityName,bool blnChkForDependants)
{
if (_xmlDocument != null)
{
XmlDocumentType _oldXmlDocumentType = _xmlDocument.DocumentType;
if(blnChkForDependants)
{
TextReader tr = new StreamReader(FilePath);
if (tr.ReadToEnd().Contains("&" + strEntityName + ";"))
{
tr.Close();
throw new Exception("Sorry you cannot delete this entity, there exists dependants on this " + strEntityName + " entity in xml file");
}
tr.Close();
}
string _strExistingSubSet = _oldXmlDocumentType.InternalSubset;
string _strFromEntity = _strExistingSubSet.Substring(_strExistingSubSet.IndexOf(" int intEntityEndIndex = _strFromEntity.IndexOf(">");
string _strEntityString = _strFromEntity.Substring(0,intEntityEndIndex+1);
_strExistingSubSet = _strExistingSubSet.Replace(_strEntityString, String.Empty);
XmlDocumentType _newXmlDocumentType = _xmlDocument.CreateDocumentType(_oldXmlDocumentType.Name, _oldXmlDocumentType.PublicId, _oldXmlDocumentType.SystemId, _strExistingSubSet);
_xmlDocument.ReplaceChild(_newXmlDocumentType, _oldXmlDocumentType);
_xmlDocument.Save(FilePath);
}
}
Below function is to save an exisiting entity or create new entity in xml file
public void SaveEntity(string strEntityName,string strValue)
{
if (_xmlDocument != null)
{
XmlNode objEntityNode = _xmlDocument.DocumentType.Entities.GetNamedItem(strEntityName);
if (objEntityNode != null)
{
XmlDocumentType _oldXmlDocumentType = _xmlDocument.DocumentType;
string _strExistingSubSet = _oldXmlDocumentType.InternalSubset;
string _strFromEntity = _strExistingSubSet.Substring(_strExistingSubSet.IndexOf(" int intEntityEndIndex = _strFromEntity.IndexOf(">");
string _strEntityString = _strFromEntity.Substring(0, intEntityEndIndex + 1);
_strExistingSubSet = _strExistingSubSet.Replace(_strEntityString,"").Trim();
XmlDocumentType _newXmlDocumentType = _xmlDocument.CreateDocumentType(_oldXmlDocumentType.Name, _oldXmlDocumentType.PublicId, _oldXmlDocumentType.SystemId, _strExistingSubSet);
_xmlDocument.ReplaceChild(_newXmlDocumentType, _oldXmlDocumentType);
}
else
{
XmlDocumentType _oldXmlDocumentType = _xmlDocument.DocumentType;
XmlDocumentType _newXmlDocumentType = _xmlDocument.CreateDocumentType(_oldXmlDocumentType.Name, _oldXmlDocumentType.PublicId, _oldXmlDocumentType.SystemId, _oldXmlDocumentType.InternalSubset + "\n ");
_xmlDocument.ReplaceChild(_newXmlDocumentType, _oldXmlDocumentType);
}
_xmlDocument.Save(FilePath);
}

}

How to add launch condition while preparing set up in Visual studio

Please refer for preparing set up at
Prog. Finidngs (Set up)
I am creating separate project in visual studio editor and choosing an
.exe file for which I am willing to prepare set up.



One of Visual studio configuration properties help to check whether the client machine (which is about to install this .MSI file) has required installations or
Prerequisites software’s to support current installing application.
For example for the set up file which I am preparing now when it gets installed, the machine should have abc.exe file in alphabets folder of Program files.
This is called as launch condition.
I have clicked on the set up project and clicked on “Launch Condition Editor”.



I am adding my launch condition as said before.









Now I am building my solution and installing the set up file created in my set up project folder.



As soon as I click on the set up file created I am getting a message box and not able to install the set up.


How to prepare set up using Visual studio

Visual studio configuration options helps to create a set up or .MSI file very easily.
We should create a separate project saying set up project as below template.
When we try to create a set up, we should right click on the solution file in Visual studio editor.
Using Visual studio we can browse set of files (.Exe’s or .Dll’s etc…) which support for an application to be runned.
I am considering a sample project which has been created for demo.
Note:- Before creating a set up project be sure the application for which you are willing to prepare must be builded and that too in release mode.

1) So now I am right clicking on my solution file and saying “Add new Project”
Then a dialog appears to select type and name of new project.
I am selecting “Set up project” because my current project for which I am preparing set up is windows application.



2) After giving project name (Setup1) and clicking on Ok
I can see the windows splitted with 3 options in UI.




3) Now I am right clicking on each 3 options and setting required options that are to be done after installing this set up file.
I have chosen an .Exe file I have




Setting an option to create shortcut on desktop while installing this set up file



Setting an option to create shortcut on programs menu while installing this set up file





Note:- You can also find many set up properties by looking into properties window of “Application folder”, “Set up” project etc…
4) Now I am building my entire solution in “release” mode.
5) After building successfully I found the set up files (.exe and .msi) at the set up project folder.



6) I am installing this set up file now.




I have done with my installation setting folder where do I need to create.



I can find short cut created on my desk top




I can find short cut created in programs menu




After clicking on the shot icon, I can find my windows form for which I have prepared set up.


Phone number formatting using javascript

Web sites which do give option for phone number should follow an universal format of arranging text boxes in an order like country code – STD – Line number.

I would like to create a html page that has 3 text boxes prompting for Country code, STD and Line number.






I am using 2 javascript functions, one of which is called on key press event of each text box (to validate character typed in by user is numeric) and the other function is to automatically set the focus to next text box when current text box value reaches maximum length.


<!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>
<title>Demo</title>
<script type="text/javascript" language="javascript">
function KyP(obj1)
{
var vl=String.fromCharCode(event.keyCode);
if(!IsNumber(vl))
return false;
return true;
}
function IsNumber(argVal)
{
var nmr='0123456789';
for(var i=0;i<argVal.length;i++)
{
if(nmr.indexOf(argVal.charAt(i))==-1)
return false;
}
return true;
}
function KyUp(obj1,obj2)
{

if(obj1.maxLength==obj1.value.length)
{
if(obj2==null)
{
alert('Thank you');
return;
}
obj2.focus();
}
}

</script>
</head>
<body>
<table>
<tr><td colspan="5">Please enter your land line number</td></tr>
<tr><td>Country code</td><td>-</td><td>STD</td><td>-</td><td>Line number</td></tr>
<tr><td><input type="text" id="txt1" maxlength="4" onkeypress="return KyP(this);" onkeyup="KyUp(this,document.getElementById('txt2'));"/></td><td>-</td><td><input type="text" id="txt2" maxlength="2" onkeypress="return KyP(this);" onkeyup="KyUp(this,document.getElementById('txt3'));"/></td><td>-</td><td><input type="text" id="txt3" maxlength="8" onkeypress="return KyP(this);" onkeyup="KyUp(this,null);"/></td></tr>
</table>
</body>
</html>





Saturday, February 14, 2009

How to move images in a html page relatively?

Html image elements have style property in which we can set position of the image as relative.
If we say that the position of an image is ‘relative’, upon setting left value for that image it calculates the left value by considering the given value as relative from the current left.
Say, If current Left of the image is at X, then by saying now Image.LEFT=Z then it calculates an exact Left as X+Z as we said the position of the image is relative.
Where as if we say position as ‘absolute’, the given value Z for left will be the same value when image is placed in browser at same Z point.

I would to share my Html page in which I am reading images available in the current browser or document , there after setting there position as ‘relative’ and then moving those images in rectangular using timer.
Html page:

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

</head>
<body>
<center>
<table>
<tr><td>
<img src="Images/2.gif" /></td></tr>
<tr><td><img src="Images/3.gif" /></td></tr>
<tr><td><img src="Images/4.gif" /></td></tr>
<tr><td><img src="Images/5.gif" /></td></tr>
<tr><td><img src="Images/6.gif" /></td></tr>
<tr><td><img src="Images/contract.gif"/>
</td></tr></table>
</center>
<script type="text/javascript" language="javascript">
var a=document.images;
function strt()
{
for(i=0; i<a.length; i++)
{
a[i].style.position='relative';
}
disp();
}
var indxShft=50;var fct=50;var dir=1;
var tmr;
function disp()
{
for(i=0; i<a.length; i++)
{
if(dir%2==0)
a[i].style.top=indxShft;
else
a[i].style.left=indxShft;
}
dir++;
if(dir>4)
dir=1;
if(dir<=2)
indxShft=fct;
else
indxShft=(-1 * fct);
tmr= setTimeout('disp();',100);
}
strt();
</script>
</body>
</html>
Or

You can just copy the below and paste in your address bar

javascript:a=document.images; for(i=0; i<a.length; i++){a[i].style.position='relative'}; indxShft=50;fct=50;dir=1; setInterval('for(i=0; i<a.length; i++){if(dir%2==0) a[i].style.top=indxShft;else a[i].style.left=indxShft};dir++;if(dir>4) dir=1;if(dir<=2)indxShft=fct;else indxShft=(-1 * fct); void(0)',100); void(0)


I have pasted the above in my address bar while opening google.
Output:-





Friday, February 13, 2009

Menu Slider using Javascript.

We can find many web sites which do menu’s hide and provide an option for viewers to click on a button to open menu list.
That too the menu list moves from Top to bottom or Left to right. Again when user clicks on hide button the menu list moves in opposite direction.
I would like to share my java script code that does this stuff.
Ex:-
I am creating a sample html page whose content is to display menu, 2 buttons (Slide in and Slide out).




Output:
Clicking on slide out button






Clicking on slide in button




Slide is closed


Oracle function that returns columns of a table in table format.

In this case it is not just to return the columns in a table but they should be returned in a table format.
So we should crate a user defined type.
To know about this in brief check at
Oracle Type in Prog. findings
I am creating a type called “tbl_columns”.
create type tbl_Columns as table of varchar2(255)



My oracle function that returns the columns in given table as table format is

--Function expecting table name as input parameter
CREATE OR REPLACE FUNCTION fun_columns(argTableName IN VARCHAR)
--setting return type, such that returning the pipe lined rows
RETURN tbl_columns pipelined
IS
--Initialising cursor to get column names of a given table
CURSOR cur_columns IS SELECT column_name FROM user_tab_cols WHERE table_name=UPPER(argTableName);
tmpRec user_tab_cols.column_name%TYPE;
BEGIN
--opening cursor to read values from the select query
OPEN cur_columns;
LOOP
FETCH cur_columns INTO tmpRec;
EXIT WHEN cur_columns%NOTFOUND;
--piping each value as row in the defined type table tbl_columns
pipe ROW(tmpRec);
END LOOP;
END;




I would like to create a table called “MyTable” to test with this oracle function as
CREATE TABLE MyTable
(
MyColumn1 NUMBER,
MyColumn2 FLOAT,
MyColumn3 DATE,
MyColumn4 VARCHAR2(100),
MyColumn5 CHAR
)



After compiling the oracle function, I am using the below select query to get the coulns available in this table (MyTable).
SELECT * FROM (TABLE(fun_columns('MyTABle')));
Output: