XML —

Using the SQL ISAPI Extension to Update Data

You can also update data to a database using the SQL ISAPI extension. To perform an update you must create an update gram. An update gram is a template that is sent in an HTML query string. The general format of an update gram is shown below:

  <sql:sync xmlns:sql="urn:schemas-microsoft-com:xml-sql">
     <sql:before>
        <TABLENAME [sql:id="value"] col="value" col="value".../>
     </sql:before>
     <sql:after>
        <TABLENAME [sql:id="value"] [sql:at-identity="value"]
           col="value" col="value".../>
     </sql:after>
  </sql:sync>

Using this format, you can perform inserts, updates, and deletes. When performing an insert you would leave out the before element, when performing a delete you would leave out the after element, and when performing an update you would include both the before and after elements and list the columns that have changed.

For example, to add a new product to the Northwind Traders' Products table we could create the following HTML file called InsertProd.htm:

  <HTML>
  <SCRIPT>
     function InsertXML(ProdName, UInStock)
     {
     myTemplate = "http://localhost/northwind?template=" +
        "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" +
           "<sql:sync>" +
           "<sql:after>" +
           "<Products ProductName=\"" + ProdName +
           " UnitsInStock=\"" + UInStock + " \"/>" +
           "</sql:after>" +
           "</sql:sync>" +
           "<sql:query>select * from Products FOR XML AUTO " +
           "</sql:query>" +
        "</ROOT>";
     alert(myTemplate);
     document.location.href = myTemplate;
     }
  </SCRIPT>
  <BODY>
     Product Name:<INPUT type="text" id="ProductName" value="">
        <br></br>
     Units In Stock:<INPUT type="text" id="UnitInStock" value="">
        <br></br>
     <INPUT type="button" value="insert"
        OnClick="InsertXML(ProductName.value, UnitInStock.value);"/>
  </BODY>
  </HTML>

This HTML page uses a Java script function that builds the template. The backslash (/) is required for including a quote within a quote. You must place quotes around the new values or you will get an error. Unfortunately, there are some fields, such as money, which will not be accepted by SQL Server this way. These fields would need to be updated using a stored procedure as described below. Notice that we also included a sql:query element that is used to determine what is returned to the client. This will allow us to see whether the data was actually added to the database. Figure 15-6 shows what the HTML page and the query string look like.

Figure 15-6. The HTML page for updating with query string.

There are other more advanced features of the SQL ISAPI extension that extend beyond the level of this tutorial. If you are interested in these features, you can look at the documentation that comes with the SQL ISAPI extension.