Closing Cursor by Checking if exists in SQL Server

Closing Cursor by Checking if exists in SQL Server

IF (SELECT CURSOR_STATUS('global','Cur_Name')) >=0
    BEGIN
        DEALLOCATE    Cur_UDD
    END

Ex :

BEGIN TRANSACTION
   
BEGIN TRY

DECLARE Cur_Name CURSOR FOR SELECT COL1,COL2 FROM TABLE

OPEN Cur_Name

FETCH NEXT FROM Cur_Name INTO @Col1,@Col2
WHILE @@FETCH_STATUS=0
BEGIN


    FETCH NEXT FROM Cur_Name INTO @Col1,@Col2

END

CLOSE        Cur_Name
DEALLOCATE    Cur_Name


COMMIT TRANSACTION
   
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
       
    IF (SELECT CURSOR_STATUS('global','Cur_UDD')) >=0
    BEGIN
        DEALLOCATE    Cur_UDD
    END
   
    DECLARE @Err_Msg NVARCHAR(MAX)
    DECLARE @Err_Sev INT
    SELECT  @Err_Msg=ERROR_MESSAGE(),@Err_Sev=ERROR_SEVERITY()   
    RAISERROR(@Err_Msg,16,1)
END CATCH

Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

CREATE PROCEDURE  Change_UDD_SP
(
    @Find_UDD_Name        NVARCHAR(MAX),
    @Replace_UDD_Type    NVARCHAR(MAX)
)
AS
BEGIN
   
    SET NOCOUNT ON

    DECLARE @Replace_UDD_Name    NVARCHAR(MAX)
    DECLARE @Udd_Count            INT
    DECLARE @CreateUDDQuery        NVARCHAR(MAX)

    SELECT    @Udd_Count            = 1
    SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)

   
    SELECT @Find_UDD_Name=LTRIM(RTRIM(@Find_UDD_Name))
    SELECT @Replace_UDD_Type=LTRIM(RTRIM(@Replace_UDD_Type))
   
    -- TO VALIDATE DATA
    IF ISNULL(@Find_UDD_Name,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Find_UDD_Name',16,1)
        RETURN
    END
   
    IF ISNULL(@Replace_UDD_Type,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Replace_UDD_Type',16,1)
        RETURN
    END

    -- TO CHECK FIND UDD IS EXISTS
    IF NOT EXISTS(SELECT 'X' FROM sys.types where name=@Find_UDD_Name)
    BEGIN
        RAISERROR('NO SUCH UDD : %s',16,1,@Find_UDD_Name)
        RETURN
    END
   
   
    --IF NOT EXISTS(SELECT    'X'    FROM    SYS.OBJECTS O,
    --                                    SYS.ALL_COLUMNS C,
    --                                    SYS.TYPES T
    --                            WHERE    o.object_id        =    c.object_id 
    --                            AND        c.user_type_id    =    t.user_type_id 
    --                            AND        t.name            =    @Find_UDD_Name)
    --BEGIN
    --    RAISERROR('NO REFERENCES TO THIS UDD : %s',16,1,@Find_UDD_Name)
    --    RETURN
    --END
   

    -- FIND RENAMING UDDs
    WHILE EXISTS(SELECT 'X' FROM SYS.TYPES WHERE name=@Replace_UDD_Name)
    BEGIN
        SELECT    @Udd_Count            = @Udd_Count +1
        SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)   
    END
   
   
    BEGIN TRANSACTION
   
    BEGIN TRY

        -- RENAMING UDDs
        EXEC SP_RENAME @Find_UDD_Name,@Replace_UDD_Name
       
        -- CREATING NEW UDD
        SELECT @CreateUddQuery    = N'CREATE TYPE ' + @Find_UDD_Name + ' FROM ' + @Replace_UDD_Type
        EXEC SP_EXECUTESQL @CreateUDDQuery
        --PRINT @CreateUDDQuery


        DECLARE @Table_Name        NVARCHAR(MAX)
        DECLARE @Table_Type        NVARCHAR(MAX)
        DECLARE @Column_Name    NVARCHAR(MAX)
        DECLARE @Is_Nullable    INT
        DECLARE @Is_Identity    INT
        DECLARE @Type_Name        NVARCHAR(MAX)
        DECLARE @Alter_Qry        NVARCHAR(MAX)
        DECLARE @Condition_Qry    NVARCHAR(MAX)
        DECLARE @Slno            INT
        DECLARE @Old_Table_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Cols    NVARCHAR(MAX)
        DECLARE @Constrn_Qry    NVARCHAR(MAX)
        SELECT    @Old_Table_Name    =    ''
        SELECT    @Constrn_Name    =    ''
        SELECT    @Constrn_Cols    =    ''

        DECLARE Cur_UDD CURSOR FOR    SELECT    o.name,o.type ,c.name,c.is_nullable,c.is_identity,t.name,CASE WHEN o.type='U' THEN 1 ELSE 2 END AS Slno 
                                    FROM    SYS.OBJECTS O,
                                            SYS.ALL_COLUMNS C,
                                            SYS.TYPES T
                                    WHERE    o.object_id        =    c.object_id 
                                    AND        c.user_type_id    =    t.user_type_id 
                                    AND        t.name            =    @Replace_UDD_Name
                                    ORDER BY Slno,o.type,o.name 
                                   

        OPEN Cur_UDD

        FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
        WHILE @@FETCH_STATUS=0
        BEGIN
           
            IF @Table_Name<>@Old_Table_Name AND UPPER(@Table_Type)='U'
            BEGIN
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
                IF EXISTS(SELECT 'X'    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                                        WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                                        AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                                        AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                                        AND        k.TABLE_NAME            =    c.TABLE_NAME
                                        AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                                        AND        k.TABLE_NAME            =    @Table_Name
                                        AND        k.COLUMN_NAME            =    @Column_Name)
                BEGIN
                    SELECT    @Constrn_Name            =    k.CONSTRAINT_NAME
                    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,
                            INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                    WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                    AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                    AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                    AND        k.TABLE_NAME            =    c.TABLE_NAME
                    AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                    AND        k.TABLE_NAME            =    @Table_Name
                    AND        k.COLUMN_NAME            =    @Column_Name
                   
                    SELECT    @Constrn_Cols    =    STUFF(CONVERT(NVARCHAR(MAX),(    SELECT    ','+COLUMN_NAME  
                                                                                FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                                                WHERE    CONSTRAINT_NAME    =    @Constrn_Name
                                                                                FOR        XML PATH(''))),1,1,'')
                    SELECT    @Constrn_Qry=N'ALTER TABLE ' +      @Table_Name + ' DROP CONSTRAINT ' + @Constrn_Name                                           
                    --PRINT @Constrn_Qry
                    EXEC    SP_EXECUTESQL @Constrn_Qry
                    SELECT    @Old_Table_Name    =    @Table_Name
                END
            END
           
            SELECT @Alter_Qry        =''
            SELECT @Condition_Qry    =''
           
            IF UPPER(@Table_Type)='U'
            BEGIN
                IF @Is_Nullable=0
                BEGIN
                    SELECT @Condition_Qry = ' NOT NULL '
                END
                SELECT @Alter_Qry=N'ALTER TABLE ' +  @Table_Name + ' ALTER COLUMN ' +  @Column_Name + ' ' + @Find_UDD_Name + ' ' + @Condition_Qry
               
                --PRINT @Alter_Qry
                EXEC SP_EXECUTESQL @Alter_Qry
               
            END
            ELSE
            BEGIN
                -- TO REFRESH TABLE FUNCTION AND VIEWS
                EXEC sp_refreshsqlmodule @Table_Name
               
            END
           
           
            FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
            -- CHECKING NEXT RECORD
            IF (@Table_Name<>@Old_Table_Name OR @@FETCH_STATUS<>0)  AND @Constrn_Name<>''
            BEGIN
                SELECT    @Constrn_Qry=N'ALTER TABLE ' + @Old_Table_Name + ' ADD CONSTRAINT ' + @Constrn_Name + ' PRIMARY KEY('+@Constrn_Cols+')'
                --PRINT @Constrn_Qry
                EXEC SP_EXECUTESQL @Constrn_Qry
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
            END
        END

        CLOSE        Cur_UDD
        DEALLOCATE    Cur_UDD
       
        -- CHECKING OTHER REF FOR THIS UDD
        DECLARE @Ref_Name    NVARCHAR(MAX)
        DECLARE Cur_Ref CURSOR FOR SELECT DISTINCT SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS WHERE USER_DEFINED_TYPE_NAME    =    @Replace_UDD_Name
        OPEN    Cur_Ref
        FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        WHILE @@FETCH_STATUS=0
        BEGIN
            EXEC sp_refreshsqlmodule @Ref_Name
            FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        END
        CLOSE        Cur_Ref
        DEALLOCATE    Cur_Ref
       
       
        -- DROPPING DUMMY UDD
        SELECT @CreateUddQuery    = N'DROP TYPE ' + @Replace_UDD_Name
        EXEC SP_EXECUTESQL @CreateUDDQuery
        COMMIT TRANSACTION
   
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
       
        IF (SELECT CURSOR_STATUS('global','Cur_UDD')) >=0
        BEGIN
            DEALLOCATE    Cur_UDD
        END
       
        IF(SELECT CURSOR_STATUS('global','Cur_Ref'))>=0
        BEGIN
            DEALLOCATE Cur_Ref
        END
       
        DECLARE @Err_Msg NVARCHAR(MAX)
        DECLARE @Err_Sev INT
        SELECT  @Err_Msg=ERROR_MESSAGE(),@Err_Sev=ERROR_SEVERITY()   
        RAISERROR(@Err_Msg,16,1)
    END CATCH
   
    SET NOCOUNT OFF

END

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2.

To Get UDD Differences :

-- NEW UDDs
select t.name,t.max_length,t.precision,t.scale,t.is_nullable,t1.name from Database1.sys.types t,Database1.sys.types t1 where t.is_user_defined=1 and t.name not in (select ti.name from Database2.sys.types ti where ti.is_user_defined=1)
AND t.system_type_id=t1.user_type_id

-- CHANGED UDDs
select t.name,tsys.name,t.max_length,t.precision,t.scale,t.is_nullable,t.default_object_id
,ttsys.name,tt.max_length,tt.precision,tt.scale,tt.is_nullable,tt.default_object_id
from Database1.sys.types t,Database2.sys.types tt,Database1.sys.types tsys,Database2.sys.types ttsys
where t.is_user_defined=1 and t.name=tt.name and t.system_type_id= tsys.user_type_id and tt.system_type_id=ttsys.user_type_id   
and t.name+'_'+CONVERT(NVARCHAR,t.system_type_id)+'_'+CONVERT(NVARCHAR,t.max_length)+'_'+CONVERT(NVARCHAR,t.precision)+'_'+CONVERT(NVARCHAR,t.scale)+'_'+CONVERT(NVARCHAR,t.is_nullable)+'_'+CONVERT(NVARCHAR,t.default_object_id)
not in (select ti.name+'_'+CONVERT(NVARCHAR,ti.system_type_id)+'_'+CONVERT(NVARCHAR,ti.max_length)+'_'+CONVERT(NVARCHAR,ti.precision)+'_'+CONVERT(NVARCHAR,ti.scale)+'_'+CONVERT(NVARCHAR,ti.is_nullable)+'_'+CONVERT(NVARCHAR,ti.default_object_id) from Database2.sys.types ti where ti.is_user_defined=1)
and t.name  not in (select n.name from Database1.sys.types n where n.is_user_defined=1 and n.name not in (select ni.name from Database2.sys.types ni where ni.is_user_defined=1) )

To Get Table Differences :

-- NEW TABLES
SELECT o.name FROM Database1.sys.objects o
WHERE o.type='U' and o.name not in (SELECT i.name FROM Database2.sys.objects i WHERE i.type='U')  order by o.name 

-- CHANGED COLUMNS
SELECT o.object_id,o.name,c.column_id,c.name,o.type,c.is_nullable,c.is_identity,t.name  
FROM Database1.sys.objects o,Database1.sys.columns c,Database1.sys.types t WHERE o.type ='U' and o.object_id=c.object_id and c.user_type_id=t.user_type_id 
AND o.name+'_'+  c.name NOT IN(SELECT uo.name+'_'+  uc.name FROM Database2.sys.objects uo,Database2.sys.columns uc WHERE uo.type ='U' and uo.object_id=uc.object_id) 
AND o.name not in (SELECT distinct n.name FROM Database1.sys.objects n
WHERE n.type='U' and n.name not in (SELECT ni.name FROM Database2.sys.objects ni WHERE ni.type='U'))
ORDER BY o.name,c.column_id  

To Get Constraint Differences :

-- DEFAULT CONSTRAINTS
select ob.name,o.name,c.name from Database1.sys.objects o,Database1.sys.columns c,Database1.sys.objects ob where o.type='D' and o.object_id=c.default_object_id and o.parent_object_id=ob.object_id  
and  ob.name+'_'+o.name+'_'+c.name not in (select obi.name+'_'+oi.name+'_'+ci.name from Database2.sys.objects oi,Database2.sys.columns ci,Database2.sys.objects obi where oi.type='D' and oi.object_id=ci.default_object_id and oi.parent_object_id=obi.object_id)  


-- PRIMARY KEY
select * from Database1.sys.objects where type='PK' and name not in(select name from Database2.sys.objects where type='PK')

Backup all databases in a Server except system DBs in Sql Server

Backup all databases in a Server except system DBs in Sql Server

Please compile this SP on Sql Server and whenever you want to take the backup of all existing databases, plz Execute this SP once.  It will automatically take backup and it will place in specified location.  

CREATE PROCEDURE [dbo].[DataBase_BackUp_SP](@OutputPath NVARCHAR(4000)='\\Server1\Shared Docs\Others\')   
AS   
BEGIN   
 SET NOCOUNT ON   
    
 DECLARE @BackFileName NVARCHAR(4000)    
 DECLARE @BackPath  NVARCHAR(4000)    
 DECLARE @BackFullPath NVARCHAR(4000)    
 DECLARE @DBName   NVARCHAR(4000)    
 DECLARE @ServerName  NVARCHAR(4000)    
   
   
 -- GETTING SERVER NAME   
 SELECT @ServerName = Name    
 FROM SYS.SERVERS    
 WHERE Server_id = 0    
   
    
 DECLARE DB_Cur CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE Database_Id>4   
 OPEN DB_Cur   
 FETCH NEXT FROM DB_Cur INTO @DBName   
 WHILE @@FETCH_STATUS=0   
 BEGIN   
     
  SELECT @BackPath=@OutputPath   
  SELECT @BackFileName='Backup_'+ @DBName +'_'+  @ServerName +'_' + CONVERT(NVARCHAR(10),GetDate(),105) + '.bak'   
  SELECT @BackFullPath =@BackPath +  @BackFileName   
     
  BACKUP DATABASE @DBName   
  TO DISK=@BackFullPath   
  WITH INIT   
     
  FETCH NEXT FROM DB_Cur INTO @DBName   
 END   
    
 CLOSE  DB_Cur   
 DEALLOCATE DB_Cur   
    
 SET NOCOUNT OFF   
END

Finding Missing Numbers in SQL Server

Finding Missing/Skipped Numbers in table  in SQL Server  

Declare @tbl table(slno int)

insert into @tbl(slno) values(1)
insert into @tbl(slno) values(3)
insert into @tbl(slno) values(4)
insert into @tbl(slno) values(5)
insert into @tbl(slno) values(11)
insert into @tbl(slno) values(13)
insert into @tbl(slno) values(18)
insert into @tbl(slno) values(8008)

select * from @tbl

select c.slno from
(select a.slno,(select b.slno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) b where b.ActSlNo=a.ActSlNo+1) as nextSlno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) a) c
where ISNULL(c.nextSlno,0)-ISNULL(c.slno,0)>1

Avoid Flickering of Datagridview in .NET

Avoid Flickering of Datagridview in .NET

Private Sub SetDoubleBuffering(ByVal pDataGridview As DataGridView)

        GetType(Control).InvokeMember("DoubleBuffered", Reflection.BindingFlags.SetProperty Or 

        Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic, Nothing, 

        pDataGridview, New Object() {True})

End Sub

Writing Control Validation in common place without writing on every control's event

Writing Control Validation in common place without writing on every control's event

here i'm going to allowing only numeric validation, i'm going to write this code in ControlValidation Class. its my own class.

Public Sub Allow_Control_Numeric(ByRef Cntrl As Control, ByVal pMsgTitle As String, Optional ByVal pNoOfDecimal As Integer = 0, Optional ByVal AllowNegative As Boolean = False, Optional ByVal AllowBlank As Boolean = True, Optional ByVal isCorrectFormatRequired As Boolean = True, Optional ByVal AllowZero As Boolean = True, Optional ByVal pIsQtyColumn As Boolean = False, Optional ByVal pTxtValidationCntrl As Control = Nothing, Optional ByVal pIsReadOnlyCntrl As Boolean = False, Optional ByVal pTxtValidationCntrlFlag As ValidationColumnFlag = ValidationColumnFlag.PartNo, Optional ByVal pIsPriceColumn As Boolean = False)
        Dim _controlValidation = New ControlValidation
        _controlValidation._Txt_Cntrl = Cntrl
        _controlValidation._MsgTitle = pMsgTitle
        _controlValidation._NoOfDecimal = pNoOfDecimal
        _controlValidation._AllowNegative = AllowNegative
        _controlValidation._AllowBlank = AllowBlank
        _controlValidation._isCorrectFormatRequired = isCorrectFormatRequired
        _controlValidation._AllowZero = AllowZero
        _controlValidation._IsQtyColumn = pIsQtyColumn
        _controlValidation._Txt_Validation_Cntrl = pTxtValidationCntrl
        _controlValidation._validationFlag = pTxtValidationCntrlFlag
        _controlValidation._IsPriceColumn = pIsPriceColumn

        If _gQtyValidationClasses.Contains(_stackTrace.GetFrames(1).GetMethod.ReflectedType.Name) Or _gQtyValidationClasses.Contains("*") Then
            _controlValidation._QtyAllowClass = True
        Else
            _controlValidation._QtyAllowClass = False
        End If

        '' To Set max Length for all Qty Controls
        If pIsQtyColumn Then
            If TypeOf Cntrl Is TextBox Then
                If _controlValidation._QtyAllowClass = True Then
                    CType(Cntrl, TextBox).MaxLength = _gQtyTotalSize
                Else
                    CType(Cntrl, TextBox).MaxLength = _gQtyTotalSizeOthers
                End If
            End If
        ElseIf pIsPriceColumn Then
            If TypeOf Cntrl Is TextBox Then
                CType(Cntrl, TextBox).MaxLength = _gPriceNumberSize + _controlValidation._NoOfDecimal + 1
            End If
        End If

        RemoveHandler Cntrl.KeyUp, AddressOf _controlValidation.Control_KeyUp
        RemoveHandler Cntrl.Validated, AddressOf _controlValidation.Control_Validated
        AddHandler Cntrl.KeyUp, AddressOf _controlValidation.Control_KeyUp
        AddHandler Cntrl.Validated, AddressOf _controlValidation.Control_Validated
    End Sub

   

    Private Sub Control_KeyUp()
        If _IsQtyColumn Then
            If _QtyAllowClass Then
                _NoOfDecimal = Get_Part_UOM_Result(_Txt_Validation_Cntrl.Text, _validationFlag)
            Else
                _NoOfDecimal = _gQtyDecimalSizeOthers
            End If
        End If
        _Txt_Cntrl.Text = _validation.Check_Numeric(_Txt_Cntrl.Text, _MsgTitle, _NoOfDecimal, _AllowNegative, _AllowBlank, True, False, _AllowZero, _IsQtyColumn, _QtyAllowClass, _IsPriceColumn)
    End Sub

 Private Sub Control_Validated()
        If _IsQtyColumn Then
            If _QtyAllowClass Then
                _NoOfDecimal = Get_Part_UOM_Result(_Txt_Validation_Cntrl.Text, _validationFlag)
            Else
                _NoOfDecimal = _gQtyDecimalSizeOthers
            End If
        End If
        _Txt_Cntrl.Text = _validation.Check_Numeric(_Txt_Cntrl.Text, _MsgTitle, _NoOfDecimal, _AllowNegative, _AllowBlank, True, _isCorrectFormatRequired, _AllowZero, _IsQtyColumn, _QtyAllowClass, _IsPriceColumn)
    End Sub

In validation class,

Public Function Check_Numeric(ByVal pValue As String, ByVal pMsgTitle As String, Optional ByVal pNoOfDecimal As Integer = 0, Optional ByVal AllowNegative As Boolean = False, Optional ByVal AllowBlank As Boolean = True, Optional ByVal pErrMsgRequired As Boolean = True, Optional ByVal isCorrectFormatRequired As Boolean = True, Optional ByVal AllowZero As Boolean = True, Optional ByVal pIsQty As Boolean = False, Optional ByVal pQtyAllowClass As Boolean = False, Optional ByVal pIsPrice As Boolean = False) As String  ',  ByVal pMsgBoxTitle As String, Optional ByVal pFrac As Boolean = False) As Char
        ' Checking Fractions value it must be >= 0
        If Not (pNoOfDecimal >= 0 And pNoOfDecimal <= 99) Then
            If pErrMsgRequired Then
                MessageBox.Show(My.Resources.E709, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
            Return ""
        End If


        '' Checking Numerics
        If IsNumeric(pValue) Or (pValue = "-" And AllowNegative = True) Or (pValue = "." And pNoOfDecimal > 0) Then

            If pIsQty Then   ' Qty Validation
                Dim _QtyDecValue As String = ""
                Dim _QtyNoValue As String = ""
                'Dim _RetValue As String = ""

                If Len(pValue) > IIf(pQtyAllowClass = True, _gQtyTotalSize, _gQtyTotalSizeOthers) Then
                    MessageBox.Show(My.Resources.E1606 & " " & IIf(pQtyAllowClass = True, _gQtyTotalSize, _gQtyTotalSizeOthers), pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Return ""
                End If

                _QtyNoValue = Mid(pValue, 1, IIf(InStr(pValue, ".") > 0, InStr(pValue, ".") - 1, Len(pValue)))
                _QtyDecValue = IIf(InStr(pValue, ".") > 0, Mid(pValue, InStr(pValue, ".") + 1), "")

                If Len(_QtyNoValue) > IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) Or Len(_QtyDecValue) > pNoOfDecimal Then
                    If Not pQtyAllowClass Then
                        MessageBox.Show(My.Resources.E1607 & " (" & IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) & " , " & pNoOfDecimal & " ) ", pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        If pNoOfDecimal <= 0 Then
                            MessageBox.Show(My.Resources.E1607 & " (" & IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) & " , " & pNoOfDecimal & " ) " & My.Resources.E1608, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Else
                            MessageBox.Show(My.Resources.E1607 & " (" & IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) & " , " & pNoOfDecimal & " ) " & My.Resources.E1609, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
                    End If
                    '_RetValue = Mid(_QtyNoValue, 1, _gQtyNumberSize)
                    'If Len(_QtyDecValue) > pNoOfDecimal Then
                    '    If pNoOfDecimal > 0 Then
                    '        _RetValue = _RetValue & "." & Mid(_QtyDecValue, 1, pNoOfDecimal)
                    '    End If
                    'End If
                    Return ""
                End If
            End If

            If pIsPrice Then
                'Dim _PriceDecValue As String = ""
                Dim _PriceNoValue As String = ""

                _PriceNoValue = Mid(pValue, 1, IIf(InStr(pValue, ".") > 0, InStr(pValue, ".") - 1, Len(pValue)))
                '_PriceDecValue = IIf(InStr(pValue, ".") > 0, Mid(pValue, InStr(pValue, ".") + 1), "")
                If Len(_PriceNoValue) > _gPriceNumberSize Then
                    MessageBox.Show(My.Resources.E1610 & " (" & _gPriceNumberSize & " , " & pNoOfDecimal & " ) ", pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Return ""
                End If
            End If


            If AllowNegative = False Then  ' checking if allownegative is false it should be positive values
                If Val(pValue) < 0 Then 'Or Val(FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)) < 0 Then
                    If pErrMsgRequired Then
                        MessageBox.Show(My.Resources.E710, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                    Return "" ''Math.Abs(Val(pValue)) ''Commented by Ravi on 12-09-2011. just clear,no need to correct the format. 
                End If
                If IsNumeric(pValue) Then
                    If Val(FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)) < 0 Then
                        If pErrMsgRequired Then
                            MessageBox.Show(My.Resources.E710, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
                        Return ""
                    End If
                End If
            End If




            ' To get first . (dot) position
            Dim _DotExists As Boolean = False
            Dim _DotPosition = InStr(pValue, ".")

            If _DotPosition = 0 Then
                _DotPosition = Len(pValue)
            Else
                _DotExists = True
            End If

            ' getting Decimal values after .
            Dim _DecimalValue = Mid(pValue, _DotPosition + 1, Len(pValue))
            'Dim _IntValue As Integer

            'If _DotExists Then
            '    _IntValue = Mid(pValue, 1, _DotPosition - 1)
            'Else
            '    _IntValue = Mid(pValue, 1, _DotPosition)
            'End If

            If Not AllowZero Then
                If pValue <> "-" And pValue <> "." Then
                    If Val(pValue) = 0 Then
                        MessageBox.Show(My.Resources.E1611, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Return ""
                    End If
                End If
            End If

            If (Len(_DecimalValue) > pNoOfDecimal) Or (pNoOfDecimal = 0 And _DotExists = True) Then
                ' careful focus when changing this conditions
                If pErrMsgRequired Then
                    If pNoOfDecimal = 0 Then
                        MessageBox.Show(My.Resources.E711, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        MessageBox.Show(My.Resources.E712 & pNoOfDecimal, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                End If
                If pValue = "-" Or pValue = "." Then
                    Return pValue
                Else
                    If isCorrectFormatRequired Then
                        Return "" 'FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)  '' No need to round off
                    Else
                        Return ""
                    End If
                End If

            End If


            '' when no error , it comes here..
            '' Convert valid numeric value to correct format
            If isCorrectFormatRequired Then
                If pValue = "." Or pValue = "-" Then
                    Return ""
                Else
                    Return FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)
                End If
            Else
                Return pValue
            End If
        Else
            If Trim(pValue) = "" And AllowBlank = True Then
                '' to skip when numeric values can allow blank values
            Else
                If pErrMsgRequired Then
                    MessageBox.Show(My.Resources.E713, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
                Return ""
            End If
        End If
        Return pValue

    End Function

Callling this function :

_controlValidation.Allow_Control_Numeric(_Value, MsgTitle, _2, False, True, False)