Learning Home

Wednesday, 26 April 2017

SQL SERVER


 

Select multiple rows without loop in SQL SERVER

declare @t varchar(max)
set @t = 'Product :'
select @t = @t + ',' + ItemInfo.ItemName from ItemInfo
print @t

SQL SERVER database suspect problem solution.



EXEC sp_resetstatus BeaconAcc;
ALTER DATABASE BeaconAcc SET EMERGENCY
DBCC checkdb(BeaconAcc)
ALTER DATABASE BeaconAcc SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (BeaconAcc, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE BeaconAcc SET MULTI_USER



Note: BeaconAcc is DBName.

Monday, 20 March 2017

Make Apostrophe in SQL Query

declare @str varchar(50)
select @str = 'ABC'''
declare @strLen int = len(@str)
declare @rtnText varchar(50)
select @rtnText = ''
if @strLen > 0
begin
declare @i int = 0
while @i <= @strLen
begin
select @rtnText = @rtnText + substring(@str,@i,1)
if substring(@str,@i,1) = ''''
select @rtnText = @rtnText + ''''
set @i = @i + 1
end
end
print @rtnText

Wednesday, 21 December 2016

Codes

A simple java script error Handling Example.


We can use try catch block in java script for error handling.this is a example for use of try catch
<head>
<title></title>
<script type="text/javascript">
    var result; //Global Variable function CalCulate()
{
    try { //try Block
        var a = document.getElementById("txt1").value;
        var b = document.getElementById("txt2").value;
        var c = a / b;
        result = c;
    }
    catch (e) { //error handling  if you try to divide by zero it will generate exception. 
        result = e;
    }
    finally { //this block will execute always either work try block or catch block
        mydiv.innerHTML = result;
}
}
</script>
</head>
<body>
<div>
<input type="text" id="txt1" />
<input type="text" id="txt2" />
<input type="button" name="btn1" onclick="CalCulate()" value="Click" />
<div id="mydiv"></div>
</div>
</body>
</html>
Download File

Check match between two text box value with JavaScript.


<html>
<head><title></title>
<script type="text/javascript">
    function match() {
        var a = document.getElementById("txt1").value;
        var b = document.getElementById("txt2").value;

        if (a == b) {
            result.innerHTML = "match";
        }
        else {
            result.innerHTML = "not match";
        }
    }
</script>
</head>
<body>
<input type = "text" id = "txt1" />
<input type = "text" id = "txt2" onKeyUp="match()" />
<div id="result" />
</body>
</html>

Software:

Software is a collection of Program. Program: is a collection of Instruction. Instruction is a meaningful data.

Set Comma in a number using vb,net


    Public Function PutComma(ByVal num As Double)
        Dim strNumber As String = ""
        Dim comma As String = ","
        Dim pref1 As String = ""
        Dim pref2 As String = ""
        Dim pref3 As String = ""
        Dim suff As String = ""
        Dim strNegative As String = ""
        If num < 0 Then
            num = num.ToString().Substring(1, num.ToString.Length - 1)
            strNegative = "-"
        End If
        If num >= 1000 Then
            If (num / 1000) < 10 Then '1000 to 9999
                pref1 = num.ToString().Substring(0, 1)
                suff = num.ToString().Substring(1, num.ToString.Length - 1)
                strNumber = strNegative & pref1 & comma & suff
            ElseIf (num / 10000) < 10 Then '10,000 to 99,999
                pref1 = num.ToString().Substring(0, 2)
                suff = num.ToString().Substring(2, num.ToString.Length - 2)
                strNumber = strNegative & pref1 & comma & suff
            ElseIf (num / 100000) < 10 Then '1,00,000 to 9,99,999
                pref1 = num.ToString().Substring(0, 1)
                pref2 = num.ToString().Substring(1, 2)
                suff = num.ToString().Substring(3, num.ToString.Length - 3)
                strNumber = strNegative & pref1 & comma & pref2 & comma & suff
            ElseIf (num / 1000000) < 10 Then '10,00,000 to 99,99,999
                pref1 = num.ToString().Substring(0, 2)
                pref2 = num.ToString().Substring(2, 2)
                suff = num.ToString().Substring(4, num.ToString.Length - 4)
                strNumber = strNegative & pref1 & comma & pref2 & comma & suff
            ElseIf (num / 10000000) < 10 Then '1,10,00,000 to 9,99,99,999
                pref1 = num.ToString().Substring(0, 1)
                pref2 = num.ToString().Substring(1, 2)
                pref3 = num.ToString().Substring(2, 2)
                suff = num.ToString().Substring(5, num.ToString.Length - 5)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            ElseIf (num / 100000000) < 10 Then '10,10,00,000 to 99,99,99,999
                pref1 = num.ToString().Substring(0, 2)
                pref2 = num.ToString().Substring(2, 2)
                pref3 = num.ToString().Substring(4, 2)
                suff = num.ToString().Substring(6, num.ToString.Length - 6)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            ElseIf (num / 1000000000) < 10 Then
                pref1 = num.ToString().Substring(0, 3)
                pref2 = num.ToString().Substring(3, 2)
                pref3 = num.ToString().Substring(5, 2)
                suff = num.ToString().Substring(7, num.ToString.Length - 7)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            ElseIf (num / 10000000000) < 10 Then
                pref1 = num.ToString().Substring(0, 4)
                pref2 = num.ToString().Substring(4, 2)
                pref3 = num.ToString().Substring(6, 2)
                suff = num.ToString().Substring(8, num.ToString.Length - 8)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            ElseIf (num / 100000000000) < 10 Then
                pref1 = num.ToString().Substring(0, 5)
                pref2 = num.ToString().Substring(5, 2)
                pref3 = num.ToString().Substring(7, 2)
                suff = num.ToString().Substring(9, num.ToString.Length - 9)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            ElseIf (num / 1000000000000) < 10 Then
                pref1 = num.ToString().Substring(0, 6)
                pref2 = num.ToString().Substring(6, 2)
                pref3 = num.ToString().Substring(8, 2)
                suff = num.ToString().Substring(10, num.ToString.Length - 10)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            ElseIf (num / 10000000000000) < 10 Then
                pref1 = num.ToString().Substring(0, 7)
                pref2 = num.ToString().Substring(7, 2)
                pref3 = num.ToString().Substring(9, 2)
                suff = num.ToString().Substring(11, num.ToString.Length - 11)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            ElseIf (num / 10000000000000) < 10 Then
                pref1 = num.ToString().Substring(0, 8)
                pref2 = num.ToString().Substring(8, 2)
                pref3 = num.ToString().Substring(10, 2)
                suff = num.ToString().Substring(12, num.ToString.Length - 12)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Else
                strNumber = num.ToString
            End If
        Else
            strNumber = num.ToString
        End If
        Return strNumber
    End Function


    Public Function PutComma1(ByVal num As Double)
        Dim strNumber As String = ""
        Dim comma As String = ","
        Dim pref1 As String = ""
        Dim pref2 As String = ""
        Dim pref3 As String = ""
        Dim suff As String = ""
        Dim strNegative As String = ""
        If num < 0 Then
            num = num.ToString().Substring(1, num.ToString.Length - 1)
            strNegative = "-"
        End If
        Dim length As Integer = Val(num.ToString.IndexOf(".").ToString)
        If length <= 0 Then
            length = num.ToString().Length
        End If
        Select Case length
            Case 4 ''1000 to 9999
                pref1 = num.ToString().Substring(0, 1) ''10,000 to 99,999
                suff = num.ToString().Substring(1, num.ToString.Length - 1)
                strNumber = strNegative & pref1 & comma & suff
            Case 5 ''1,00,000 to 9,99,999
                pref1 = num.ToString().Substring(0, 2)
                suff = num.ToString().Substring(2, num.ToString.Length - 2)
                strNumber = strNegative & pref1 & comma & suff
            Case 6 '10,00,000 to 99,99,999
                pref1 = num.ToString().Substring(0, 1)
                pref2 = num.ToString().Substring(1, 2)
                suff = num.ToString().Substring(3, num.ToString.Length - 3)
                strNumber = strNegative & pref1 & comma & pref2 & comma & suff
            Case 7 '1,10,00,000 to 9,99,99,999
                pref1 = num.ToString().Substring(0, 2)
                pref2 = num.ToString().Substring(2, 2)
                suff = num.ToString().Substring(4, num.ToString.Length - 4)
                strNumber = strNegative & pref1 & comma & pref2 & comma & suff
            Case 8 '10,10,00,000 to 99,99,99,999
                pref1 = num.ToString().Substring(0, 1)
                pref2 = num.ToString().Substring(1, 2)
                pref3 = num.ToString().Substring(2, 2)
                suff = num.ToString().Substring(5, num.ToString.Length - 5)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case 9
                pref1 = num.ToString().Substring(0, 2)
                pref2 = num.ToString().Substring(2, 2)
                pref3 = num.ToString().Substring(4, 2)
                suff = num.ToString().Substring(6, num.ToString.Length - 6)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case 10
                pref1 = num.ToString().Substring(0, 3)
                pref2 = num.ToString().Substring(3, 2)
                pref3 = num.ToString().Substring(5, 2)
                suff = num.ToString().Substring(7, num.ToString.Length - 7)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case 11
                pref1 = num.ToString().Substring(0, 4)
                pref2 = num.ToString().Substring(4, 2)
                pref3 = num.ToString().Substring(6, 2)
                suff = num.ToString().Substring(8, num.ToString.Length - 8)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case 12
                pref1 = num.ToString().Substring(0, 5)
                pref2 = num.ToString().Substring(5, 2)
                pref3 = num.ToString().Substring(7, 2)
                suff = num.ToString().Substring(9, num.ToString.Length - 9)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case 13
                pref1 = num.ToString().Substring(0, 6)
                pref2 = num.ToString().Substring(6, 2)
                pref3 = num.ToString().Substring(8, 2)
                suff = num.ToString().Substring(10, num.ToString.Length - 10)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case 14
                pref1 = num.ToString().Substring(0, 7)
                pref2 = num.ToString().Substring(7, 2)
                pref3 = num.ToString().Substring(9, 2)
                suff = num.ToString().Substring(11, num.ToString.Length - 11)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case 15
                pref1 = num.ToString().Substring(0, 8)
                pref2 = num.ToString().Substring(8, 2)
                pref3 = num.ToString().Substring(10, 2)
                suff = num.ToString().Substring(12, num.ToString.Length - 12)
                strNumber = strNegative & pref1 & comma & pref2 & comma & pref3 & comma & suff
            Case Else
                strNumber = num.ToString
        End Select

        Return strNumber
    End Function


A Class for convert number to word in bengali format.

Public Class ConvertWord
    Private Shared Function strReplicate(ByVal str As String, ByVal intD As Integer) As String
        'This fucntion padded "0" after the number to evaluate hundred, thousand and on....
        'using this function you can replicate any Charactor with given string.
        Dim i As Integer
        strReplicate = ""
        For i = 1 To intD
            strReplicate = strReplicate + str
        Next
        Return strReplicate
    End Function
    Public Shared Function AmtInWord(ByVal Num As Decimal) As String

        Dim strNum As String
        Dim strNumDec As String
        Dim StrWord As String
        strNum = Num

        If InStr(1, strNum, ".") <> 0 Then
            strNumDec = Mid(strNum, InStr(1, strNum, ".") + 1)

            If Len(strNumDec) = 1 Then
                strNumDec = strNumDec + "0"
            End If
            If Len(strNumDec) > 2 Then
                strNumDec = Mid(strNumDec, 1, 2)
            End If

            strNum = Mid(strNum, 1, InStr(1, strNum, ".") - 1)
            StrWord = IIf(CDbl(strNum) = 1, "", "") + NumToWord(CDbl(strNum)) + IIf(CDbl(strNumDec) > 0, " and Paise" + cWord3(CDbl(strNumDec)), "")
        Else
            StrWord = IIf(CDbl(strNum) = 1, "", "") + NumToWord(CDbl(strNum))
        End If
        AmtInWord = StrWord & " Taka Only."
        Return AmtInWord
    End Function
    Private Shared Function NumToWord(ByVal Num As Decimal) As String
        'I divided this function in two part.
        '1. Three or less digit number.
        '2. more than three digit number.
        Dim strNum As String
        Dim StrWord As String
        strNum = Num

        If Len(strNum) <= 3 Then
            StrWord = cWord3(CDbl(strNum))
        Else
            StrWord = cWordG3(CDbl(Mid(strNum, 1, Len(strNum) - 3))) + " " + cWord3(CDbl(Mid(strNum, Len(strNum) - 2)))
        End If
        NumToWord = StrWord
    End Function
    Private Shared Function cWordG3(ByVal Num As Decimal) As String
        '2. more than three digit number.
        Dim strNum As String = ""
        Dim StrWord As String = ""
        Dim readNum As String = ""
        strNum = Num
        If Len(strNum) Mod 2 <> 0 Then
            readNum = CDbl(Mid(strNum, 1, 1))
            If readNum <> "0" Then
                StrWord = retWord(readNum)
                readNum = CDbl("1" + strReplicate("0", Len(strNum) - 1) + "000")
                StrWord = StrWord + " " + retWord(readNum)
            End If
            strNum = Mid(strNum, 2)
        End If
        While Not Len(strNum) = 0
            readNum = CDbl(Mid(strNum, 1, 2))
            If readNum <> "0" Then
                StrWord = StrWord + " " + cWord3(readNum)
                readNum = CDbl("1" + strReplicate("0", Len(strNum) - 2) + "000")
                StrWord = StrWord + " " + retWord(readNum)
            End If
            strNum = Mid(strNum, 3)
        End While
        cWordG3 = StrWord
        Return cWordG3
    End Function
    Private Shared Function cWord3(ByVal Num As Decimal) As String
        '1. Three or less digit number.
        Dim strNum As String = ""
        Dim StrWord As String = ""
        Dim readNum As String = ""
        If Num < 0 Then Num = Num * -1
        strNum = Num

        If Len(strNum) = 3 Then
            readNum = CDbl(Mid(strNum, 1, 1))
            StrWord = retWord(readNum) + " Hundred"
            strNum = Mid(strNum, 2, Len(strNum))
        End If

        If Len(strNum) <= 2 Then
            If CDbl(strNum) >= 0 And CDbl(strNum) <= 20 Then
                StrWord = StrWord + " " + retWord(CDbl(strNum))
            Else
                StrWord = StrWord + " " + retWord(CDbl(Mid(strNum, 1, 1) + "0")) + " " + retWord(CDbl(Mid(strNum, 2, 1)))
            End If
        End If

        strNum = CStr(Num)
        cWord3 = StrWord
        Return cWord3
    End Function

    Private Shared Function retWord(ByVal Num As Decimal) As String
        'This two dimensional array store the primary word convertion of number.
        retWord = ""
        Dim ArrWordList(,) As Object = {{0, ""}, {1, "One"}, {2, "Two"}, {3, "Three"}, {4, "Four"}, _
                                        {5, "Five"}, {6, "Six"}, {7, "Seven"}, {8, "Eight"}, {9, "Nine"}, _
                                        {10, "Ten"}, {11, "Eleven"}, {12, "Twelve"}, {13, "Thirteen"}, {14, "Fourteen"}, _
                                        {15, "Fifteen"}, {16, "Sixteen"}, {17, "Seventeen"}, {18, "Eighteen"}, {19, "Nineteen"}, _
                                        {20, "Twenty"}, {30, "Thirty"}, {40, "Forty"}, {50, "Fifty"}, {60, "Sixty"}, _
                                        {70, "Seventy"}, {80, "Eighty"}, {90, "Ninety"}, {100, "Hundred"}, {1000, "Thousand"}, _
                                        {100000, "Lakh"}, {10000000, "Crore"}}

        Dim i As Integer
        For i = 0 To UBound(ArrWordList)
            If Num = ArrWordList(i, 0) Then
                retWord = ArrWordList(i, 1)
                Exit For
            End If
        Next
        Return retWord
    End Function
End Class

Some English words with Bengali meaning.

aisle-করিডোর(passage,walk way)
anonymous-নামবিহীন(nameless,unknown)
elevation-টিলা,উন্নয়ন(promotion)