Thursday, September 25, 2008

SQL Server 2005 : Generate Sequence number and delete rows with top

Generate Sequence Number in SQL 2005

Select ROW_NUMBER() OVER(ORDER BY Column_Name) As ID,* From Table_Name



Delete First Row in SQL 2005

WITH T1 AS (Select Id,FirstName,LastName, ROW_NUMBER()
OVER (PARTITION BY Id,FirstName,LastName Order By Id) AS NUMBER From TEMP)
Delete From T1 Where Number > 1



The Most easiest way to delete first row in SQL 2005 is,


Delete top (1) From Table_Name

Monday, September 22, 2008

Java script Confirm with Yes No buttons

You can write javascript with diffrent button options like YesNo, YesNoCancel etc.
You can change the code 4132 and see the O/P.
To Complete this task, just write following java script in your code,
< script language=javascript >

function window.confirm(str)
{
  execScript('n = msgbox("'+str+'","4132","Title")', "vbscript");
  return(n == 6);
}

</script>

In this script, the line will execute vbScript's Message box withYesNo Option.
execScript('n = MsgBox("Yes Or No?",vbYesNo,"Title")', "vbscript");

Thursday, September 11, 2008

SQL Stuff

--- Database transactions
begin tran
rollback tran
Save Transaction tran_name

--Deletes last row inserted in table Employees

declare @topN int
set @topN = 1
set rowcount @topN
Delete From Employees


--Makes ROWCOUNT Off

--Once the rowcount is set , it is set until you explicitly make off
Set ROWCOUNT 0

Important :
When a transaction begins,
resources used during the transaction are held until the completion of the transaction
(namely locks).
When part of a transaction is rolled back to a savepoint, resources
continue to be held until the completion of the transaction
(or a rollback of the complete transaction).


@@TRANCOUNT
Returns the number of active transactions for the current connection.

@@ROWCOUNT
Returns the number of rows affected by the last statement.

@@IDENTITY
Returns the last-inserted identity value.

@@FETCH_STATUS
Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

Return value Description
0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing.

@@ERROR
Returns the error number for the last Transact-SQL statement executed.

@@DBTS
Returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database.

Creates a user-defined data type.
sp_addtype [ @typename = ] type,
  [ @phystype = ] system_data_type
  [ , [ @nulltype = ] 'null_type' ]
  [ , [ @owner = ] 'owner_name' ]

Sunday, September 7, 2008

Perform shallow copy of an object : example

To Perform shallow copy of an object MemberwiseClone() function is used.
In this example, you can also observe the behavior of static and non-static members.
To understand this function create Console Application.
In Console Application create class MyBaseClass as follows.
  class MyBaseClass
  {
  public static string CompanyName = "My Company";
  public int age;
  public string name;
   
//overides to string method
  public override string ToString()
  {
  return string.Format("CompanyName = {0}, age = {1}, name = {2}", CompanyName, age, name);
  }
  }
Now, Rename Program.cs to MyDerivedClass.cs,  paste following code,  and check output.
  class MyDerivedClass : MyBaseClass
  {
  static void Main(string[] args)
  {
  // Creates an instance of MyDerivedClass and assign values to its fields.
  MyDerivedClass m1 = new MyDerivedClass();
  MyDerivedClass.CompanyName = "Softweb Solutions";
  m1.age = 42;
  m1.name = "Sam";

  //Performs a shallow copy of m1 and assign it to m2. (Value Type)
  MyDerivedClass m2 = (MyDerivedClass)m1.MemberwiseClone();

  //Performs a copy of m1 and assign it to m3. (Reference Type)
  MyDerivedClass m3 = m1;


  Console.WriteLine("\nBefore Change");

  Console.WriteLine("Object m1---------:" + m1.ToString());
  Console.ReadKey();

  Console.WriteLine("Object m2---------:" + m2.ToString());
  Console.ReadKey();

  Console.WriteLine("Object m3---------:" + m3.ToString());
  Console.ReadKey();

  //Now Change m1 and print m1, m2, and m3
  MyDerivedClass.CompanyName = "TRC";
  m1.age = 100;
  m1.name = "Charles";

  Console.WriteLine("\n\nAfter Change");

  Console.WriteLine("Object m1---------:" + m1.ToString());
  Console.ReadKey();

  Console.WriteLine("Object m2---------:" + m2.ToString());
  Console.ReadKey();

  Console.WriteLine("Object m3---------:" + m3.ToString());
  Console.ReadKey(); 
  }
  }

Saturday, September 6, 2008

New operator ?? in c#

The operator '??' is in c#, which is used check the value is null.
Previously, we used to check the null value by the following methods..

if (a!=null) { c=a; } or
c = ((a!=null)?a:0);


we can do the same checking with the new operator ?? like this..
c = a??0;

By this statement the value a will be assigned to c if it is not null else the zero will be assigned. 

Monday, September 1, 2008

Locate nested controls easily

Finding controls within a Page's control hierarchy can be painful but if you know how the controls are nested you can use the lesser known "$" shortcut to find controls without having to write recursive code. The following example shows how to use the DefaultFocus property to set the focus on a textbox that is nested inside of a FormView control. Notice that the "$" is used to delimit the nesting: 

<form id="form1" runat="server" DefaultFocus="formVw$txtName">
  <div>

  <asp:FormView ID="formVw" runat="server">
  <ItemTemplate>

  Name: 
  <asp:TextBox ID="txtName" runat="server" 

  Text='<%# Eval("FirstName") + " " + Eval("LastName") %>' />
  </ItemTemplate>

  </asp:FormView>

  </div>
</form> 

This little trick can also be used on the server-side when calling FindControl().
Here's an example: 

TextBox tb = this.FindControl("form1$formVw$txtName") as TextBox;

if (tb != null)

{
  //Access TextBox control

Set the default focus to a control when the page loads

 This is extremely simple thing that can be done without resorting to writing JavaScript. If you only have a single textbox (or two) on a page why should the user have to click in the textbox to start typing? Shouldn't the cursor already be blinking in the textbox so they can type away? Using the DefaultFocus property of the HtmlForm control you can easily do this. 

< form id="frm" DefaultFocus="txtUserName" runat="server" >
  ...
</form>

Friday, August 29, 2008

Table with Fixed Header

Sometimes we need to display an html table with fixed header,following is the code for a table with fixed header.

This task can be completed with keeping two things in mind.

 1. Div tag with style="height:300px; overflow:auto;”
  This makes division section fixed in page and allow scrollbar to apper.

 2. Making Table header position relative to parent (div) ,
  <tr style="position:relative; top:expression(offsetParent.scrollTop); ">
  
 In this example, I have added 2 elements in table, to see the effect you need to enter more elements that exceeds the div tag height.

 Moreover, we can use style="position:relative; top:expression(offsetParent.scrollTop); " to make header fixed for datagrid also.


 CODE:
<div style="height:300px; overflow:auto; ">
  <table cellspacing="5" width= "952px" style="border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid; border-collapse: separate; table-layout: auto; vertical-align: middle; overflow: auto; text-align: center; background-color: navajowhite;" cellpadding="5" frame="box">
  <thead style="background:Brown">
  <tr style="position:relative; top:expression(offsetParent.scrollTop);">
  <th>Station</th>
  <th>Date</th>
  <th>Status</th>
  <th style="width: 161px">Num.</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>KABC</td>
  <td>09/12/2002</td>
  <td>Submitted</td>
  <td>0</td>
  </tr>
  <tr>
  <td>KCBS</td>
  <td>Lockdown</td>
  <td></td>
  <td>2</td>
  </tr> 
  </tbody>
  </table>
</div >

Wednesday, August 27, 2008

Nullable type in .Net 2.0

Nullable type allows a varible to store null value, Infact this is the special about the type. The main purpose is it will add two members to the variable HasValue and Value Members.
for example, if you store data for a yes/no question and if the user did not answer the question then null will be stored in that place. This means it stores True, False and third state nothing as null also. This will be represented as shown below.

In VB.Net,
Dim b as Nullable(Of Boolean) = Nothing
.....
If b.HasValue Then
Do if b has some value and not null
Else
Do if b is null
End If
In C#,
Nullable b = null;

Otherwise it can be used this way also, only applicable for C#,
bool? b = null;
.....
if (b.HasValue) {Do if it is not null} else {Do if it null}