none
How do I create and display sql statement using vb.net code? RRS feed

  • Question

  • I have function which is using id_judge from xml document and then I would like to use that id_judge to create a sql select statement. This statement will be a string in strOutputSql variable.

    Every sql select statement will be the same. The only difference will be CodeID because this is the variable for the id_judge.

    Right now my Function's Return strOutputSql is showing only 09002P . I would like to modify this variable to instead show the sql statement which I have pasted below under header Expected sql output.

    How do I do this inside my function?

    For the xml document I have, my Function should return the following output inside the string variable strOutputSql

    Expected sql output

    select * 
    from ucode as uc
    inner join uJudOffc as uj on (uj.JudicialOffCodeID = uc.CodeID)
    where Code='09002P'
    and uc.CacheTableID = 136
    FOR XML PATH('JudgeCode')

    My vb.net 4.0 code

    Private Class MessageProcessor
    Inherits Msc.Integration.MessageBroker.Library.v4.XmlMessageProcessor
    
        Protected Overrides Sub ProcessMessage(ByRef aobjBroker As MessageBroker.Library.v4.Broker, ByRef aobjXmlInputDoc As System.Xml.XmlDocument, ByRef aobjInstantiatedObjectsCollection As Microsoft.VisualBasic.Collection)
        MyBase.ProcessMessage(aobjBroker, aobjXmlInputDoc, aobjInstantiatedObjectsCollection)
        Dim objXmlLoadedElement As XmlElement
        Dim strSql As String
        Dim strJudgeId As String
    
        objXmlLoadedElement = aobjXmlInputDoc.CreateElement("Loaded")
        aobjXmlInputDoc.DocumentElement.AppendChild(objXmlLoadedElement)
    
        strJudgeId = aobjXmlInputDoc.DocumentElement.SelectSingleNode("id_judge").InnerText
        strSql = GetSql(strJudgeId)
        End Sub
    
        Function GetSql(ByVal astrJudgeId As String) As String
        Dim strOutputSql As String
    
        strOutputSql = astrJudgeId
    
        Return strOutputSql
        End Function
    End Class

    My xml document with id_judge 09002P that is used to create the sql statement in vb.net function.

        <?xml version="1.0" encoding="utf-8"?>
    <GJUDGE triggerEvent="Update" xmlns="">
       <id_judge>09002P</id_judge>
        <Loaded>
            <JudgeCode>
                <CodeID>7598</CodeID>
                <CacheTableID>136</CacheTableID>
                <RevisionID>5321</RevisionID>
                <Code>07001G</Code>
                <RootNodeID>0</RootNodeID>
                <EffectiveDate>2007-06-14T00:00:00</EffectiveDate>
                <UserIDCreate>1</UserIDCreate>
                <TimestampCreate>2003-02-01T16:45:00</TimestampCreate>
                <UserIDChange>1</UserIDChange>
                <TimestampChange>2015-07-06T09:49:27.700</TimestampChange>
                <Description>Amos,Wako ,</Description>
                <JudicialOffCodeID>7598</JudicialOffCodeID>
                <NameFirst>Amos</NameFirst>
                <NameLast>Wako</NameLast>
                <UnavailableOnly>1</UnavailableOnly>
                <Private>0</Private>
            </JudgeCode>
        </Loaded>
    </GJUDGE>

    Tuesday, July 14, 2015 8:43 PM

Answers

  • Try this:

        Function GetSql(ByVal judgeId As String) As String

            Return <text>

    select *

    from ucode as uc

    inner join uJudOffc as uj on uj.JudicialOffCodeID = uc.CodeID

    where Code='<%= judgeId %>'

    and uc.CacheTableID = 136

    FOR XML PATH('JudgeCode')

                   </text>.Value.Trim

        End Function

    But in order to avoid certain well-known problems, you should consider “parameterised SQL queries”.


    Wednesday, July 15, 2015 5:37 AM

All replies

  • Try this:

        Function GetSql(ByVal judgeId As String) As String

            Return <text>

    select *

    from ucode as uc

    inner join uJudOffc as uj on uj.JudicialOffCodeID = uc.CodeID

    where Code='<%= judgeId %>'

    and uc.CacheTableID = 136

    FOR XML PATH('JudgeCode')

                   </text>.Value.Trim

        End Function

    But in order to avoid certain well-known problems, you should consider “parameterised SQL queries”.


    Wednesday, July 15, 2015 5:37 AM
  • Thanks this worked....Also my boss decided that using parameterised SQL is better
    Wednesday, July 15, 2015 4:57 PM