Convert numeric value to Indian currency format in word.

Convert numeric value to Indian currency format in word.

I know it is not that much crucial but neither easy to get rid of financial application’s requirements as clients demand more and more facilities even they are small in size.

Here is one that SQL Script that every finance domain expert looks for. That is the currency writer in WORDS instead of digits.

Its return word in Indian currency format

Example

select dbo.[NumToWords](1110011011)

o/p         ONE ARAB  ELEVEN CRORE ELEVEN THOUSAND ELEVEN RS

CODE

/****** Object:  UserDefinedFunction [dbo].[NumToWords]   

            Script Date: 12/13/2012 07:04:34

            Created by :- Nilesh Makavana

            ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[NumToWords]

(   

      @InNumericValue NUMERIC(38,2)

)

RETURNS VARCHAR(1000)

AS

BEGIN

--declare @InNumericValue NUMERIC(38,2) = 109

DECLARE @RetVal VARCHAR(60),@StrRight VARCHAR(5),@StrFinal VARCHAR(60),@StrLength INT

SET @RetVal = ''

SET @RetVal= @InNumericValue

SET @StrLength = LEN(@RetVal)

--create a virtual table

declare @sequence as table (seq INTEGER NOT NULL UNIQUE, word [varchar](25) NOT NULL)

INSERT INTO @sequence SELECT 0, '' INSERT INTO @sequence SELECT 1, 'One ' INSERT INTO @sequence SELECT 2, 'Two '

INSERT INTO @sequence SELECT 3, 'Three ' INSERT INTO @sequence SELECT 4, 'Four ' INSERT INTO @sequence SELECT 5, 'Five '

INSERT INTO @sequence SELECT 6, 'Six ' INSERT INTO @sequence SELECT 7, 'Seven ' INSERT INTO @sequence SELECT 8, 'Eight '

INSERT INTO @sequence SELECT 9, 'Nine ' INSERT INTO @sequence SELECT 10, 'Ten ' INSERT INTO @sequence SELECT 11, 'Eleven '

INSERT INTO @sequence SELECT 12, 'Twelve ' INSERT INTO @sequence SELECT 13, 'Thirteen ' INSERT INTO @sequence SELECT 14, 'Fourteen '

INSERT INTO @sequence SELECT 15, 'Fifteen ' INSERT INTO @sequence SELECT 16, 'Sixteen ' INSERT INTO @sequence SELECT 17, 'Seventeen '

INSERT INTO @sequence SELECT 18, 'Eighteen ' INSERT INTO @sequence SELECT 19, 'Nineteen ' INSERT INTO @sequence SELECT 20, 'Twenty '

INSERT INTO @sequence SELECT 30, 'Thirty ' INSERT INTO @sequence SELECT 40, 'Forty ' INSERT INTO @sequence SELECT 50, 'Fifty '

INSERT INTO @sequence SELECT 60, 'Sixty ' INSERT INTO @sequence SELECT 70, 'Seventy ' INSERT INTO @sequence SELECT 80, 'Eighty '

INSERT INTO @sequence SELECT 90, 'Ninty '

IF(@StrLength > 6)

BEGIN

      SET @StrFinal = RIGHT(@RetVal,6)        

      SET @RetVal = SUBSTRING(@RetVal,-5,@StrLength)

      SET @StrLength = LEN(@RetVal)

      IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)

      BEGIN

            SET @StrFinal = @RetVal + ',' + @StrFinal

      END

      WHILE LEN(@RetVal) > 2

      BEGIN

            SET @StrRight = RIGHT(@RetVal,2)              

            SET @StrFinal = @StrRight + ',' + @StrFinal

            SET @RetVal = SUBSTRING(@RetVal,-1,@StrLength)

            SET @StrLength = LEN(@RetVal)

            IF(LEN(@RetVal) > 2)

                  CONTINUE

            ELSE

                  SET @StrFinal = @RetVal + ',' + @StrFinal

            BREAK

      END

END

ELSE

BEGIN

      SET @StrFinal = @RetVal

END

SELECT @StrFinal = ISNULL(@StrFinal,00)

declare @TbStrFinal as table(id int,value varchar(6))

insert into @TbStrFinal

select * FROM dbo.Split(@StrFinal,',')

--select * from ##StrFinal order by 1 desc

declare @totRow int

declare @counter int = 1

declare @finalWord varchar(1000) = ''

declare @num numeric(10,2)

select @totRow = count(*) from @TbStrFinal

while @totrow > 0

begin

      select @num = value from @TbStrFinal where id = @totrow

      if @counter = 1

      BEGIN

            if convert(int,@num) >= 100

            begin

                  SELECT @finalWord = case when len(THundreds.word) > 0 then THundreds.word + 'Hundred ' else '' end

                  + case when (select len(word) from @sequence where seq = ((convert(int,@num) % 100))) > 0 then

                        'and ' else '' end

                  + case when TTens.seq > 19 then  + '' + TTens.word + TUnits.word else

                        (select + '' + word from @sequence where seq = ((convert(int,@num) % 100))) end

                  FROM @sequence AS TUnits

                  CROSS JOIN @sequence AS TTens

                  CROSS JOIN @sequence AS THundreds

                  WHERE TUnits.seq = (convert(int,@num) % 100) % 10

                  AND TTens.seq = (convert(int,@num) % 100) - (convert(int,@num) % 100) % 10

                  AND THundreds.seq = (convert(int,@num) / 100)

            end

            else

            begin

                  SELECT @finalWord = case when TTens.seq > 19 then  + '' + TTens.word + TUnits.word else

                        (select + '' + word from @sequence where seq = ((convert(int,@num) % 100))) end

                  FROM @sequence AS TUnits

                  CROSS JOIN @sequence AS TTens

                  WHERE TUnits.seq = (convert(int,@num) % 100) % 10

                  AND TTens.seq = (convert(int,@num) % 100) - (convert(int,@num) % 100) % 10                  

            end

      END

      if @counter = 2

      BEGIN      

            set @finalWord = (select case

            when @num = 0 then ''

            when @num < 19 and @num <> 0 then (select word + 'Thousand ' from @sequence where seq = @num)

            else

            (select TTens.word + TUnits.word + 'Thousand ' FROM @sequence AS TUnits CROSS JOIN @sequence AS TTens

            where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) +  @finalWord

      END

      if @counter = 3

      BEGIN      

            set @finalWord = (select case

            when @num = 0 then ''

            when @num < 19 and @num <> 0 then (select word + 'lac ' from @sequence where seq = @num)

            else

            (select TTens.word + TUnits.word + 'lac ' FROM @sequence AS TUnits CROSS JOIN @sequence AS TTens

            where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) +  @finalWord

      END

      if @counter = 4

      BEGIN      

            set @finalWord = (select case

            when @num = 0 then ''

            when @num < 19 and @num <> 0 then (select word + 'crore ' from @sequence where seq = @num)

            else

            (select TTens.word + TUnits.word + 'crore ' FROM @sequence AS TUnits CROSS JOIN @sequence AS TTens

            where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) +  @finalWord

      END

      if @counter = 5

      BEGIN      

            set @finalWord = (select case

            when @num = 0 then ''

            when @num < 19 and @num <> 0 then (select word + 'arab  ' from @sequence where seq = @num)

            else

            (select TTens.word + TUnits.word + 'arab ' FROM @sequence AS TUnits CROSS JOIN @sequence AS TTens

            where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) +  @finalWord

      END

      if @counter = 6

      BEGIN      

            set @finalWord = (select case

            when @num = 0 then ''

            when @num < 19 and @num <> 0 then (select word + 'kharab  ' from @sequence where seq = @num)

            else

            (select TTens.word + TUnits.word + 'kharab ' FROM @sequence AS TUnits CROSS JOIN @sequence AS TTens

            where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) +  @finalWord

      END

      if @counter = 7

      BEGIN      

            set @finalWord = (select case

            when @num = 0 then ''

            when @num < 19 and @num <> 0 then (select word + 'marab  ' from @sequence where seq = @num)

            else

            (select TTens.word + TUnits.word + 'marab ' FROM @sequence AS TUnits CROSS JOIN @sequence AS TTens

            where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) +  @finalWord

      END

      set @totrow = @totrow - 1

      set @counter = @counter + 1

end

RETURN UPPER(@finalWord + 'rs')

END

Here split function also used. for view that code go to this post.

http://nile-programming.blogspot.in/2012/12/split-in-mssql-with-specific-character.html

Split in mssql with specific character.

After long search on web i found some help and created this function. Its function is used for split a string in mssql.
Its return table of split word.

/****** Object:  UserDefinedFunction [dbo].[Split]   

            Script Date: 12/13/2012 07:04:16

            Created by :- Nilesh Makavana

            ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[Split]

(

   @List nvarchar(2000),

   @SplitOn nvarchar(5)

) 

RETURNS @RtnValue table

(

      Id int identity(1,1),

      Value nvarchar(100)

)

AS 

BEGIN

While (Charindex(@SplitOn,@List)>0)

Begin

      Insert Into @RtnValue (value)

      Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

      Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

End

Insert Into @RtnValue (Value)

Select Value = ltrim(rtrim(@List))

Return

End

GO

custom control textbox with validation c# and vb.net

Custom control textbox with validation in C#

first that why we create a custom text box control in .net application development. is any reason or not. so first reason is you can automatically validate all control. and it's easy to use. simple example for if you want to input only integer or decimal from text box you have to write event for it on text box. suppose you have to more than one text box than you have to write for all control. where you use. so that time if you create a custom control than it's help you in fast development.

here in code i give you how to input only integer or decimal from textbox in vb.net and c#.

here code in c# and vb.net.

1> go to add new item in visual studio's current project.

2> select custome control and give name CustomeTextBox. which example given in following image.

3> Goes to source code of and change inheritance from control to TextBox or System.Windows.Forms.TextBox

4> Add following code in class. for c#. For vb.net copy it from here and convert from web. it's also working for vb.net also.

private InputType _InputSelection;

        public InputType InputTextType

        {

            set { _InputSelection = value; }

            get { return _InputSelection; }

        }

        public enum InputType

        {

            [Description("Input General String")]

            Text,

            [Description("Input Only Integer Number")]

            Integer,

            [Description("Input Only Decimal Number")]

            Decimal

        }

        string FTextC;

        public  CustomeTextBox()

        {

            InitializeComponent();

            this.TextChanged += new EventHandler(ORTextBox_TextChanged);

            this.KeyDown += new KeyEventHandler(ORTextBox_KeyDown);

            FTextC = this.Text;

        }

        protected override void OnPaint(PaintEventArgs pe)

        {

            base.OnPaint(pe);

        }

        private void ORTextBox_TextChanged(object sender, EventArgs e)

        {

            if (_InputSelection == InputType.Text)

            {

            }

            else if (_InputSelection == InputType.Integer)

            {

                if (string.IsNullOrEmpty(((TextBox)sender).Text))

                {

                    FTextC = "";

                }

                else

                {

                    Int64 num = 0;

                    bool success = Int64.TryParse(((TextBox)sender).Text, out num);

                    if (success & num >= 0)

                    {

                        ((TextBox)sender).Text.Trim();

                        FTextC = ((TextBox)sender).Text;

                    }

                    else

                    {

                        ((TextBox)sender).Text = FTextC;

                        ((TextBox)sender).SelectionStart = ((TextBox)sender).Text.Length;

                    }

                }

            }

            else if (_InputSelection == InputType.Decimal)

            {

                if (string.IsNullOrEmpty(((TextBox)sender).Text))

                {

                    FTextC = "";

                }

                else

                {

                    double num = 0;

                    bool success = double.TryParse(((TextBox)sender).Text, out num);

                    if (success & num >= 0)

                    {

                        ((TextBox)sender).Text.Trim();

                        FTextC = ((TextBox)sender).Text;

                    }

                    else

                    {

                        ((TextBox)sender).Text = FTextC;

                        ((TextBox)sender).SelectionStart = ((TextBox)sender).Text.Length;

                    }

                }

            }

        }

        private void ORTextBox_KeyDown(object sender, KeyEventArgs e)

        {

            if (e.KeyCode == Keys.Enter)

            {

                SendKeys.Send("{TAB}");

                e.SuppressKeyPress = true;

            }

        }


here also code for move's next control. when you press enter than it's send TAB key so it's moves to next control.

and it's completed. debug your application.

you get your control in Toolbox panel. just drag on form and enjoy with it.

for change input type you have to just change the property which is InputType

have you any query about it than comment.
than you.