Wednesday, January 28, 2009

[vb6] Shuting down/Unload form in Form_Load event

You are doing some validation code in the Form_Load event. If that validation
code fails, then you do not want to show the form. If you call "Unload ME", you will get an error in the form that called ".Show" of the form loading, saying that the form is already unloaded.
  Private mbCancelForm As Boolean

Private Sub Form_Activate()
If mbCancelForm = True Then Unload Me
End Sub

Private Sub Form_Load()
mbCancelForm = False

If {SomeCondition} = True Then
MsgBox "False condition, unable to load the form", vbExclamation + vbOKOnly
mbCancelForm = True
End If
End Sub

Thursday, January 22, 2009

[mssql] Server Side Paging

The script below can be used to fetch the record according to certain page number and size. The syntax is:
  SELECT {field_list} FROM
(SELECT TOP {page_size} {field_list} FROM
(SELECT TOP {page_size * page_number} {field_list}
FROM {table} (NOLOCK)
WHERE {where_criteria}
ORDER BY {field_to_order} {order_type}
) AS SortedData
ORDER BY {field_to_order} {reverse_order_type}
) AS PagedData
ORDER BY {field_to_order} {order_type}

Let's say we would like to fetch the data from table customer for page number 10 where the page size is 20, which descending order by username.
  SELECT username, fullname FROM
(SELECT TOP 20 username, fullname FROM
(SELECT TOP 200 username, fullname
FROM customer (NOLOCK)
WHERE fullname like '%ali%'
ORDER BY username DESC
) AS SortedData
ORDER BY username ASC
) AS PagedData
ORDER BY main_username DESC

Wednesday, January 21, 2009

[vb6] Convert formatted string into numeric

The function below is used to convert formatted string that produced by Format() function into numeric value (Double datatype). This function will remove all non-numeric string except minus and decimal delimiter.

Create new module and paste the source below inside your module file (.bas).
  '****************************************************************************
' Name : StrToNumeric
' Author : Chandra Gunawan
' Date : 22-Jan-2009
' Description : Convert formatted string into numeric
'****************************************************************************
Public Function StrToNumeric( _
ByVal pString As String, _
Optional ByVal pDecimalDelimiter As String) As Double

Dim I%, J%, strVal$

If pDecimalDelimiter <> "." And pDecimalDelimiter <> "," Then
pDecimalDelimiter = Trim(Format(0, "#.#"))
End If

'Remove all non-numeric except dot and comma
For I = 1 To Len(pString)
If InStr(1, "-0123456789" & pDecimalDelimiter, Mid(pString, I, 1)) > 0 Then
strVal = strVal & Mid(pString, I, 1)
End If
Next

'Remove unused delimiter
J = Len(strVal) - Len(Replace(strVal, pDecimalDelimiter, ""))
If J > 1 Then
strVal = Replace(strVal, pDecimalDelimiter, "", 1, J - 1)
End If

'Replace to proper decimal delimiter
If strVal = "" Or strVal = "." Then strVal = "0"
StrToNumeric = CDbl(Replace(strVal, pDecimalDelimiter, "."))
End Function 'StrToNumeric

[vb6] Numeric input mask for Textbox

Place the 2 Textbox objects in your form with name Textbox1 and Textbox2.
And then paste the code below into your .frm file.
  Private Sub Text1_KeyPress(KeyAscii As Integer)
Call KeyPressNumeric(KeyAscii)
End Sub

Private Sub Text2_KeyPress(KeyAscii As Integer)
Call KeyPressNumeric(KeyAscii, True, Text2.Text, True, Text2.SelStart)
End Sub

Private Sub Text1_GotFocus()
Text1.Text = Replace(Replace(Text1.Text, ",", ""), ".", "")
End Sub

Private Sub Text1_LostFocus()
Text1.Text = Format(Text1.Text, "###,###,##0")
End Sub

Private Sub Text2_GotFocus()
Text2.Text = Replace(Replace(Text2.Text, ",", ""), ".", "")
End Sub

Private Sub Text2_LostFocus()
Text2.Text = Format(Text2.Text, "###,###,##0.00")
End Sub

Create new module and place the code below inside your module file (.bas).
  '****************************************************************************
' Name : KeyPressNumeric
' Author : Chandra Gunawan
' Date : 22-Jan-2009
' Description : For use with Textbox_KeyPress events
' Allows only numeric entries
' Note : If you set pAllowDecimal = True
' then you must set pTextBox_Text = TextBox.Text
' If you set pAllowMinus = True
' then you must set pTextbox_SelStart = TextBox.SelStart
'*****************************************************************************
Public Sub KeyPressNumeric( _
pKeyAscii As Integer, _
Optional pAllowDecimal As Boolean = False, _
Optional pTextBox_Text As String, _
Optional pAllowMinus As Boolean = False, _
Optional pTextbox_SelStart As Long = 0)

Dim strDecDlm As String

'Note: 8=backspace, 13=enter, 27=escape
strDecDlm = Trim(Format(0, "#.#"))
If Chr(pKeyAscii) = strDecDlm Then
If Not pAllowDecimal Or InStr(1, pTextBox_Text, strDecDlm) > 0 Then pKeyAscii = 0
ElseIf Chr(pKeyAscii) = "-" Then
If Not pAllowMinus Or pTextbox_SelStart <> 0 Then pKeyAscii = 0
ElseIf InStr(1, "0123456789", Chr(pKeyAscii)) < 1 And _
Not (pKeyAscii = 8 Or pKeyAscii = 13 Or pKeyAscii = 27) Then
pKeyAscii = 0
End If
End Sub 'KeyPressNumeric

Tuesday, January 6, 2009

[mssql] How to count records with distinct option?

SELECT
(SELECT count(*) FROM
(SELECT DISTINCT City, Gender
FROM Database1..Person (NOLOCK)
WHERE Age > 18
UNION
SELECT DISTINCT City, Gender
FROM Database2..Person (NOLOCK)
WHERE Age > 18) x)