This query will help you get the top most rows from the bottom. It may sound simple but complex, when you are not allowed to use the Primary key or unique key.
There may be many other ways..But I thought of this one way.
Note: VName is th column name of table mytable
--this query will give data from the last record
select * from mytable
Declare @NoOfRecord int
Set @NoOfRecord=2 --1 gives the last record --2 will give second last record
Select Top 1 VName from mytable
where VName not in (
(Select (Count(*)-@NoOfRecord) from mytable)
Thursday, July 22, 2010
Wednesday, May 12, 2010
There is a way to swap bit values.I saw it in one of my colleagues mahendra blog.
Later I thought if I want to swap non bit values. May it be a string or an integer. Then how to do it. There may be numerous ways. But one way which I tried is as below.
Step 1 . Make a table named data and have two columns id(pk autonumber) and val(int)
For testing put come data
Step 2. Put some raw data eg, 1,1;2,2;3,1;4,2;5,1;6,2 etc.
create a procedure names uspSwapData
Create procedure uspSwapDataasdeclare @mydata table(id int)insert into @mydataSelect ID from data where val=2update data set val=2 whereID in (select id where val=1)-- or you can use this query instead of the above one-- update data set val=2 where val=1update data set val=1 where id in (select id from @mydata)
Now it is done. you will get the Val swapped with 1 and 2
Enjoy ... I hope this will help many SQL loving souls...
Friday, April 16, 2010
This concept always fascinated me. I always wondered how to do it in .NET. Then came the MSDN in rescue. So here I am sharing a tip to do it.
Step 1 : In the windows form there is a property KeyPreview make it to True . By default it is False.
Step 2 : In the KeyDown event of the form write this small code.
if (e.KeyCode == Keys.Escape)this.Dispose();
Wednesday, March 31, 2010
It is very important to understand when and where to use access modifiers.
5) protected internal
1) private : It is visible to only the same class .You cannot access the variable from any other class, even by creating an object or by inheriting it to a child class.
private Var A
2) public : It is visible through out the project and the variable can be accessed from other project.
public Var A
3) protected : It is visible inside the class. It cannot be accessed directly from other class. It has to be inherited to access it.
protected Var A
4) internal : It is visible all over the project except from other project or applications
internal Var A
5) protected internal : It is visible all over the project except from other procect or applications. It can be accessed in other project only by inheriting the base class.
protected internal Var A
I am sorry for being very short in defining.
Friday, March 26, 2010
It is a precompiled set of sql statements. It is normally used where you have to work of big or complex queries or have some checks before executing any queries. Stored Procedures are not case sensitive.
Create a stored procedure
CREATE PROCEDURE spGETDateASSELECT GETDATE() AS TodaysDate
Alter a stored procedure
It is used when you have to make some changes to the existing procedure.
ALTER PROCEDURE spGETDateASSELECT GETDATE() + 1 AS NextDate
Passing variables to a stored procedure
You can pass any number of variables,but the variables should be defined with the data types and the variable name should be preceded with an @ symbol.
CREATE PROCEDURE spGETDate@daystoaddorsubtract INTASSELECT GETDATE() AS TodaysDate,GETDATE() + @daystoaddorsubtract AS 'ModifiedDate'
Calling the stored procedure
Procedure name and the values passed as parameters
Stored Procedure with many checks and computation.
I am providing a basic example. many complex procedures can be written as per the requirement.
This procedure checks whether the passed data exist. If exist then it returns a -1 . If the data doesnt exist it inserts the data and returns the last inserted identity value. If the insert statement fails then it will return a 0 to indicate an error.
Thanks to Roopesh Thakkar for giving a hint about this interesting stored procedure.
Note: To run this procedure you should have a table named mytable with columns id,Vname,VAddress. id is a primary key with identity 1 and Vname and VAddress are varchar with size 1000
CREATE PROCEDURE spInsertMYTABLE@Name VARCHAR(1000),@Address VARCHAR(1000)ASIF NOT EXISTS (SELECT id FROM mytable WHERE VName = @Name)BEGINDECLARE @error INTINSERT INTO mytable(VName,VAddress)VALUES(@Name,@Address)SET @error = @@errorIF @error=0SELECT scope_identity() AS 'ReturnValue'ELSESELECT 0 AS 'ReturnValue'ENDELSESELECT -1 AS 'ReturnValue'
I really liked the concept of stored procedures. It will make your life more simpler when you deal with complex and big queries.
Wednesday, February 24, 2010
(This is one of the concept which I liked very much in .NET)
It gives the program to do many things simultaneously.Threading is used to divide many things at a same time but independently or dependently.
Here I will be telling in brief how threading is used in a small application using class and delegates.I will populate a listbox with numbers 0-500 bysing threads.
1) Create a windows application
2) Add a Form "FormMain"
3) Drop a button "ButtonStartThread"
3) Drop a button "ButtonStartThread"
4) Drop a Listbox "ListBoxNumbers"
5) Add a class "ClassDo"
6) In buttonClick write this
Private Sub ButtonStartThread_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonStartThread.Click
Dim objClassDo As ClassDO
objClassDo = New ClassDO
AddHandler objClassDo.evtdisplayInLV, AddressOf evt_hndlr_displayInLV 'Event handler to handle the event raised from the classdo
7) Event handlers and delegates
Private Sub evt_hndlr_displayInLV(ByVal p_intVal As Integer)
'Call a delegate to handle this event.We do this to avoid cross thread operations8) In class Define these things
Me.Invoke(New del_displayLV(AddressOf hndlr_displayLV), p_intVal)
'Declare a delegate
Private Delegate Sub del_displayLV(ByVal p_intVal As Integer)
Private Sub hndlr_displayLV(ByVal p_intVal As Integer)
'Populate the item in the listbox
Public Class ClassDO
'Declare and event to raise in the main form
Public Event evtdisplayInLV(ByVal p_intVal As Integer)
'this function is called form the main form
Public Sub startThread()
'Call a thread here
Dim objTH As Thread
objTH = New Thread(AddressOf doStuff)
'Procedure to be called by the thread
Private Sub doStuff()
Dim intMax As Integer = 500
Dim intVal As Integer = 0
intVal += 1
If intVal > intMax Then
'Sleep the thread to view the progress
'Raise and event to the main form to display in the Listbox
Tuesday, February 9, 2010
Dynamic button creation in VB.NET with click events
It is simple to create a button runtime. But it is one more step to define a respective event handler for the events for the control.
Private Sub loadButtons()Dim intNoOfTextBox As Integer = 10For intC As Integer = 0 To intNoOfTextBox - 1Dim objButton As ButtonobjButton = New ButtonobjButton.Size = New Size(200, 25)objButton.Location = New Point(5, intC * 25)objButton.Text = " My Runtime Dynamic Button " & (intC + 1).ToStringAddHandler objButton.Click, AddressOf ButtonClickMe.Controls.Add(objButton)NextEnd Sub
Event handler for button click
Private Sub ButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim objTempButton As Button = CType(sender, Button)
Dim strButtonText As String = ""
strButtonText = objTempButton.Text.Trim