Solved

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

Posted on 2004-04-13
10
339 Views
Last Modified: 2010-08-05
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
Comment
Question by:jmamer
  • 5
  • 5
10 Comments
 

Author Comment

by:jmamer
ID: 10818754
test
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10818793
Let's see your stylesheet.
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10818828
An example of the XML output would help as well.
0
 

Author Comment

by:jmamer
ID: 10818833
<?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
 
LVL 12

Expert Comment

by:dfiala13
ID: 10819749
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jmamer
ID: 10825199
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
 

Author Comment

by:jmamer
ID: 10825299
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
 
LVL 12

Accepted Solution

by:
dfiala13 earned 500 total points
ID: 10825558
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
 

Author Comment

by:jmamer
ID: 10825596
Thanks SOOOOOOOOOOOOOOOOOOO  much!!!!!!
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10825611
You're welcome.

Have fun.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now