Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-04-13
10
Medium Priority
?
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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
 

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 2000 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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

604 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