adsense

Saturday, January 30, 2010

Order By union

Unions are used to display joined results from two queries with the same list of fields.
Ex:
Select f1,f2 --f1,f2 are the fields
from tbl1 --table
where f1<1
union
Select f1,f2
from tbl1
where f1>1

If you want to order the displayed results you'd have definitely tried following way

Select f1,f2
from tbl1
where f1<1
order by f1
union
Select f1,f2
from tbl1
where f1>1
order by f1

Sadly this is not going to work. If you want to order the displayed results the above query should be modified as follows

SELECT * FROM
(
SELECT f1,f2
from tbl1
where f1<1
order by f1
union
SELECT f1,f2
from tbl1
where f1>1
order by f1
) A
ORDER BY f1,f2

Happy coding..............!

Wednesday, January 13, 2010

Numeric Text box

I have seen many people asking about using a textbox that accepts only numbers. Here I am providing some thoughts on that. I have seen many solutions diffent people have provided. But for me none of them was satisfactory. eventhough most of the textboxes allow only numbers, text valu can be pasted from right clicking mouse buttton and past option.
The solution for that is disabling paste on mouse right cliking.
'this class 'disables paste on text box

Imports System.Windows.Forms
Public Class TextBoxOnPaste
Inherits NativeWindow

Private tb As TextBox

Private Sub New()

End Sub

Public Sub New(ByVal tb As TextBox)
Me.tb = tb
Me.AssignHandle(tb.Handle)
End Sub

Private Const WM_PASTE As Integer = &H302

Protected Overrides Sub WndProc(ByRef m As Message)
'With every key press, click, key-combination press, etc a Message is sent to the
' window. You need to read about Win32 programming if you don't understand it.

Select Case m.Msg
Case WM_PASTE
'User has tried a way to paste something, like SHIFT+INSERT or
' right-click context menu...
If Clipboard.GetDataObject.GetDataPresent(DataFormats.Text) Then
'What user has tried to paste is a piece of text...
Dim str As String = Clipboard.GetDataObject.GetData(DataFormats.Text)
'Remove all appearances of coma
str = Replace(str, ",", "")
Dim NewVal As String
NewVal = Mid(tb.Text, 1, tb.SelectionStart) & str & Mid(tb.Text, tb.SelectionStart + tb.SelectionLength + 1, Len(tb.Text))
'NewVal will contain the future value of the textbox, if we would let
' what user wanted to paste to actually paste there...
If IsNumeric(NewVal) Then
' The result will be a numeric value. So go ahead and paste it!
tb.SelectedText = str
End If
' We're done, so we exit the sub (not letting the default WndProc() to
' paste the original string user tried to paste.
Exit Sub
End If
End Select
'In situations other than what we handled, the default WndProc() needs to be run MyBase.WndProc(m)
End Sub
End Class

wheew..!! Thatt's done next is overriding the onkey press event of the text box
Public Class NumericTextBox
Inherits TextBox
Public Sub New()
AddHandler MyBase.KeyPress, New KeyPressEventHandler(AddressOf Me.numericTextboxKeyPress)
Dim onPaste1 As New TextBoxOnPaste(Me)
End Sub
Private Sub numericTextboxKeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) 'Handles Me.KeyPress
Dim tb As TextBox = CType(sender, TextBox)
Dim chr As Char = e.KeyChar
If IsNumeric(e.KeyChar) And Not e.KeyChar = "-" Then
'If adding the character to the end of the current TextBox value results in
' a numeric value, go on. Otherwise, set e.Handled to True, and don't let
' the character to be added.
e.Handled = Not IsNumeric(tb.Text & e.KeyChar)
ElseIf e.KeyChar = "." Then
'For the decimal character (.) we need a different rule:
'If adding a decimal to the end of the current value of the TextBox results
' in a numeric value, it can be added. If not, this means we already have a
' decimal in the TextBox value, so we only allow the new decimal to sit in
' when it is overwriting the previous decimal.
If Not (tb.SelectedText = "." Or IsNumeric(tb.Text & e.KeyChar)) Then
e.Handled = True
End If
ElseIf e.KeyChar = "-" Then
'A negative sign is prevented if the "-" key is pressed in any location
' other than the begining of the number, or if the number already has a
' negative sign
If tb.SelectionStart <> 0 Or Microsoft.VisualBasic.Left(tb.Text, 1) = "-" Then e.Handled = True
End If
ElseIf Not Char.IsControl(e.KeyChar) Then
'IsControl is checked, because without that, keys like BackSpace couldn't
' work correctly.
e.Handled = True
End If

End Sub

End Class

You can creeate this as a library and use it in any project. If needed thousand seperators then you can customize this further.

And thats it....!!! Hope it ll be useful for you in some way...

cheers
Happy coding