Share via

  • Article

Using an Updategram in a Sample ASP Application (SQLXML 4.0)

This Active Server Pages (ASP) application allows you to update customer information in the Person.Person table in the AdventureWorks2008R2 sample database in Microsoft SQL Server. The application does the following:

  • Asks the user to enter a business entity ID.

  • Uses this ID value to execute a template to retrieve contact information from the Person.Person table.

  • Displays this information by using an HTML form.

The user can then update contact information but not the business entity ID (because the BusinessEntityID is the primary key). After the user submits the information, an updategram is executed and all the form parameters are passed to the updategram.

The following template is the first template (GetPerson.xml). Save this template in the directory that is associated with the virtual name of template type.

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <sql:header>
      <sql:param name="bid"></sql:param>
   </sql:header>
   <sql:query>
      SELECT  * 
      FROM    Person.Person
      WHERE   BusinessEntityID=@bid 
      FOR XML AUTO
   </sql:query>
</root>

The following template is the second template (UpdatePerson.xml). Save this template in the directory that is associated with the virtual name of template type.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:header>
   <updg:param name="bid"/>
   <updg:param name="jobtitle" />
   <updg:param name="firstname" />
   <updg:param name="lastname" />
</updg:header>
<updg:sync >
   <updg:before>
      <Person.Person BusinessEntityID="$bid" /> 
   </updg:before>
   <updg:after>
      <Person.Person BusinessEntityID="$bid" 
       JobTitle="$jobtitle"
       FirstName="$firstname"
       LastName="$lastname"/>
   </updg:after>
</updg:sync>
</ROOT>

The following code is the ASP application (SampleASP.asp). Save it in the directory that is associated with a virtual root that you create by using the Internet Services Manager utility. (This virtual root is not created by using the IIS Virtual Directory Management for SQL Server utility because IIS Virtual Directory Management for SQL Server cannot access or identify ASP applications.).

Note

In the code, you must replace "ServerName" with the name of the server running Microsoft Internet Information Services (IIS).

<% LANGUAGE=VBSCRIPT %>
<%
  Dim BusinessEntityID
  BusinessEntityID=Request.Form("bid")
%>
<html>
<body>
<%
  'If a BusinessEntityID value is not yet provided, display this form.
  if BusinessEntityID="" then
%>
<!-- If the BusinessEntityID has not been specified, display the form that allows users to enter an ID. -->
<form action="AdventureWorksPeople.asp" method="POST">
<br>
Enter BusinessEntityID: <input type=text name="bid"><br>
<input type=submit value="Submit this ID" ><br><br>
<-- Otherwise, if a BusinessEntityID is entered, display the second part of the form where the user can change customer information. -->
<%
  else
%>
<form name="People" action="https://localhost/AdventureWorks2008R2/Template/UpdatePerson.xml" method="POST">
You may update customer information below.<br><br>
<!-- A comment goes here to separate the parts of the application or page. -->
<br>
<%
  ' Load the document in the parser and extract the values to populate the form.
    Set objXML=Server.CreateObject("MSXML2.DomDocument")
    ObjXML.setProperty "ServerHTTPRequest", TRUE

    objXML.async=False
    objXML.Load("https://localhost/AdventureWorks2008R2/Template/GetPerson.xml?bid=" & BusinessEntityID)
    set objCustomer=objXML.documentElement.childNodes.Item(0)

  ' In retrieving data from the database, if a value in the column is NULL there
  '  is no attribute for the corresponding element. In this case,
  ' skip the error generation and go to the next attribute.

  On Error Resume Next

  Response.Write "Business Entity ID: <input type=text readonly=true style='background-color:silver' name=bid value="""
  Response.Write objCustomer.attributes(0).value
  Response.Write """><br><br>"


  Response.Write "Job Title: <input type=text name=jobtitle value="""
  Response.Write objCustomer.attributes(1).value
  Response.Write """><br><br>"

  Response.Write "First Name: <input type=text name=firstname value="""
  Response.Write objCustomer.attributes(2).value
  Response.Write """><br>"

  Response.Write "Last Name: <input type=text name=lastname value="""
  Response.Write objCustomer.attributes(3).value
  Response.Write """><br><br>"

  set objCustomer=Nothing
  Set objXML=Nothing
%>
<input type="submit" value="Submit this change" ><br><br>
<input type=hidden name="contenttype" value="text/xml">
<input type=hidden name="eeid" value="<%=BusinessEntityID%>"><br><br>
<% end if %>

</form>
</body>
</html>