johna's blog

Custom error pages for ASP.NET Web Forms and Classic ASP in IIS 7 and 8.5

2019-02-20T12:00:00+10:00

I was recently doing some work on a website which has a mixture of older Classic ASP pages and ASP.NET Web Forms pages. The website is running on IIS 8.5 and .NET framework 4.x.

The website has some code that sent email notifications of any Classic ASP server errors, but nothing for .NET errors. It also has custom 404 and 500 error pages for Classic ASP but nothing for ASP.NET.

I attempted to create some custom error pages and email notification for .NET but ran into some troubles along the way.

Here's what you need to do and some of the things that can go wrong.

httpErrors element

This is under system.webServer in the web.config file and is used for non-aspx pages.

Here you can set your custom 404 page not found errors for Classic ASP and other non-aspx pages, like images or folders.

You can also set your custom error pages for 500 internal server errors.

My Classic ASP error email notification code is in the custom 500 error page (coded in Classic ASP of course). One problem I came across here was that Server.GetLastError was not reporting any error. I found that you must specify the 100 subStatusCode of the 500 error here to get the details of the error with Server.GetLastError.

Here's a sample of this section from my working web.config:

<system.webServer>
	<httpErrors errorMode="Custom">
		<remove statusCode="404" subStatusCode="-1" />
		<error statusCode="404" prefixLanguageFilePath="" path="/_Error404.aspx" responseMode="ExecuteURL" />
		<remove statusCode="500" subStatusCode="100" />
		<error statusCode="500" subStatusCode="100" prefixLanguageFilePath="" path="/_Error500.asp" responseMode="ExecuteURL" />
	</httpErrors>
</system.webServer>

Note that I use an aspx page for my custom 404 error page so I only need one for all 404 errors, but I have a asp page for my custom 500 error page so I can send email notifications.

customErrors element

This is under system.web in the web.config and is used for aspx pages.

Here's a sample from my working web.config:

<system.web>
	<customErrors mode="On" redirectMode="ResponseRewrite">
		<error statusCode="404" redirect="/_Error404.aspx" />
	</customErrors>
</system.web>

Note that I only trap for 404 errors for aspx page requests here.

Setting the redirectMode to ResponseRewrite means that the user does not get redirected to the custom error pages. However, one problem that I ran into with this setting is that my aspx custom 404 error page didn't show, I just saw an application error. The problem is that Session is not available if you use ResponseRewrite so you must not attempt to access the Session object in your custom error page.

Trapping 500 internal server errors for aspx pages

You could trap these errors also in the customErrors element but I chose to do this instead in the global.asax page.

I send my email error notification then use Server.Transfer to show the custom 500 error page, like this:

void Application_Error(object sender, EventArgs e)
{
	//error notification code here

	Server.Transfer("/_Error500.aspx");
}

Notes about custom 500 error pages

It should be obvious but your custom 500 error pages should not include any code that might cause an error itself, or at least it should be handled properly.

For this reason I generally use a very simple custom 500 error page with no database calls. I don't use my website's MasterPage as that usually has code for things like logged-in user, or shopping cart lookups that might result in errors.

If you're sending email notifications it's also a good idea to handle mail sending errors in case your mail server is down. That way the user still sees your friendly error message.

Custom 404 errors don't have this problem (except for the abovementioned Session object issue) so can be rich in content and have full functionality. In fact it's good practice to make your custom 404 error pages as helpful as possible: attempt to offer an alternate page that you think matches the user's original request; briefly explain what your website is all about; and, offer a next relevant step for the user so that hopefully they don't leave your site.

Sending email using Amazon SES with Classic ASP

2017-03-21T12:00:00+10:00

I recently needed to change a client's website to send emails using Amazon SES and encountered a few issues.

The client's code was using jMail COM component but I couldn't make this work, most likely because Amazon SES requires use of TLS and I could not find how to enable this in jMail, if it supports it at all.

So the solution was to change to CDOSYS, but this required some experimentation before it would work.

Some of the errors I received along the way were:

• The server rejected the sender address
• The server response was: 530 Authentication required
• 530 Must issue a STARTTLS command first

To use CDOSYS with Amazon SES you must specify a remote server, authenticate using basic authentication, use port 25, and use SSL (not TLS as I attempted).

This sample code worked for me. You will of course need to include your own Amazon SES SMTP login details, server name, and verified sender address.

Dim objMessage
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Your subject"
objMessage.From = "test@exampledomain.com"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "email-smtp.us-west-2.amazonaws.com" 'Change if using a different Amazon SES server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Your Amazon SES SMTP username, usually starts with AKI..."
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Your Amazon SES SMTP password"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
objMessage.Configuration.Fields.Update
objMessage.TextBody = "This is a test email"
objMessage.To = "success@simulator.amazonses.com"

On Error Resume Next
objMessage.Send

If Err.Number = 0 Then
	Response.Write("OK")
Else
	Response.Write("FAIL: " + Err.Description)
End If

Classic ASP class constructors with parameters

2016-06-08T12:00:00+10:00

Although probably no one cares about Classic ASP, except those who still need to support it, I was refreshing my memory today on how to use classes in Classic ASP and found a couple of options for simulating constructors with parameters.

We start with a simple class and add a default function that returns the current instance:

Class MyClass
     Private m_myvalue
 
     Public Default Function Init(myvalue)
         m_myvalue= myvalue
         
         Set Init = Me
     End Function
     
     Public Property Get MyValue 
         MyValue = m_myvalue
     End Property
End Class

Then we can create an instance of our class with a faux constructor, like this...

Set myobject = (New MyClass)(123)

...or, if you prefer, this...

Set myobject = New MyClass.Init(123)

Sources: www.visualbasicscript.com/Initializing-a-Class-with-parameters-m76239.aspx and brendangadd.blogspot.com.au/2011/06/constructors-in-asp-classic.html.

Classic ASP cookie with no name hacking attack causing error 80004005

2015-07-31T12:00:00+10:00

As of July 2015, many of my Classic ASP websites have started logging strange errors, either 80004005 or not reporting any error code at all.

After carefully looking through the HTTP headers for these requests I found a common link: they all had at least one cookie with no name.

It appears that Classic ASP can't handle this and will stop executing your script with an error whenever a cookie with no name exists and you attempt to access the cookies, either by Request.Cookies(), Response.Cookies() or Request().

These requests are obviously some type of attack on the website, and based on forum posts and Q&A website posts I have seen, many other sites have been attacked starting around the same time.

It could possibly be a denial of service attack. By causing these types of errors some websites may eventually stop working due to not closed or destroying certain objects.

Alternately it could be some sort of other attack not specifically for Classic ASP websites using cookie names normally used by common website platforms with values that may be intended to cause issues or gain access.

To avoid this problem I looked at several alternatives but the simplest one for me to implement was to add some code to check whether the cookies could be read and terminate the script if it they couldn't be read, using error trapping. As most of my Classic ASP websites have an "include" file that is used by all scripts this only needed to be added in one place.

On Error Resume Next
Request.Cookies("test")
If Err.Number <> 0 Then Response.End
On Error Goto 0

There are other interesting options including IIS URL rewrite rules and regular expressions. There has been some interesting discussion at forums.iis.net.

Classic ASP functions for database queries and SQL injection protection

2014-07-28T12:00:00+10:00

If writing database queries in SQL in Classic ASP for SQL Server, rather than using ADO or parameterised queries, you can use these functions to correctly format your data and protect against SQL injection.

Numeric fields

ForceNumeric ensures that only numeric values are passed.

Function ForceNumeric(sngValue)
	If IsNull(sngValue) Then
		ForceNumeric = 0
	ElseIf sngValue = "" Or Not IsNumeric(sngValue)  Then
		ForceNumeric = 0
	Else
		ForceNumeric = CSng(sngValue)
	End If
End Function

String fields

Quotes adds single quotes around the string, and escapes single quotes within the string.

Function Quotes(strValue)
	If IsNull(strValue) Then
		Quotes = "null"
	Else
		Quotes = "'" & Replace(strValue, "'", "''") & "'"
	End If
End Function

Date fields

FormatDateSql adds quotes and formats dates to a SQL friendly format (yyyy-mm-dd). If not a valid date or a null value then it returns "null".

FormatDateTimeSql does the same except it adds the time too, down to seconds.

Function FormatDateSql(datValue)
	If IsNull(datValue) Then 
		FormatDateSql = "Null"
	ElseIf datValue = "" Then
		FormatDateSql = "Null"
	ElseIf IsDate(datValue) Then
		FormatDateSql = Quotes(Year(datValue) & "-" & Right("0" & Month(datValue), 2) & "-" & Right("0" & Day(datValue), 2))
	Else
		FormatDateSql = "Null"
	End If
End Function

Function FormatDateTimeSql(datValue)
	If IsNull(datValue) Then 
		FormatDateTimeSql = "Null"
	ElseIf datValue = "" Then
		FormatDateTimeSql = "Null"
	ElseIf IsDate(datValue) Then
		FormatDateTimeSql = Quotes(Year(datValue) & "-" & Right("0" & Month(datValue), 2) & "-" & Right("0" & Day(datValue), 2) & " " & Right("0" & Hour(datValue), 2) & ":" & Right("0" & Minute(datValue), 2) & ":" & Right("0" & Second(datValue), 2))
	Else
		FormatDateTimeSql = "null"
	End If
End Function

Boolean fields

CBit returns 1 or 0 to represent true or false.

Function CBit(booleanvalue)
	If booleanvalue Then
		CBit = 1
	Else
		CBit = 0
	End If
End Function

How to share Session securely between Classic ASP and ASP.NET

2014-03-22T12:00:00+10:00

Looking at the Google search results, it seems that there has been plenty of demand to share Session values between Classic ASP and ASP.NET. This is usually a result of supporting legacy Classic ASP with new development in ASP.NET.

Most of the solutions I found either did not handle sharing securely or required modifications to the Classic ASP code (such as the top ranking Microsoft solution).

Deep into the search results I found what seems like a great solution but doesn't seem to have had much support in the form of comments and social shares.

Like most of the solutions, it uses a Classic ASP script to return requested session variables. But it does this securely by locking down this script to internal requests only (by IP address), and retrieving by a server-side HTTP request. As a server-side request would usually use the user Session of the web server itself rather than the user's Session, the author of this code cleverly retrieves the Classic ASP cookie and passes it in the request headers. A very clever solution.

The only improvement I could suggest is that the IP address could be faked so instead I would possibly suggest using a password protected folder or some other method of authentication.

I also found that when I tried to access the Classic ASP session using the HTTPS protocol it would not work. The only way I could get it to work was to change the IIS setting "New ID On Secure Connect" to false which allows the use of the same Session in both HTTP and HTTPS.

You can find the original solution at http://devproconnections.com/aspnet/share-session-state-between-asp-and-aspnet-apps.

I have used that concept and made my own version that allows you to get or set a Classic ASP Session variable from ASP.NET:

AspSession.cs

using System;
using System.IO;
using System.Net;
using System.Web;

public class AspSession
{
	public static object Get(string name)
	{
		HttpContext context = HttpContext.Current;
		object value = null;
		String[] cookies = context.Request.Cookies.AllKeys;
		for (int i = 0; i < cookies.Length; i++)
		{
			HttpCookie cookie = context.Request.Cookies[cookies[i]];
			if (cookie.Name.StartsWith("ASPSESSION"))
			{
				System.Uri uri = context.Request.Url;
				HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri.Scheme + "://" + uri.Host + ":" + uri.Port.ToString() + "/Services/AspSession.asp?mode=get&name=" + name);
				request.Headers.Add("Cookie: " + cookie.Name + "=" + cookie.Value);
				HttpWebResponse response = (HttpWebResponse)request.GetResponse();
				Stream responseStream = response.GetResponseStream();
				System.Text.Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
				StreamReader readStream = new StreamReader(responseStream, encode);
				value = readStream.ReadToEnd();
				response.Close();
				readStream.Close();
				break;
			}
		}
		return value;
	}

	public static void Set(string name, object value)
	{
		HttpContext context = HttpContext.Current;

		String[] cookies = context.Request.Cookies.AllKeys;

		for (int i = 0; i < cookies.Length; i++)
		{
			HttpCookie cookie = context.Request.Cookies[cookies[i]];

			if (cookie.Name.StartsWith("ASPSESSION"))
			{
				System.Uri uri = context.Request.Url;

				HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri.Scheme + "://" + uri.Host + ":" + uri.Port.ToString() + "/Services/LegacySession.asp?mode=set&name=" + context.Server.UrlEncode(name) + "&value=" + context.Server.UrlEncode(value.ToString()));
				request.Headers.Add("Cookie: " + cookie.Name + "=" + cookie.Value);
				HttpWebResponse response = (HttpWebResponse)request.GetResponse();
				Stream responseStream = response.GetResponseStream();
				break;
			}
		}
	}
}

/services/aspsession.asp

<%
Dim strMode, strName, strValue

If Request.ServerVariables("REMOTE_ADDR") = Request.ServerVariables("LOCAL_ADDR") Then
	strMode = Request.QueryString("mode")
	strName = Request.QueryString("name")
	If strMode = "get" Then
		Response.Write(Session(strName))
	ElseIf strMode = "set" Then
		strValue = Request.QueryString("value")
		Session(strName) = strValue
	End If
End If
%>

Update 22-Dec-2017: I've since found there is an issue with this script. Sometimes in Classic ASP there will be multiple session cookies for the same user. I suspect the last one is the one Classic ASP uses. The ASP.NET code needs to be modified to either use the last cookie or try each cookie until it finds one that returns the value. Further investigation and changes are necessary.

Review of ABCPDF.NET from webSupergoo

2014-03-05T12:00:00+10:00

I have been using ABCpdf for generating PDF documents in Classic ASP and ASP.NET for many years, and have always found it to be the simplest way to generate and manipulate PDF documents.

Not only can you create empty documents and add text and images wherever and however you like, but the feature I find most useful is conversion from HTML to PDF.

As an example, on one website I need to generate nice looking brochures from content from a database which changes frequently. I have created a protected ASPX page which retrieves the content from the database and displays it in HTML. I also have created some attractive PDF templates -- a different one for page one and for subsequent pages. Using ABCpdf I load the appropriate PDF template then overlay the HTML page. The result is exactly what I needed. ABCpdf also overlays page numbers and allows me to save the resulting PDF to the file system or send it straight to the browser as a dynamic PDF.

With my HTML and CSS I can also control things like how different parts of the content will break across pages, and ABCpdf understands and obeys this. About the only thing I found it wouldn't do were custom web fonts that weren't available on the server.

Let's face it, creating a PDF by placing items on different parts of a page is time-consuming and requires lots of tweaking along the way. If you're a web developer you know HTML and CSS and can probably produce results quicker working that way and converting to PDF using ABCpdf.

Of course if you have to generate PDFs by placing items on the page, ABCpdf does have a very useful feature where it will add a grid to the page during your development which really helps with positioning and adjustments.

I looked at other PDF components and found none that could do all of this so well, and quickly, and as easy. I know there are free PDF tools out there and ABCpdf is a comparatively a little pricey (starts at US$329), but for all the extra effort you would have to put in to get the free tools generating something like what you need, you could get ABCpdf doing the job quicker and better.

The other good thing about ABCpdf is that despite several version updates over the years, even the latest version is still backwards compatible so my old Classic ASP code and works side-by-side with newer .NET code.

You can download a free trial or purchase ABCpdf.NET from webSupergoo's website.

Classic ASP Master Pages

2010-03-27T12:00:00+10:00

When developing websites most of them have common elements like headers, footers and navigational menus that are common throughout the site. In Classic ASP we mostly use multiple server side includes to accomplish this. I like ASP.NET master pages and this page details the technique I use to achieve something similar in Classic ASP.

My technique involves creating a master page ASP file which has all the common elements. Where ever I want content to be added I add a call to a sub routine.

Then in my content pages when I want the master page content to be added I include it at that point. I then create a sub routine for each content area and put my content inside these. You can have as many content sub routines as required but they must exist in every content page that uses that master page.

The following example shows a master page and content page with two content areas: one inside the head tag (called HeadPlaceHolder in this example) so I can easily add title and meta tags, and add JavaScript and CSS within the head tag if required; and one inside the body tag between the header and footer (called ContentPlaceHolder in this example).

Any ASP variables that are needed by more than one content area should be created outside of the sub routines.

masperpage.asp

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<% Call HeadPlaceHolder() %>
	<link rel="stylesheet" type="text/css" href="stylesheet.css" />
	<script language="javascript" type="text/javascript" src="/java.js"></script>
</head>
<body>
		<div id="header">
			Logo etc etc
		</div>
		<div id="content">
			<% Call ContentPlaceHolder() %>
		</div>
		<div id="footer">
			Copyright etc etc
		</div>
</body>
</html>

content-page.asp


<!--#include virtual="masterpage.asp"-->

<%
'Any ASP variables that are needed by multiple contenbt areas should be declared
'outside of the sub routines
'It's a good practice to keep your ASP script at the beginning of your pages anyway
%>
<% Sub HeadPlaceHolder() %>

	<title>My page title</title>
	<meta content="My meta description" name="description" />

<% End Sub %>

<% Sub ContentPlaceHolder() %>

	<p>My page content goes here</p>

<% End Sub %>

CAPCTHAs and alternatives

2009-12-09T12:00:00+10:00

CAPTCHAs and their alternatives

The basic purpose of a CAPTCHA (Completely Automatic Public Turing Test to Tell Computers and Humans Apart) is to prevent robots from submitting web forms. By displaying an image that cannot be “read” by a computer, only a human can submit the form successfully.

Most of the time CAPTCHAs are an irritation to legitimate users, and can also cause issues with accessibility unless an alternative audible version of the text or numbers in the CAPTCHA image is also available.

My technique for CAPTCHA in Classic ASP is an image only system which works like this:

When the form is displayed two random numbers are generated. One is used as the number that is generated into an image. The other random number is used as the name of a session variable which contains the first number. This second number is stored as a hidden form field (it doesn't matter that this second number can be easily read). I use a random session variable name so as not to cause problems if the user has multiple forms open, this way each should submit without any conflict. On form submit the script gets the hidden field value and then the value of this session variable and compares this with the user's input. If they don't match the user (or robot) is redirected back to the form and two new random numbers are generated so the process repeats.

A more simple but less effective system that prevents some robots but avoids irritation to legitimate users and accessibility problems is to use a system similar to the CAPTCHA system described above where a random number is generated and added as a hidden form field and also stored in a session variable. On form post the hidden form field value is compared with the session variable value and succeeds only if they match. This means that the form can only be submitted if the user or robot visited the web form page first and posts the random number. This prevents robots that just simulate a form post to a URL from succeeding but doesn't stop the robot if they are willing to retrieve the web form page each time they post a form.

An improvement to this system would be to use a JavaScript script to add the value to the hidden field. Again, this would be fairly easy for a robot to simulate but would mean that the robot would have to be written specifically for the site.

Creating an image or audible CAPTCHA is not possible for some websites, for example in Classic ASP it usually requires a third party server component (although it can be done without one). There are other alternatives.

* Instead of creating a random number you could generate a random mathematical question, for example:

What is five plus twenty-three?

* You could create a database of random questions and answers, for example:

Which is not a colour? Blue, green, apple, orange or black?

* You could show several photos of different objects and ask the visitor to pick which picture contains a certain object.

All of these techniques are easy to produce using a system similar to what I described near the beginning of this article.

* You could create a textbox and set it to display:none in CSS. A robot is likely to fill this field so you can void any form submissions where this textbox is not empty.

* Dynamically change the names of form fields and store their new names in a session variable.

Obtaining Image Properties in ASP Without a Component

2009-12-09T12:00:00+10:00

You don't need a third party server component to determine the height and width of an image file in Classic ASP. The LoadPicture command will load an image into and object and you can then access the height and width, such as in this example:

Dim objImage
Set objImage = LoadPicture(Server.MapPath("image.ext")) 
intWidth = objImage.Width
intHeight = objImage.Height

4GuysFromRoll.com has an interesting article on resizing images without a component - see http://www.4guysfromrolla.com/webtech/050300-1.shtml.

The following link contains some other code that can access image properties as well as modify GIF images: http://www.u229.no/stuff/.

Lastly the following link is for a component-less CAPTCHA genertor which generates a BMP image from ASP.It may help you understand how to create and modify BMP files. See http://www.tipstricks.org/.

Classic ASP Title Case

2008-07-29T12:00:00+10:00

Need a function that capitalises the first letter of each word, as used in titles?

Unlike some title case functions, this one doesn't just look for spaces, but capitilises the first letter after each non-alpha character which means that hyphenated words and words after numbers are considered new words.

Function TitleCase(byVal strValue) Dim intChat, intLastChar, blnUCase strValue = LCase(strValue) For intChar = 1 To Len(strValue) blnUCase = False If intChar = 1 Then blnUCase = True Else intLastChar = Asc(Mid(strValue, intChar - 1, 1)) If Not ((intLastChar >= 97 And intLastChar <= 122) Or (intLastChar >= 65 And intLastChar <= 90)) Then blnUCase = True End If End If If blnUCase Then strValue = Left(strValue, intChar - 1) & UCase(Mid(strValue, intChar, 1)) & Mid(strValue, intChar + 1) End If Next TitleCase = strValue End Function

SQL Injection Protection - b.js

2008-06-30T12:00:00+10:00

There is a automated SQL injection attack doing the rounds at the moments which injects some html (<script src=http://www.domain.com/b.js></script>) into certain fields in all the tables in a database.

If you have been attacked don't feel bad as an Internet search of "b.js" reveals tens of thousands of hacked sites.

The attack cleverly appends a series of SQL commands onto your querystrings and if your code is unprotected, and you don't use Access databases, the commands may be passed on to your SQL server and the damage done.

Considering the damage that could be done by this sort of attack, I guess we are lucky that they chose only to append their little JavaScript.

However, this attack could render your website as "unsafe" in search engine results.

Reversing the Damage

We are also extremely fortunate that the changes can be easily reversed with a few changes of the attackers original SQL commands.

Simply execute the following to clean up the damage. If you have been attacked multiple times (ie. you have multiple script blocks appended to your SQL data) then you will need to execute the following script for each attack.

DECLARE @T varchar(255), @C varchar(255); DECLARE Table_Cursor CURSOR FOR SELECT a.name, b.name FROM sysobjects a, syscolumns b WHERE a.id = b.id AND a.xtype = 'u' AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC( 'update ['+@T+'] set ['+@C+'] = left( convert(varchar(8000), ['+@C+']), len(convert(varchar(8000), ['+@C+'])) - 6 - patindex(''%tpircs<%'', reverse(convert(varchar(8000), ['+@C+']))) ) where ['+@C+'] like ''%<script%</script>''' ); FETCH NEXT FROM Table_Cursor INTO @T, @C; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor;

Protecting against attacks

There are many methods out there to protect against this sort of attack.

The best method is to ensure that you protect every value that you pass to SQL. Strings should have a function to replace single quotes with two single quotes. Numbers should have a function that forces them to a numeric value.

The following function is a simple method which removes multiple inline SQL commands. If you issue multiple inline SQL commands in one go then obviously it will not be suitable but for everyone else it should stop any attack. This will not protect against all types of attacks however.

Parse your SQL command strings with the following syntax:

SQLCheck(sql-string-here)

Function SQLCheck(strCommand) Dim intChar Dim blnQuotes SQLCheck = strCommand blnQuotes = False For intChar = 1 To Len(strCommand) If Mid(strCommand, intChar, 1) = "'" Then If Not blnQuotes Then blnQuotes = True Else If intChar < Len(strCommand) Then If Mid(strCommand, intChar + 1, 1) = "'" Then intChar = intChar + 1 Else blnQuotes = False End If End If End If ElseIf Mid(strCommand, intChar, 1) = ";" Then If Not blnQuotes Then SQLCheck = Left(strCommand, intChar - 1) Exit For End If End If Next End Function

VBScript: Suggested Prefixes for Indicating the Data Type of a Variable

2008-05-03T12:00:00+10:00

Data Type	Prefix
ADO command	cmd
ADO connection	cnn
ADO field	fld
ADO parameter	prm
ADO recordset	rst
Boolean	bln
Byte	byt
Collection object	col
Currency	cur
Date-time	dtm
Double	dbl
Error	err
Integer	int
Long	lng
Object	obj
Single	sng
String	str
User-defined type	udt
Variant	vnt

Handling Boolean Fields in MS-SQL and MS-Access

2008-05-03T12:00:00+10:00

If you have migrated from Microsoft Access to SQL Server or MySql then you have probably encountered the differences with boolean values.

In Microsoft Access you can use true or false in queries, for example:

SELECT * FROM tablename WHERE booleanfield=TRUE
SELECT * FROM tablename WHERE booleanfield=FALSE

SQL Server/MySql require a different approach:

SELECT * FROM tablename WHERE booleanfield=1
SELECT * FROM tablename WHERE booleanfield=0

The Microsoft Access query will not work in SQL Server/MySql and the SQL Server/MySql query will not work in Access as it treats true as -1 not 1.

For a cross-platform solution you can use the following:

SELECT * FROM tablename WHERE booleanfield<>0
SELECT * FROM tablename WHERE booleanfield=0

VBScript Class to Send Mail With CDOSYS

2008-02-16T12:00:00+10:00

This is my first attempt at a VBScript class.

It allows full control of the CDO Message object.

You can create a new instance of the class with the following:

Dim clsSendMail
Set clsSendMail = New SendMail

Then add the various properties of the email:

clsSendMail.SendTo = "test@test.com"
clsSendMail.From = "test@test.com"
clsSendMail.Subject = "Test Message"

Next you choose what type of mail you are going to send: plain text, HTML or from a URL or local file.

Type	Property/Method	Syntax
Plain Text	TextBody	clsSendMail.TextBody = "message here"
HTML	HTMLBody	clsSendMail.HTMLBody = "<b>html</b> message here"
URL	CreateMHTMLBody	Call clsSendMail.CreateMHTMLBody "http://www.url.com/pagename.htm"
Local File	CreateMHTMLBody	Call clsSendMail.CreateMHTMLBody "file://c:/mydocuments/test.htm"

To add an attachment use the following:

Call clsSendMail.AddAttachment "c:\mydocuments\test.txt"

To embed files in your message use:

clsSendMail.AddRelatedBodyPart "/older/imagefile.gif", "image1.gif"

This can then be included in your HTMLBody by referencing the second parameter as the CID, for example:

<img src="cid:image1.gif">

You can also set a SMTP server and port if neccessary:

Call clsSendMail.SMTPServer "mail.test.com", 25

SendMail Class

Class SendMail

Private objCDOMessage
	Private objCDOBodyPart

Private strTo
	Private strFrom
	Private strCC
	Private strBCC
	Private strSubject
	Private strTextBody
	Private strHTMLBody

Private Sub Class_Initialize()
		Set objCDOMessage = CreateObject("CDO.Message")
	End Sub

Private Sub Class_Terminate()
		Set objCDOMessage = Nothing
	End Sub

Public Property Let SendTo(strParam1)
		strTo = strParam1
	End Property

Public Property Let From(strParam1)
		strFrom = strParam1
	End Property

Public Property Let CC(strParam1)
		strCC = strParam1
	End Property

Public Property Let BCC(strParam1)
		strBCC = strParam1
	End Property

Public Property Let Subject(strParam1)
		strSubject = strParam1
	End Property

Public Property Let TextBody(strParam1)
		strTextBody = strParam1
	End Property

Public Property Let HTMLBody(strParam1)
		strHTMLBody = strParam1
	End Property

Public Sub CreateMHTMLBody(strParam1)
		objCDOMessage.CreateMHTMLBody strParam1
	End Sub

Public Sub AddAttachment(strParam1)
		objCDOMessage.AddAttachment strParam1
	End Sub

Public Sub SMTPServer(strParam1, intParam2)
		objCDOMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
		objCDOMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strParam1
		objCDOMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = intParam2
		objCDOMessage.Configuration.Fields.Update
	End Sub

Public Sub AddRelatedBodyPart(strParam1, strParam2)
		Set objCDOBodyPart = objCDOMessage.AddRelatedBodyPart(Server.MapPath(strParam1), strParam2, 1)
		objCDOBodyPart.Fields.Item("urn:schemas:mailheader:Content-ID") = "<" & strParam2 & ">"
		objCDOBodyPart.Fields.Update
	End Sub

Public Sub Send()
		If strTo <> "" Then objCDOMessage.To = strTo
		If strFrom <> "" Then objCDOMessage.From = strFrom
		If strCC <> "" Then objCDOMessage.CC = strCC
		If strBCC <> "" Then objCDOMessage.BCC = strBCC
		If strSubject <> "" Then objCDOMessage.Subject = strSubject
		If strTextBody <> "" Then objCDOMessage.TextBody = strTextBody
		If strHTMLBody <> "" Then objCDOMessage.HTMLBody = strHTMLBody
		objCDOMessage.Send
	End Sub
End Class