none
join in vb.net

    Question

  • what does the 39 mean.

    I am new to reporting especially with the vb  code.

          sOut = sOut & "FROM (SELECT StrToSet('{" & replace(Join(Report.Parameters!OrgLevel1.Value,","),chr(39),chr(39)&chr(39)) & "}', constrained) ON COLUMNS "

                iCriteria = iCriteria + 1

          end if

    Sunday, March 23, 2014 1:49 AM

Answers

  • Chr(39) is "Character Number 39 in the ASCII table".


    “If you want something you've never had, you need to do something you've never done.”

    Don't forget to mark helpful posts and answers ! Answer an interesting question? Write a new article about it! My Articles
    *This post does not reflect the opinion of Microsoft, or its employees.

    Tuesday, April 01, 2014 3:58 AM
    Moderator
  • Its not 39 alone, its Chr(39) which represents APOSTROPHES (').

    Sunday, March 23, 2014 2:38 AM
  • The above piece of code is replacing "test1,test'2,test3" string returned from JOIN SSRS function with "test1,test''2,test3".  Did you notice I have added (') character two times.

    SQL statement does not support single (') character within a string. Hence it is converting to double ('') so that it can be pass as a sql query.

    Below query will through error,

    SELECT * FROM TABLENAME WHERE COL1 = 'ABC'DEF'

    You need to write as below. That is the reason why we are replacing (') with ('').

    SELECT * FROM TABLENAME WHERE COL1 = 'ABC''DEF'

    Or you can try running the below two print in SSMS,

    PRINT  'tod'ay'
    PRINT  'tod''ay'


    Regards, RSingh

    Sunday, March 23, 2014 12:21 PM

All replies

  • Its not 39 alone, its Chr(39) which represents APOSTROPHES (').

    Sunday, March 23, 2014 2:38 AM
  • The above piece of code is replacing "test1,test'2,test3" string returned from JOIN SSRS function with "test1,test''2,test3".  Did you notice I have added (') character two times.

    SQL statement does not support single (') character within a string. Hence it is converting to double ('') so that it can be pass as a sql query.

    Below query will through error,

    SELECT * FROM TABLENAME WHERE COL1 = 'ABC'DEF'

    You need to write as below. That is the reason why we are replacing (') with ('').

    SELECT * FROM TABLENAME WHERE COL1 = 'ABC''DEF'

    Or you can try running the below two print in SSMS,

    PRINT  'tod'ay'
    PRINT  'tod''ay'


    Regards, RSingh

    Sunday, March 23, 2014 12:21 PM
  • Thanks, for the response.

    Do you think you can figure out what the following code does.


    Public

    Function fCheckForOrgParmOmit(ByVal sVal as string, ByVal oParm as Parameter, ByVal iLvl as integer) as Boolean


    'Returns True to omit the parameter, false to include it



    if

    Left(Report.Parameters!ResponsibleOrChargeUnit.Value,4) = Left(sVal,4) then


    if iLvl=1 and Report.Parameters!pSummary1.Value=sVal then


    Return False


    elseif iLvl=2 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal) then


    Return False


    elseif iLvl=3 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal or Report.Parameters!pSummary3.Value=sVal) then


    Return False


    elseif InStr(join(oParm.Value, " "), "All")>0 then


    Return True


    else


    Return False


    end if

    else


    return True

    end

    if

    End

    Function

    Public

    Function fCheckForOrgUnitParmOmit(ByVal sVal as string, ByVal Parm as Parameter, ByVal iLvl as integer) as boolean



    Dim bOmit as Boolean


    ' if the value is responsible unit, but the parm is charge unit, omit


    if left(sVal,4) = "Resp" and Instr(Join(Parm.Value, " "), "Charge")>0 then


    bOmit =

    True


    elseif left(sVal,4) = "Char" and Instr(Join(Parm.Value, " "), "Resp")>0 then


    bOmit =

    True


    else


    bOmit =

    False


    end if


    if iLvl=1 and (Report.Parameters!pSummary1.Value=sVal) then


    return False


    elseif iLvl=2 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal) then


    return False


    elseif iLvl=3 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal or Report.Parameters!pSummary3.Value=sVal) then


    return False


    elseif InStr(join(Parm.Value, " "), "All")>0 or bOmit=True


     

    return True


    else


     

    return False


    end if



    End

    Function

    Public

    Function fCheckForOtherParmOmit(ByVal sVal as string, ByVal Parm as Parameter, ByVal iLvl as integer) as boolean


     

    if iLvl=1 and (Report.Parameters!pSummary1.Value=sVal) then


    return False


     

    elseif iLvl=2 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal) then


     

    return False


     

    elseif iLvl=3 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal or Report.Parameters!pSummary3.Value=sVal) then


     

    return False


     

    elseif InStr(join(Parm.Value, " "), "All")>0 then


     

    return True


     

    else


     

    return False


     

    end if

    End

    Function

    Public

    Function fParseOrgParm(ByVal sParm as Parameter, ByVal iChar as integer) as String


     

    dim sOut as String


      sOut =

    ""


     

    for i as integer = 0 to sParm.Count-1

      sOut = sOut & left(sParm.Label(i),iChar) &

    ","


     

    next

    return

    left(sOut, len(rtrim(sOut))-1)


    End

    Function

    Public

    Function fParseParm(ByVal sFluff as string, ByVal sParm as Parameter) as String


    dim sOut as String


    If sParm.Value(0).Contains("[All]") THEN


    return Nothing


    else


    sOut = Replace(Replace(join(sParm.Value,

    ","),sFluff,""),"],", ",")

    return Left(sOut, LEN(rtrim(sOut))-1)

    end if

    End

    Function

    Public

    function fParseOrgLevelParm(ByVal sParm as parameter, ByVal sType as String) as string


    'Possible values

    '[Responsible Unit].[Org Level FY].&[2012]

    '[Responsible Unit].[Org Level 1].&[60]

    '[Responsible Unit].[Org Level 2].&[10]

    '[Responsible Unit].[Org Level 3].&[02]


    Dim

    sOut as String

    Dim

    sTmp as String

    Dim

    iCnt as integer

    Dim

    ex As New System.Text.RegularExpressions.Regex("&")


    Dim

    m As System.Text.RegularExpressions.MatchCollection



    If

    sParm.IsMultiValue Then


    sOut =

    ""


    For i as integer = 0 to sParm.Count-1

    sTmp = Right(sParm.Value(i), Len(sParm.Value(i))-Instr(sParm.Value(i),

    "&")-1)

    m = ex.Matches(sParm.Value(i))

    iCnt = m.Count.tostring()

    if sType = "FY" then


    sTmp = Left(sTmp,4) &

    ","


    elseif sType="L1" or sType="L2" or sType="L3" then


    sTmp = Left(sTmp,2) &

    ","


    else


    sTmp =

    ""

    end if


    sOut = sTmp & sOut

    Next


    if Len(sOut)>0 then


    sOut = Left(sOut, Len(sOut)-1)

    else


    sOut =

    Nothing


    end if

    Else


    sOut = Right(sParm.Value, Len(sParm.Value)-Instr(sParm.Value,

    "&")-1)

    m = ex.Matches(sParm.Value)

    iCnt = m.Count.tostring()

    if sType = "FY" then


    sOut = Left(sOut,4)

    elseif sType="L3" or sType="L3" or sType="L3" then


    sOut = Left(sOut,2)

    else


    sOut =

    Nothing


    end if

    End

    If

    return

    sOut


    End

    Function

    Monday, March 24, 2014 9:54 PM
  • can someone explain the following code to me .I am in new in vb.net

    Public

    Function fCheckForOrgParmOmit(ByVal sVal as string, ByVal oParm as Parameter, ByVal iLvl as integer) as Boolean


    'Returns True to omit the parameter, false to include it



    if

    Left(Report.Parameters!ResponsibleOrChargeUnit.Value,4) = Left(sVal,4) then


    if iLvl=1 and Report.Parameters!pSummary1.Value=sVal then


    Return False


    elseif iLvl=2 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal) then


    Return False


    elseif iLvl=3 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal or Report.Parameters!pSummary3.Value=sVal) then


    Return False


    elseif InStr(join(oParm.Value, " "), "All")>0 then


    Return True


    else


    Return False


    end if

    else


    return True

    end

    if

    End

    Function

    Public

    Function fCheckForOrgUnitParmOmit(ByVal sVal as string, ByVal Parm as Parameter, ByVal iLvl as integer) as boolean



    Dim bOmit as Boolean


    ' if the value is responsible unit, but the parm is charge unit, omit


    if left(sVal,4) = "Resp" and Instr(Join(Parm.Value, " "), "Charge")>0 then


    bOmit =

    True


    elseif left(sVal,4) = "Char" and Instr(Join(Parm.Value, " "), "Resp")>0 then


    bOmit =

    True


    else


    bOmit =

    False


    end if


    if iLvl=1 and (Report.Parameters!pSummary1.Value=sVal) then


    return False


    elseif iLvl=2 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal) then


    return False


    elseif iLvl=3 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal or Report.Parameters!pSummary3.Value=sVal) then


    return False


    elseif InStr(join(Parm.Value, " "), "All")>0 or bOmit=True


     

    return True


    else


     

    return False


    end if



    End

    Function

    Public

    Function fCheckForOtherParmOmit(ByVal sVal as string, ByVal Parm as Parameter, ByVal iLvl as integer) as boolean


     

    if iLvl=1 and (Report.Parameters!pSummary1.Value=sVal) then


    return False


     

    elseif iLvl=2 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal) then


     

    return False


     

    elseif iLvl=3 and (Report.Parameters!pSummary1.Value=sVal or Report.Parameters!pSummary2.Value=sVal or Report.Parameters!pSummary3.Value=sVal) then


     

    return False


     

    elseif InStr(join(Parm.Value, " "), "All")>0 then


     

    return True


     

    else


     

    return False


     

    end if

    End

    Function

    Public

    Function fParseOrgParm(ByVal sParm as Parameter, ByVal iChar as integer) as String


     

    dim sOut as String


      sOut =

    ""


     

    for i as integer = 0 to sParm.Count-1

      sOut = sOut & left(sParm.Label(i),iChar) &

    ","


     

    next

    return

    left(sOut, len(rtrim(sOut))-1)


    End

    Function

    Public

    Function fParseParm(ByVal sFluff as string, ByVal sParm as Parameter) as String


    dim sOut as String


    If sParm.Value(0).Contains("[All]") THEN


    return Nothing


    else


    sOut = Replace(Replace(join(sParm.Value,

    ","),sFluff,""),"],", ",")

    return Left(sOut, LEN(rtrim(sOut))-1)

    end if

    End

    Function

    Public

    function fParseOrgLevelParm(ByVal sParm as parameter, ByVal sType as String) as string


    'Possible values

    '[Responsible Unit].[Org Level FY].&[2012]

    '[Responsible Unit].[Org Level 1].&[60]

    '[Responsible Unit].[Org Level 2].&[10]

    '[Responsible Unit].[Org Level 3].&[02]


    Dim

    sOut as String

    Dim

    sTmp as String

    Dim

    iCnt as integer

    Dim

    ex As New System.Text.RegularExpressions.Regex("&")


    Dim

    m As System.Text.RegularExpressions.MatchCollection



    If

    sParm.IsMultiValue Then


    sOut =

    ""


    For i as integer = 0 to sParm.Count-1

    sTmp = Right(sParm.Value(i), Len(sParm.Value(i))-Instr(sParm.Value(i),

    "&")-1)

    m = ex.Matches(sParm.Value(i))

    iCnt = m.Count.tostring()

    if sType = "FY" then


    sTmp = Left(sTmp,4) &

    ","


    elseif sType="L1" or sType="L2" or sType="L3" then


    sTmp = Left(sTmp,2) &

    ","


    else


    sTmp =

    ""

    end if


    sOut = sTmp & sOut

    Next


    if Len(sOut)>0 then


    sOut = Left(sOut, Len(sOut)-1)

    else


    sOut =

    Nothing


    end if

    Else


    sOut = Right(sParm.Value, Len(sParm.Value)-Instr(sParm.Value,

    "&")-1)

    m = ex.Matches(sParm.Value)

    iCnt = m.Count.tostring()

    if sType = "FY" then


    sOut = Left(sOut,4)

    elseif sType="L3" or sType="L3" or sType="L3" then


    sOut = Left(sOut,2)

    else


    sOut =

    Nothing


    end if

    End

    If

    return

    sOut

    Monday, March 24, 2014 10:10 PM
  • This forum is for SQL Server not for VB.NET, so refer you to 

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral

    Or to lear VB.NET step by Step :

    http://www.youtube.com/watch?v=hkcO_M9gcNw

    Thank's

    Tuesday, March 25, 2014 12:03 AM
  • Hi Dre01,

    Since this issue is related to Visual Basic, I will move this thread to Visual Basic forum. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, March 26, 2014 1:42 AM
  • can you explain this

    (instr(join(Report.Parameters!EA.value,","),"All")>0) then

    Monday, March 31, 2014 8:47 PM
  • Chr(39) is "Character Number 39 in the ASCII table".


    “If you want something you've never had, you need to do something you've never done.”

    Don't forget to mark helpful posts and answers ! Answer an interesting question? Write a new article about it! My Articles
    *This post does not reflect the opinion of Microsoft, or its employees.

    Tuesday, April 01, 2014 3:58 AM
    Moderator