Thursday, July 22, 2010

Get Top Last Record

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 Top
(Select (Count(*)-@NoOfRecord) from mytable)
from mytable

Wednesday, May 12, 2010

Swap Values in SQL

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.

Step 3.
create a procedure names uspSwapData

Create procedure uspSwapData
declare @mydata table
id int)

insert into @mydata
Select ID from data where val=2

update data set val=2 where
ID in (select id where val=1)
-- or you can use this query instead of the above one
-- update data set val=2 where val=1

update 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

On Esc close the window

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)

Wednesday, March 31, 2010

Access Modifiers

Access Modifiers

It is very important to understand when and where to use access modifiers.
1) private
2) public
3) protected
4) internal
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.
Class Parent
private Var A

2) public : It is visible through out the project and the variable can be accessed from other project.
Class Parent
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.
Class Parent
protected Var A

Class Child
Inherits Parent

4) internal : It is visible all over the project except from other project or applications
Class Parent
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.
Class App1.Parent
protected internal Var A

Class App2.Child
Inherits Appl1.Parent

I am sorry for being very short in defining.

Friday, March 26, 2010

Stored Procedure

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


Alter a stored procedure
It is used when you have to make some changes to the existing procedure.

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.

@daystoaddorsubtract INT
SELECT 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

@Name VARCHAR(1000),
@Address VARCHAR(1000)
IF NOT EXISTS (SELECT id FROM mytable WHERE VName = @Name)
INSERT INTO mytable(VName,VAddress)
SET @error = @@error
IF @error=0
SELECT scope_identity() AS 'ReturnValue'
SELECT 0 AS 'ReturnValue'
SELECT -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

Multithreading in VB.NET

(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"
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
End Sub

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 operations
Me.Invoke(New del_displayLV(AddressOf hndlr_displayLV), p_intVal)
End Sub
'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
End Sub
8) In class Define these things

Imports System.Threading
Imports System.Windows
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)
End Sub
'Procedure to be called by the thread
Private Sub doStuff()
Dim intMax As Integer = 500
Dim intVal As Integer = 0
While True
intVal += 1
If intVal > intMax Then

Exit While
End If
'Sleep the thread to view the progress
'Raise and event to the main form to display in the Listbox
RaiseEvent evtdisplayInLV(intVal)
End While
End Sub

End Class

Tuesday, February 9, 2010

Dynamic button creation

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.

Load buttons

Private Sub loadButtons()
Dim intNoOfTextBox As Integer = 10
For intC As Integer = 0 To intNoOfTextBox - 1
Dim objButton As Button
objButton = New Button
objButton.Size = New Size(200, 25)
objButton.Location = New Point(5, intC * 25)
objButton.Text = " My Runtime Dynamic Button " & (intC + 1).ToString
AddHandler objButton.Click, AddressOf ButtonClick
End 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
End Sub