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)
VName
from mytable
)

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

as

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...

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)

this.Dispose();

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.

eg:

Class Parent

private Var A

2) public : It is visible through out the project and the variable can be accessed from other project.

eg:

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.

eg:

Class Parent

protected Var A

Class Child

Inherits Parent

base.A

4) internal : It is visible all over the project except from other project or applications

eg:

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.

eg:

Class App1.Parent

protected internal Var A

Class App2.Child

Inherits Appl1.Parent

base.A

I am sorry for being very short in defining.

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

CREATE PROCEDURE spGETDate

AS

SELECT GETDATE() AS TodaysDate

Alter a stored procedure

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

ALTER PROCEDURE spGETDate

AS

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

AS

SELECT GETDATE() AS TodaysDate,GETDATE() + @daystoaddorsubtract AS 'ModifiedDate'

Calling the stored procedure

Procedure name and the values passed as parameters

spGETDATE 29

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)

AS

IF NOT EXISTS (SELECT id FROM mytable WHERE VName = @Name)

BEGIN

DECLARE @error INT

INSERT INTO mytable(VName,VAddress)

VALUES(@Name,@Address)

SET @error = @@error

IF @error=0

SELECT scope_identity() AS 'ReturnValue'

ELSE

SELECT 0 AS 'ReturnValue'

END

ELSE

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.

Multithreading in VB.NET

Threading

(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

    objClassDo.startThread()
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
    ListBoxNumbers.Items.Add(p_intVal)
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)
       objTH.Start()
   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
           Thread.Sleep(10)
           'Raise and event to the main form to display in the Listbox
           RaiseEvent evtdisplayInLV(intVal)
       End While
   End Sub

End Class

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
            Me.Controls.Add(objButton)
        Next
    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

MessageBox.Show(strButtonText)

End Sub