Andrew Mooney

Using a Text Editor to Develop and Deploy an ASP.NET Web Application

andrewmooney — Mon, 15 Oct 2007 18:04:00 GMT

My latest article demonstrates how to use the freeware Crimson Editor to develop, compile, deploy (Xcopy), and launch an ASP.NET web application in a browser. You can read it at ASPAlliance.com: Using a Text Editor to Develop and Deploy an ASP.NET Web Application.

SQL Server 2005 Audit Log Using Triggers

andrewmooney — Mon, 20 Aug 2007 13:40:00 GMT

Once a database row has changed how do you retrieve the original data? There are many ways to do this, but for the application I'm working on I decided to use triggers.

Creating triggers in SQL Server 2005 is fairly easy. When you create a trigger you are presented with a template that you can modify to meet your needs. In the template there is a line that reads: SET NOCOUNT ON; with the explanation that tells you why: SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. it looks like this:

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;

When using triggers to update another table you need to leave this statement in or you will not be able to update your audit table, from triggers, and you will get the following error message:

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

Here are the SQL Statements required to create a table, an audit table, and the triggers :

USE [db]
GO

CREATE TABLE [dbo].[table1](
[table1_id] [int] IDENTITY(1,1) NOT NULL,
[table1_date] [datetime] NULL CONSTRAINT [DF_table1_table1_date] DEFAULT (getdate()),
[table1_data] [varchar](50) NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[table1_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[table1audit](
[table1audit_id] [int] IDENTITY(1,1) NOT NULL,
[table1audit_table1id] [int] NULL,
[table1audit_date] [datetime] NULL,
[table1audit_data] [varchar](50) NULL,
[table1audit_type] [varchar](50) NULL,
[table1audit_performed] [datetime] NULL CONSTRAINT [DF_table1audit_table1audit_date] DEFAULT (getdate()),
CONSTRAINT [PK_table1audit] PRIMARY KEY CLUSTERED
(
[table1audit_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TRIGGER dbo.table1auditdelete
ON dbo.table1
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    INSERT INTO table1audit(table1audit_table1id, table1audit_date, table1audit_data, table1audit_type)
    SELECT table1_id, table1_date, table1_data, 'DELETE'
    FROM deleted

END
GO

CREATE TRIGGER dbo.table1auditupdate
ON dbo.table1
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    INSERT INTO table1audit(table1audit_table1id, table1audit_date, table1audit_data, table1audit_type)
    SELECT table1_id, table1_date, table1_data, 'UPDATE'
    FROM inserted

END
GO

CREATE TRIGGER dbo.table1auditinsert
ON dbo.table1
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    INSERT INTO table1audit(table1audit_table1id, table1audit_date, table1audit_data, table1audit_type)
    SELECT table1_id, table1_date, table1_data, 'INSERT'
    FROM inserted

END
GO

How Can You Make The Column Headers Bold?

andrewmooney — Sat, 18 Aug 2007 21:21:00 GMT

The article Convert XML To an Excel Spreadsheet Using XSL explains how to transform an XML file into an Excel spreadsheet using XSL. The question is, how can you make the column headers bold? To view the original XSL file visit the article link above.

The answer is to add a Style to the XSL file and then set the ss:Style property for the Cells in the first row equal to the ss:ID property of the Style. See the XSL listed below. The bold font indicates the changes to the original XSL.

<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-scripts"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >

<xsl:template match="/">
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <xsl:apply-templates/>
</Workbook>
<Styles>
    <Style ss:ID="s21">
      <Font x:Family="Swiss" ss:Bold="1"/>
    </Style>
</Styles>
</xsl:template>

<xsl:template match="/*">
<Worksheet>
<xsl:attribute name="ss:Name">
<xsl:value-of select="local-name(/*/*)"/>
</xsl:attribute>
    <Table x:FullColumns="1" x:FullRows="1">
      <Row>
        <xsl:for-each select="*[position() = 1]/*">
          <Cell ss:StyleID="s21"><Data ss:Type="String">
          <xsl:value-of select="local-name()"/>
          </Data></Cell>
        </xsl:for-each>
      </Row>
      <xsl:apply-templates/>
    </Table>
</Worksheet>
</xsl:template>

<xsl:template match="/*/*">
<Row>
<xsl:apply-templates/>
</Row>
</xsl:template>

<xsl:template match="/*/*/*">
<Cell><Data ss:Type="String">
<xsl:value-of select="."/>
</Data></Cell>
</xsl:template>

</xsl:stylesheet>

Why not generate the XML file dynamically?

andrewmooney — Mon, 18 Jun 2007 11:46:00 GMT

WOW! I'm still getting feedback/questions on an article I wrote three ago Make Reports Quickly With ASP.NET and XML. Recently I received a question about generating the XML file dynamically. For this application it would be a waste of time and resources to do this. If you want to use a database, just keep the report parameters in a table. There's no need to generate an XML file.

The next step in this application is to add parameters for filtering the report data. By the way, I will publishing an article on this very soon. Now, generating links that contain these filter parameters makes a lot of sense. But, once the XML report file is created your report is done and there is no need to generate the XML report file every time a request is made.

Pardonner mon absence, s'il vous plaît. (Pardon my absence, please.)

andrewmooney — Fri, 26 Jan 2007 13:41:00 GMT

As the title suggests, I've absent for a while. However, I am back and I have a new article Display Local Weather Data On Your Sitethat was published yesterday (January 25, 2007) on ASPAlliance.com. The article demonstrates how to display current weather data (US only) on your web site.

How do I set the TransformArgumentList property of the Xml Control

andrewmooney — Mon, 23 May 2005 16:58:00 GMT

The Xml server control has a nice property that can only be set from code the TransformArgumentList. Here is an example of how to create an XsltArguementList, add a parameter named "Category" from the query string, and finally assign the XsltArguementList to Xml1 (an Xml server control):

<%@ Page language="c#" %>
<script runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
if(Request["cat"] != null)
{
System.Xml.Xsl.XsltArgumentList XsltArgumentList1 = new System.Xml.Xsl.XsltArgumentList();
XsltArgumentList1.AddParam("Category", "", Request["cat"].ToString());
Xml1.TransformArgumentList = XsltArgumentList1;
}
}
</script>
<html>
<body>
<asp:Xml id="Xml1" runat="server" DocumentSource="Document.xml" TransformSource="Style.xsl" />
</body>
</html>

Where Can I download the pubs.mdb?

andrewmooney — Fri, 20 May 2005 23:51:00 GMT

Developers looking for the pubs.mdb Microsoft Access database to test their applications can download this pubs.zip which contains both the Access 2000 and Access 2002 versions.

ASP.NET Web Printing Impersonation

andrewmooney — Mon, 16 May 2005 13:10:00 GMT

My article on Printing an ASP.NET Web Form When It Is Submitted mentions that you may have to add impersonation to your web.config file for Novell network printing. For the printer name use: “\\server\printer“.

Create a Novell user that has permissions to use the printer. Then change your web.config file to include the following:
<identity impersonate="true" userName="{DomainName\UserName}"
password="{UserPassword}"/>
<authentication mode="Windows" />

How to Add a Title to an Excel Spreadsheet From XSL

andrewmooney — Fri, 13 May 2005 14:00:00 GMT

The article Convert XML To an Excel Spreadsheet Using XSL explains how to transform an XML file into an Excel spreadsheet using XSL. The question is, how can you add a title to the Excel spreadsheet from XSL? To view the original XSL file visit the article link above.

First add an XSL parameter to the stylesheet immediately after the xsl:stylesheet declaration:
<xsl:param name="title" />

Second change this template in the XSL file:
<xsl:template match="/*">
<Worksheet>
<xsl:attribute name="ss:Name">
<xsl:value-of select="local-name(/*/*)"/>
</xsl:attribute>
    <Table x:FullColumns="1" x:FullRows="1">
      <Row>
          <Cell><Data ss:Type="String">
          <xsl:value-of select="$title"/>
          </Data></Cell>
      </Row>
      <Row>
        <xsl:for-each select="*[position() = 1]/*">
          <Cell><Data ss:Type="String">
          <xsl:value-of select="local-name()"/>
          </Data></Cell>
        </xsl:for-each>
      </Row>
      <xsl:apply-templates/>
    </Table>
</Worksheet>
</xsl:template>

Third use this web form:
<%@ Page Language="c#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Xml.Xsl" %>
<script language="C#" runat="server">
    public void Page_Load(Object sender, EventArgs E) {
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("Authors.xml"));
        XsltArgumentList xal = new XsltArgumentList();
        xal.AddParam("title", "", "Author List");
        XmlDataDocument xdd = new XmlDataDocument(ds);
        XslTransform xt = new XslTransform();
        xt.Load(Server.MapPath("Excel.xsl"));
        xt.Transform(xdd, xal, Response.OutputStream);
        Response.End();
      }
</script>

Alternate Table Row Colors with XSL

andrewmooney — Wed, 11 May 2005 11:17:00 GMT

This XSL file is from an article I wrote entitled: Displaying XML Files with ASP.NET 2.0. So, how do you alternate row colors? Compare the these two XSL files.

Original XSL file from the article:

<?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" omit-xml-declaration="yes"/>
<xsl:template match="/">
<table>
<tr bgcolor="#99aacc">
<th>Last Name</th>
<th>First Name</th>
<th>Phone</th>
</tr>
<xsl:for-each select="NewDataSet/authors">
<xsl:sort select="au_lname"/>
<tr bgcolor="#aaccff">
<td><xsl:value-of select="au_lname"/></td>
<td><xsl:value-of select="au_fname"/></td>
<td><xsl:value-of select="phone"/></td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>

Make this change to alternate row colors:

<xsl:if test="position() mod 2 = 1">
<tr bgcolor="#aaccff">
<td><xsl:value-of select="au_lname"/></td>
<td><xsl:value-of select="au_fname"/></td>
<td><xsl:value-of select="phone"/></td>
</tr>
</xsl:if>

<xsl:if test="position() mod 2 = 0">
<tr bgcolor="#ffccaa">
<td><xsl:value-of select="au_lname"/></td>
<td><xsl:value-of select="au_fname"/></td>
<td><xsl:value-of select="phone"/></td>
</tr>
</xsl:if>

</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>

Converting a SQL Server Web Application to Access?

andrewmooney — Wed, 11 May 2005 02:32:00 GMT

Over a year ago I wrote an article, Commerce Starter Kit For Access 2000, where I converted the Microsoft ASP.NET Commerce Starter Kit from SQL Server to an Access database. Since then I have recieved a lot questions on converting SQL Server web applications, including other Microsoft Starter Kits, to Access. When you consider just one difference between SQL Server and Access you start to get an idea of how difficult this can be. SQL Server stored procedures can accomplish multiple tasks and Access stored procedures (queries) can only accomplish one. This means that to convert some SQL Server stored procedures to Access will require multiple queries in the Access database and multiple calls to that database to accomplish the same thing. This is not desirable in most cases. I chose to convert the Commerce Starter Kit because the database structure and stored procedures were not as complex as the other Starter Kits.

However, I realize that some developers are only able to use Access. That is why I felt the need to write the article. For those wanting to convert a SQL Server Web Application to Access the best advice I have is don't. Instead write an application built for Access. If you still feel the need to convert, try to move some of the tasks in the stored procedures to ASP.NET code to limit the database activity.

If you need some Jet SQL help try these links:
http://devguru.com/Technologies/jetsql/quickref/jet_sql_list.html
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acfundsql.asp

AxpDataGrid for ASP.NET

andrewmooney — Mon, 09 May 2005 12:25:00 GMT

The AxpDataGrid from Axezz is much more than another web grid component. This control can automatically generate both forms and grids from OleDb data sources. If you haven't looked at this component lately, look again, you'll be pleasantly surprised.

Read the review

Visit AxpDataGrid Site

Visual Studio 2005 Standard Edition

andrewmooney — Fri, 22 Oct 2004 12:34:00 GMT

The announcement of Visual Studio 2005 Standard Edition proves that Microsoft is listening. Before this announcement, users of VS.NET 2003 Standard Edition had no upgrade path in VS.NET 2005. They could either move up to the Professional version or move down by combining a couple Express editions together. Now, there is a clear upgrade path.

http://lab.msdn.microsoft.com/vs2005/productinfo/productline/default.aspx

http://www.microsoft.com/presspass/press/2004/sep04/09-13VSLiveOrlandoPR.asp

Loading a dBase/Excel table into a GridView Control in ASP.NET 2.0

andrewmooney — Fri, 22 Oct 2004 11:50:00 GMT

To load a dBase table using the AccessDataSource control use the following:
DataFile="data;extended properties=dBase IV"
SelectCommand="select * from [ACCOUNTS.dbf]"

To load an Excel worksheet using the AccessDataSource control use the following:
DataFile="data\ACCOUNTS.xls;extended properties=Excel 8.0"
SelectCommand="select * from [ACCOUNTS$]"

XML Form Generator, Part 3: Displaying the Results

andrewmooney — Thu, 14 Oct 2004 17:41:00 GMT

Part 1 of this series demonstrated how to create an XML form using a DataGrid in a web page. Part 2 focused on how to display the XML form using XSL and then save the user input to a new XML file. This final part of the series will show how to create a web form that will list the saved XML results and allow viewing the data. XML Form Generator, Part 3: Displaying the Results.