How do i pass params to SQL Templates?

Discussion in 'ASP .Net' started by Padgett, Jul 4, 2003.

  1. Padgett

    Padgett Guest

    Hi There,

    I'm trying to do an XML / XLS transform but am having trouble retrieving my
    data. The trouble seems to be with the parameter - if I hard code the id in
    the template SQL, everything works, but when I use .CreateParameter to set
    the @ID value then i get an exception:

    "Microsoft.Data.SqlXml.SqlXmlException: Exception from HRESULT:
    0x80040E21. ---> System.Runtime.InteropServices.COMException (0x80040E21):
    Exception from HRESULT: 0x80040E21. at
    Microsoft.Data.SqlXml.Common.ISQLXMLCommandManagedInterface.ExecuteToOutputS
    tream()"

    The code looks like this:

    Dim oTransform As New Xsl.XslTransform
    Dim DataURL As String

    Try
    'Set response properties
    With Response
    .ContentType = "text/html"
    'Get the data as XML
    Dim ID As Integer
    ID = Integer.Parse(Request.QueryString("ID"))
    Dim oCmd As New SqlXmlCommand(CONN_STRING)
    oCmd.CommandType = SqlXmlCommandType.TemplateFile
    oCmd.CommandText =
    Server.MapPath("./templates/Template.xml")
    Dim oParam As SqlXmlParameter = oCmd.CreateParameter()
    oParam.Name = "@ID"
    oParam.Value = ID

    'Transform
    oTransform.Load(Server.MapPath("./templates/p7.xsl"))
    Dim xDoc As New XPathDocument(oCmd.ExecuteXmlReader,
    XmlSpace.Default)

    Dim xNav As XPathNavigator = xDoc.CreateNavigator()
    oTransform.Transform(xDoc, Nothing, Response.OutputStream,
    Nothing)
    .Flush()
    End With

    Catch ex As System.Xml.Xsl.XsltException
    Throw New Exception("Doh", ex)
    Catch ex As Exception
    Throw New Exception("Doh", ex)
    Finally
    oTransform = Nothing
    End Try

    And the Template.xml file looks like:

    <Plan xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:query>
    SELECT Business.*,
    FROM Business
    WHERE (Business.BusinessID = @ID)
    FOR XML AUTO, ELEMENTS
    </sql:query>
    </Plan>

    The only thing the MS documentation has to say is:

    "Note If you pass a parameter to a template, the parameter name must begin
    with '@' (for example, p.Name="@EmployeeID", where p is a SqlXmlParameter
    object)."

    which doesn't help much as there isn't an example. (I tried this, but
    cannot seem to get it to work)

    Any help would be greatly appreciated.

    If anyone has successfully passed a parameter to a query like this an
    example would be wonderful!

    Cheers,

    Padgett Rowell
    Padgett, Jul 4, 2003
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Hubbard
    Replies:
    0
    Views:
    4,489
    David Hubbard
    Mar 1, 2006
  2. JKop
    Replies:
    3
    Views:
    468
  3. recover
    Replies:
    2
    Views:
    799
    recover
    Jul 25, 2006
  4. Barry
    Replies:
    9
    Views:
    443
    Ara.T.Howard
    Sep 15, 2005
  5. ArbolOne

    Templates with two params

    ArbolOne, Sep 28, 2012, in forum: C++
    Replies:
    4
    Views:
    310
    red floyd
    Sep 29, 2012
Loading...

Share This Page