Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Displaying XML returned from SQL Server using ASP.NET (vb) and xslt

I am upgrading an application from ASP to ASP.NET.  I have developed code that retrieves XML data from SQL Server (using 'for xml auto'), adds a root node, and then attempts to display the code using an XSLT style sheet.  I am sure the stylesheet is well-formed as it is working quite well in the original ASP application.  My code compiles, however nothing is displayed in the browser.  What am I doing wrong?  

***I am fairly certain that the error in my code lies in one of 2 sections... I have noted these two sections of code with "***********" to draw attention to them ***

Thanks all!

Jessica


     Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim myConn As SqlConnection
        Dim myCmd As New SqlCommand()

        Dim xmlReader As XmlReader
        Dim xmlRoot As XmlElement
        Dim xmlDoc As New XmlDocument()

        Dim sqlParam As New SqlParameter()
        Dim sqlParam2 As New SqlParameter()
        Dim sqlParam3 As New SqlParameter()
        Dim sqlParam4 As New SqlParameter()

        Dim refLon As Decimal
        Dim refLat As Decimal
        Dim speccode As String
        Dim provname As String
        Dim networkindex As Integer
        Dim rowstart As Integer
        Dim rowend As Integer
        Dim spec2 As String
        Dim xsltFileLoc As String

        Dim totalcount As Integer
        Dim sessionID As Integer

'following values hardcoded, for sake of example
        refLon = -122.70074
        refLat = 45.53237
        speccode = "PACU"
        networkindex = 1
        rowstart = 1
        rowend = 5
        xsltFileLoc = Server.MapPath("provlocations.xslt")

'initialize connection string  
        myConn = New SqlConnection(connStr)
        myConn.Open()

'initialize command object
        myCmd.CommandType = CommandType.StoredProcedure
        myCmd.CommandText = "getProvs"
        myCmd.Connection = myConn

'add parameters to command object
        sqlParam = myCmd.Parameters.Add(New SqlParameter("@RefLong", SqlDbType.Real, 14))
        sqlParam.Direction = ParameterDirection.Input
        sqlParam.Value = RefLon

        sqlParam2 = myCmd.Parameters.Add(New SqlParameter("@RefLat", SqlDbType.Real, 14))
        sqlParam2.Direction = ParameterDirection.Input
        sqlParam2.Value = refLat
       
        sqlParam3 = myCmd.Parameters.Add(New SqlParameter("@totalRecords", SqlDbType.Int))
        sqlParam3 .Direction = ParameterDirection.Output

        sqlParam4 = myCmd.Parameters.Add(New SqlParameter("@SessionID", SqlDbType.Int))
        sqlParam4 .Direction = ParameterDirection.Output

'execute command object which returns XML and output parameters
        xmlReader = myCmd.ExecuteXmlReader()

'retrieve values outputted by procedure (I have verified, and these values are being populated correctly)
        totalcount = myCmd.Parameters("@totalRecords").Value
        sessionID = myCmd.Parameters("@sessionID").Value
       
'add root node (as "for xml auto" does not include the root node in the return value) and read xml
'from Reader
*******************************************************  
        xmlReader.MoveToContent()
        xmlRoot = xmlDoc.CreateElement("Root")
        xmlDoc.AppendChild(xmlRoot)
        While Not xmlReader.EOF
            xmlRoot.InnerXml = xmlRoot.InnerXml & xmlReader.ReadOuterXml()
        End While
        xmlReader.Close()
  *******************************************************    
 'add parameter values that the XSLT file expects
        Dim xsltArgList As New XsltArgumentList()
        Dim targetURL As String
        Dim fc As String
       
        targetURL = "originDrivingDirections.asp"
        fc = ""
       
        xsltArgList.AddParam("driveitUrl", "", targeturl)
        xsltArgList.AddParam("newSearchURL", "", "selectaddr.asp")
        xsltArgList.AddParam("ProvListingURL", "", "showprovs.asp")
        xsltArgList.AddParam("displaycount", "", 5)
        xsltArgList.AddParam("actualcount", "", totalCount)
        xsltArgList.AddParam("FC", "", CStr(FC))
        xsltArgList.AddParam("lon", "", CStr(Server.UrlEncode(refLon)))
        xsltArgList.AddParam("lat", "", CStr(Server.UrlEncode(refLat)))
        xsltArgList.AddParam("SessionID", "", sessionID)
       
 'declare and load XSLT file
        Dim xslt As New XslTransform()
        xslt.Load(xsltFileLoc)
******************************************************************
'output XML using xslt stylesheet
        Dim writer As New XmlTextWriter(Response.Output)
        xslt.Transform(xmlDoc, xsltArgList, writer)
        writer.Close()
        myConn.Close()
******************************************************************
End Sub
0
jmamer
Asked:
jmamer
  • 5
  • 5
1 Solution
 
jmamerAuthor Commented:
test
0
 
dfiala13Commented:
Let's see your stylesheet.
0
 
dfiala13Commented:
An example of the XML output would help as well.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jmamerAuthor Commented:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="html" encoding="iso-8859-1" />
      
      
      <xsl:param name="Url" />
      <xsl:param name="driveitUrl" />
      <xsl:param name="newSearchURL" />
      <xsl:param name="ProvListingURL"/>
      <xsl:param name="displaycount"/>
      <xsl:param name="actualcount"/>
      <xsl:param name="FC"/>
      <xsl:param name="lon"/>
      <xsl:param name="lat"/>
                <xsl:param name="SessionID"/>
   
  <xsl:template match="/" >
    <xsl:apply-templates select= "Error"/>
    <xsl:apply-templates select= "root/ProviderLocations" />
 </xsl:template>
 
<xsl:template match="Error" name="handle_error">
            <FONT color="red">
                        <xsl:text>ErrorNumber: </xsl:text>
                <b><xsl:value-of select="//ErrorNumber" /></b>
                        <br></br>
                        <xsl:text>Description: </xsl:text>
                        <b><xsl:value-of select="//Description" /></b>
            </FONT>
            <br></br>
      </xsl:template>

   
    <xsl:template match="root/ProviderLocations">
           
          <xsl:variable name = "startrow" select="//WebSessionProLookup[1]/@RowNumber"/>
      <xsl:choose>
        <!-- if we have any location node, build a grid-->
        <xsl:when test= "//WebSessionProLookup">  
         

                   <table width="475" ID="Table1"><tr><td align="right" valign="top">
             <form id="printview" name="printview" Method="GET" style="display:0">
               <xsl:attribute name="action">providers_print.asp</xsl:attribute>
               <xsl:call-template name="hidden_parameters">                  
               <xsl:with-param name="startrow" select="$startrow"/>    
         <xsl:with-param name="displaycount" select="$displaycount"/>
               </xsl:call-template>
               <input type="hidden" name="S" ID="Hidden1">
                        <xsl:attribute name="value"><xsl:value-of select="number($startrow)"/></xsl:attribute>
                        </input>
            </form>      
               [<a>
                <xsl:attribute name="href">javascript:void(printview.submit())</xsl:attribute>
                     Print Results
            </a>]

          </td>
          </tr>
          </table>
          <div class="headercontainer">
          <table width="475" border="0" cellspacing="0" cellpadding="0">
                  
                  <tr class="tableheader">
                              <td width="15"><p class="nomargin"><strong>No.</strong></p>
                              <img src="/images/sp.gif" width="15" height="1" border="0" />
                              </td>
                              <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td width="132"><p class="nomargin"><strong>Provider Name</strong></p>
                              <img src="/images/sp.gif" width="132" height="1" border="0" />
                              </td>
                              <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td width="60"><p class="nomargin"><strong>Specialty</strong></p>
                              <img src="/images/sp.gif" width="60" height="1" border="0" />
                              </td>
                              <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td width="117"><p class="nomargin"><strong>Phone Number &amp; Address</strong></p>
                              <img src="/images/sp.gif" width="117" height="1" border="0" />
                              </td>
                              <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td width="50"><p class="nomargin"><strong>Dist.</strong><br />(miles)</p>
                              <img src="/images/sp.gif" width="50" height="1" border="0" />
                              </td>
                              <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                              <td width="50"><p class="nomargin"><strong>Map</strong></p>
                              <img src="/images/sp.gif" width="50" height="1" border="0" />
                              </td>
                  </tr>
          <xsl:for-each select="//WebSessionProLookup">
            <xsl:if test="position()&lt;=number($displaycount)">
            
          <tr valign="top">
            <td><p class="highmargin"><xsl:value-of select="@RowNumber"/></p></td>
            <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                  <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
            <td width="132"><p class="highmargin"><xsl:value-of select="@ProvName"/>          
            </p></td>
            <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
            <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                <td width="60"><p class="highmargin">
                  <xsl:choose>
                  <xsl:when test='contains(@Specialty, "/")'>
                  <xsl:value-of select='concat(substring-before(@Specialty,"/"), "/ ", substring-after(@Specialty, "/") )'/>
                  </xsl:when>
                  <xsl:otherwise>
                  <xsl:value-of select = "@Specialty"/>
                  </xsl:otherwise>
                  </xsl:choose></p></td>
                <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
            <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                <td width="117"><p class="highmargin">
                        <xsl:variable name="phone" select="@Phone"/>
                <b><xsl:value-of select='concat(substring($phone, 1, 3), "-", substring($phone, 4, 3), "-", substring($phone, 7, 4))'/></b>
                    <br></br>
                    <xsl:value-of select="@Address1"/>
                    <br></br>
                        <xsl:value-of select="@City"/>
                        <xsl:text>, </xsl:text>
                        <xsl:value-of select="@State"/>
                        <xsl:text> </xsl:text>
                        <xsl:value-of select="@Zip"/>
                        </p></td>
              <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
            <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
                <td><p class="highmargin"><xsl:value-of select ="@Distance"/></p></td>
                <td bgcolor="#DEE9FB" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
            <td bgcolor="#FFFFFF" width="1"><img src="/images/sp.gif" width="1" height="1" border="0" /></td>
         
            <td><p class="highmargin">
            <a>
              <xsl:attribute name="href">
               <xsl:value-of select="$driveitUrl"/>&amp;RowNumber=<xsl:value-of select="@RowNumber"/>&amp;SessionID=<xsl:value-of select="$SessionID"/>
              </xsl:attribute>
              <xsl:text>Map</xsl:text><br/>
             
             
            </a>
            </p>
                  
            </td>
              </tr>  
              </xsl:if>      
          </xsl:for-each>
          </table>
          </div>
          <br></br>
                <p><strong>Click on the "<span class="bluetext">Map</span>" link for detailed directions.</strong></p>
      
      <p>
           
           
              <xsl:comment>
                <xsl:text>StartRow:</xsl:text>            
                <xsl:value-of select="$startrow"/><br/>
                <xsl:text>DisplayCount:</xsl:text>            
                <xsl:value-of select="$displaycount"/><br/>
                <xsl:text>ActualCount:</xsl:text>            
                <xsl:value-of select="$actualcount"/><br/>
              </xsl:comment>
             
             
             
          <xsl:if test="number($displaycount)&lt;number($actualcount)">
                        <xsl:choose>
                               <xsl:when test="number($startrow) &gt;1">
                                          <xsl:choose>
                                                <xsl:when test="number($startrow) + number($displaycount) &lt;=number($actualcount)">
                                                      <table border="0" cellpadding="0" cellspacing="0">
                                                            <tr>
                                                                  <td>
                                                                         <form Method="GET" >
                                                                              <xsl:attribute name="action"><xsl:value-of select="$Url"/></xsl:attribute>
                                                                              <xsl:call-template name="hidden_parameters">              
                                                                              <xsl:with-param name="startrow" select="$startrow"/>
                                                                              <xsl:with-param name="displaycount" select="$displaycount"/>
                                                                              </xsl:call-template>            
                                                                              <input type="hidden" name="S">
                                                                                    <xsl:attribute name="value"><xsl:value-of select="number($startrow)-number($displaycount)"/></xsl:attribute>
                                                                              </input>
                                                
                                                                              <input type="submit" class="formbtn2" >
                                                                                    <xsl:attribute name="value"><xsl:text>Display Previous </xsl:text><xsl:value-of select="$displaycount"/><xsl:text> listings</xsl:text></xsl:attribute>
                                                                              </input>
                                                                        </form>
                                                                  </td>
                                                                  <td width="10"><img src="/images/sp.gif" width="10" height="1" border="0" /></td>          
                                                                  <td>
                                                                  <form Method="GET" >
                                                                              <xsl:attribute name="action"><xsl:value-of select="$Url"/></xsl:attribute>
                                                                              <xsl:call-template name="hidden_parameters">              
                                                                              <xsl:with-param name="startrow" select="$startrow"/>
                                                                              <xsl:with-param name="displaycount" select="$displaycount"/>
                                                                              </xsl:call-template>            
                                                                              <input type="hidden" name="S">
                                                                                    <xsl:attribute name="value"><xsl:value-of select="number($startrow)+number($displaycount)"/></xsl:attribute>
                                                                              </input>
                                                
                                                                              <input type="submit" class="formbtn2" >
                                                                                    <xsl:attribute name="value"><xsl:text>Display next </xsl:text><xsl:value-of select="$displaycount"/><xsl:text> listings</xsl:text></xsl:attribute>
                                                                              </input>
                                                                        </form>
                                                            </td>
                                                      </tr>
                                                </table>
                                          </xsl:when>
                                          <xsl:otherwise>
                                                      <form Method="GET" >
                                                                              <xsl:attribute name="action"><xsl:value-of select="$Url"/></xsl:attribute>
                                                                              <xsl:call-template name="hidden_parameters">              
                                                                              <xsl:with-param name="startrow" select="$startrow"/>
                                                                              <xsl:with-param name="displaycount" select="$displaycount"/>
                                                                              </xsl:call-template>            
                                                                              <input type="hidden" name="S">
                                                                                    <xsl:attribute name="value"><xsl:value-of select="number($startrow)-number($displaycount)"/></xsl:attribute>
                                                                              </input>
                                                
                                                                              <input type="submit" class="formbtn2" >
                                                                                    <xsl:attribute name="value"><xsl:text>Display Previous </xsl:text><xsl:value-of select="$displaycount"/><xsl:text> listings</xsl:text></xsl:attribute>
                                                                              </input>
                                                                  </form>
                                          </xsl:otherwise>
                                    </xsl:choose>
                            </xsl:when>
                            
                              
                              <xsl:otherwise>
                                                      <form Method="GET" >
                                                                              <xsl:attribute name="action"><xsl:value-of select="$Url"/></xsl:attribute>
                                                                              <xsl:call-template name="hidden_parameters">              
                                                                              <xsl:with-param name="startrow" select="$startrow"/>
                                                                              <xsl:with-param name="displaycount" select="$displaycount"/>
                                                                              </xsl:call-template>            
                                                                              <input type="hidden" name="S">
                                                                                    <xsl:attribute name="value"><xsl:value-of select="number($startrow)+number($displaycount)"/></xsl:attribute>
                                                                              </input>
                                                
                                                                              <input type="submit" class="formbtn2" >
                                                                                    <xsl:attribute name="value"><xsl:text>Display next </xsl:text><xsl:value-of select="$displaycount"/><xsl:text> listings</xsl:text></xsl:attribute>
                                                                              </input>
                                                                  </form>
                                                
                              </xsl:otherwise>
                        </xsl:choose>
                  </xsl:if>            
                     <p>
       
      </p>
                    
         
          </p>    
      

      </xsl:when>
      <xsl:otherwise>
          <!-- now records found, display a message-->
          <br></br>
              <br></br>
              <p><b><font color="red">Sorry, no listings match your Search Criteria. Please try again.</font></b></p>
    </xsl:otherwise>
 </xsl:choose>      
     <form method="GET" ID="Form5">
         <xsl:attribute name="action"><xsl:value-of select="$newSearchURL"/></xsl:attribute>
         <xsl:call-template name="hidden_parameters">
         <xsl:with-param name="startrow" select="$startrow"/>        
         <xsl:with-param name="displaycount" select="$displaycount"/>
         </xsl:call-template>    
          <input TYPE="submit" VALUE="New search" class="formbtn2" ID="Submit4" NAME="Submit4"/>
      </form>
</xsl:template>

<xsl:template name="hidden_parameters">
  <xsl:param name="startrow"/>
  <xsl:param name="displaycount"/>
 
  <input type="hidden" name="FC">
    <xsl:attribute name="value"><xsl:value-of select="$FC"/></xsl:attribute>
   </input>
   <input type="hidden" name="lg">  
      <xsl:attribute name="value"><xsl:value-of select="$lon"/></xsl:attribute>
   </input>

   <input type="hidden" name="lt">  
      <xsl:attribute name="value"><xsl:value-of select="$lat"/></xsl:attribute>
   </input>
             
      
</xsl:template>
</xsl:stylesheet>


 
0
 
dfiala13Commented:
XSLT is case sensitive.  You are creating a Root element and searching for a root element.  Change your code here

xmlRoot = xmlDoc.CreateElement("Root")

to

xmlRoot = xmlDoc.CreateElement("root")

if that doesn't fix it all, post your XML too
0
 
jmamerAuthor Commented:
I just ran it, and it was not fixed.  Just out of curiousity, does this statement make sense to you?  I have a sneaking suspicion that the problem is here:
While Not xmlReader.EOF
            xmlRoot.InnerXml = xmlRoot.InnerXml & xmlReader.ReadOuterXml()
End While

Anyhow, here is an example of the XML returned by SQLServer.  Keep in mind that this worked perfectly in the original ASP application (where, you are right, the root node I added did not have a capital "R")

<WebSessionProLookup RowNumber="1" ProvName="OHSU Medical Group-Pediatric Adolescent Medicine" Address1="3181 SW Sam Jackson Park Rd" City="Portland" State="OR" Zip="97239" Phone="5034185700" Distance="2.4" Longitude="-122.687012" Latitude="45.500000" Specialty="Adolescent Medicine" SpecCode="ADO" ProvID="D0000000004081971ADO"/>

Thanks for your help!!!
0
 
jmamerAuthor Commented:
Thank you!  Your post made me realize that in the original ASP code I had the following statement:
  srcXmlDoc.loadXML("<root><ProviderLocations>" & mystream.readtext & "</ProviderLocations></root>")

So I not only added a "root" node, I added a "ProviderLocations" node.

So I guess that my question is now, how can I add this second "ProviderLocations" node in the VB Syntax...

 xmlReader.MoveToContent()
        xmlRoot = xmlDoc.CreateElement("Root")
        xmlDoc.AppendChild(xmlRoot)
        While Not xmlReader.EOF
            xmlRoot.InnerXml = xmlRoot.InnerXml & xmlReader.ReadOuterXml()
        End While
        xmlReader.Close()
0
 
dfiala13Commented:
This should do it...
xmlProv = xmlDoc.CreateElement("ProviderLocations")
xmlRoot.AppendChild(xmlProv)
xmlDoc.AppendChild(xmlRoot)
 While Not xmlReader.EOF
            xmlProv.InnerXml = xmlProv.InnerXml & xmlReader.ReadOuterXml()
        End While
        xmlReader.Close()


0
 
jmamerAuthor Commented:
Thanks SOOOOOOOOOOOOOOOOOOO  much!!!!!!
0
 
dfiala13Commented:
You're welcome.

Have fun.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now