John's Technology Blog

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

<%
'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

Embed Images in CDOSYS messages

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

When sending HTML email messages using CDOSYS you can embed images as per the following sample:

Thumbnails Using ASPImage

2007-10-25T12:00:00+10:00

This script creates thumbnails for images using Server Object's ASPImage (see www.serverobjects.com).

You specify the image path and filename and the height and width required and this script will return the resized image.

The original aspect ratio of the image will allways be retained and whitespace will be added if neccessary to ensure that the dimensions are as requested. If there is any whitespace then you can choose to have the image centred or left/right aligned or top/bottom aligned.

Call the script using the following syntax:

<img src="thumbnail.asp?src=image.jpg&align=center&valign=center&height=100&width=75">

Where src is the path and filename (URL Encoded), align is the horizontal alignment (left, center or right, default is left), valign is the vertical alignment (top, center or bottom, default is top), height is the height in pixels, and width is the width in pixels.

thumbnail.asp

<%
'Get image filename
strImage = Request("src")
'Get request characteristics
If IsNumeric(Request("width")) Then
	intWidth = CInt(Request("width"))
Else
	intWidth = 0
End If
If intWidth = 0 Then intWidth = 100 'default size is 100x100
If IsNumeric(Request("height")) Then
	intHeight = CInt(Request("height"))
Else
	intHeight= 0
End If
If intHeight= 0 Then intHeight = intWidth
strAlign = LCase(Request("align"))
If strAlign = "" Then strAlign = "left" 'default align is left
strVAlign = LCase(Request("valign"))
If strVAlign = "" Then strVAlign = "top" 'default align is top
Set Image = Server.CreateObject("AspImage.Image")
Image.PadSize = 0
Image.LoadImage(Server.MapPath(strImage))
Image.ImageFormat = 1
Response.ContentType = "image/jpeg"
'Set your image quality here
Image.JPEGQuality = 30
intXSize = Image.MaxX
intYSize = Image.MaxY
If (intWidth / intXSize) > (intHeight / intYSize) Then
	intXSize = Int( (intHeight / Image.MaxY) * Image.MaxX )
	intYSize = intHeight
	Image.ResizeR intXSize, intYSize
	If strAlign = "center" Then
		intPadding = Int((intWidth - Image.MaxX) / 2)
		Image.CropImage -(intPadding), 1, Image.MaxX + intPadding, Image.MaxY
	ElseIf strAlign = "right" Then
		intPadding = Int(intWidth - Image.MaxX)
		Image.CropImage -(intPadding), 1, Image.MaxX + intPadding, Image.MaxY
	End If
	Image.MaxX = intWidth
Else
	intXSize = intWidth
	intYSize = (intWidth / Image.MaxX) * Image.MaxY
	Image.ResizeR intXSize, intYSize
	If strVAlign = "center" Then
		intPadding = Int((intHeight - Image.MaxY) / 2)
		Image.CropImage 1, -(intPadding), Image.MaxX, Image.MaxY + intPadding
	ElseIf strVAlign = "bottom" Then
		intPadding = Int(intHeight - Image.MaxY)
		Image.CropImage 1, -(intPadding), Image.MaxX, Image.MaxY + intPadding
	End If
	Image.MaxY = intHeight
End If
Response.BinaryWrite Image.Image
set Image = nothing
%>

Converting Numbers To Shorter/Easier to Convey Strings

2007-10-18T12:00:00+10:00

These functions convert a number into a shorter string containing both text and numbers.

Possible uses include where you may have a reference number that you don't want to look as if it is sequential and easy to pick, and/or if you want a refernce number that people could quote easier (because it is shorter).

Basically, it converts the number into a Base-34 number (similar to hexadecimal which is Base-16, or binary which is Base-2. Decimal is Base-10).

An advantage to this script, however, is that it doesn't use the letter 'I' or the letter 'O' so there can never been any confusion between them and the numbers one and zero.

To call the script simply use ToBase34(your-number) to convert to the string, then FromBase34(converted-string) to convert back to decimal.

Show sample

Determining a vistor's country or location

2007-10-09T12:00:00+10:00

There are several ways to determine a vistor's country through ASP.

The most reliable method is to look up the visitor's IP address and determine the country from that but that requires a large IP address database or call to another web site and there is usually a cost associated with these methods (31-Jan-08 update: see http://www.ourline.com/asp/findip.asp for a free script and database).

A simpler (and free) method is to try and determine the location based on the visitor's language settings.

The server variable 'HTTP_ACCEPT_LANGUAGE' will return a 2 or 5 character string with the visitor's language setting (assuming they have selected it correctly). You can then use this string to lookup what country(s) that language setting is associated with.

Whilst hardly a perfect solution, this method works quite reliably for determining major countries.

Here is a sample script that attempts to return a 2-digit country code based on this variable.

Run Sample

A better implementation of this method would be to have a lookup database which lists each of the possible language settings and looks up the most likely country.

The complete list of possible languages:

Afrikaans (af)
Albanian (sq)
Basque (eu)
Bulgarian (bg)
Byelorussian (be)
Catalan (ca)
Chinese (zh)
Chinese/China (zh-cn)
Chinese/Taiwan (zh-tw)
Chinese/Hong Kong (zh-hk)
Chinese/singapore (zh-sg)
Croatian (hr)
Czech (cs)
Danish (da)
Dutch (nl)
Dutch/Belgium (nl-be)
English (en)
English/United Kingdom (en-gb)
English/United Satates (en-us)
English/Australian (en-au)
English/Canada (en-ca)
English/New Zealand (en-nz)
English/Ireland (en-ie)
English/South Africa (en-za)
English/Jamaica (en-jm)
English/Belize (en-bz)
English/Trinidad (en-tt)
Estonian (et)
Faeroese (fo)
Farsi (fa)
Finnish (fi)
French (fr)
French/Belgium (fr-be)
French/France (fr-fr)
French/Switzerland (fr-ch)
French/Canada (fr-ca)
French/Luxembourg (fr-lu)
Gaelic (gd)
Galician (gl)
German (de)
German/Austria (de-at)
German/Germany (de-de)
German/Switzerland (de-ch)
German/Luxembourg (de-lu)
German/Liechtenstein (de-li)
Greek (el)
Hindi (hi)
Hungarian (hu)
Icelandic (is)
Indonesian (id or in)
Irish (ga)
Italian (it)
Italian/ Switzerland (it-ch)
Japanese (ja)
Korean (ko)
Latvian (lv)
Lithuanian (lt)
Macedonian (mk)
Malaysian (ms)
Maltese (mt)
Norwegian (no)
Polish (pl)
Portuguese (pt)
Portuguese/Brazil (pt-br)
Rhaeto-Romanic (rm)
Romanian (ro)
Romanian/Moldavia (ro-mo)
Russian (ru)
Russian /Moldavia (ru-mo)
Scots Gaelic (gd)
Serbian (sr)
Slovack (sk)
Slovenian (sl)
Sorbian (sb)
Spanish (es or es-do)
Spanish/Argentina (es-ar)
Spanish/Colombia (es-co)
Spanish/Mexico (es-mx)
Spanish/Spain (es-es)
Spanish/Guatemala (es-gt)
Spanish/Costa Rica (es-cr)
Spanish/Panama (es-pa)
Spanish/Venezuela (es-ve)
Spanish/Peru (es-pe)
Spanish/Ecuador (es-ec)
Spanish/Chile (es-cl)
Spanish/Uruguay (es-uy)
Spanish/Paraguay (es-py)
Spanish/Bolivia (es-bo)
Spanish/El salvador (es-sv)
Spanish/Honduras (es-hn)
Spanish/Nicaragua (es-ni)
Spanish/Puerto Rico (es-pr)
Sutu (sx)
Swedish (sv)
Swedish/Findland (sv-fi)
Thai (ts)
Tswana (tn)
Turkish (tr)
Ukrainian (uk)
Urdu (ur)
Vietnamese (vi)
Xshosa (xh)
Yiddish (ji)
Zulu (zu)

Nice Page Number Navigation for Recordsets

2007-10-03T12:00:00+10:00

The following script creates links to navigate through your database table recordset.

It is a solution to displaying navigation links where the number of pages may be quite large and there may not be enough room to display all page numbers.

Sometimes it can be frustrating for users if there is only a previous and next link but they want to get to a page half way through of 50 pages.

It creates links to the surrounding pages as well as the first or last page, as well as previous and next links.

A few examples:

1 2 3 4 5 6 7 8 9 10 11 ... 100 Next

Previous 1 ... 6 7 8 9 10 11 12 13 14 15 16 ... 100 Next

Previous 1 ... 90 91 92 93 94 95 96 97 98 99 100

The following variables and objects are used and may need to be changed for your application.

'lngPage' is the current page number and this is passed in the URL parameter 'page'.
'rsTable' is the recordset.
'page.asp' should be changed to the correct script name and any additional parameters added to the querystring.

If lngPage > 1 Then Response.Write("<a href=""page.asp?page=" & (lngPage - 1) & """>Previous</a> ")
If lngPage > 5 Then
	lngStart = lngPage - 5
	If lngPage < rsTable.PageCount - 5 Then
		lngEnd = lngPage + 5
	Else
		lngEnd = rsTable.PageCount
	End If
	If lngEnd - lngStart < 11 And lngStart > 1 Then
		lngStart = lngEnd - 10
		If lngStart < 1 Then lngStart = 1
	End If
Else
	lngStart = 1
	If lngPage < rsTable.PageCount - 5 Then
		lngEnd = lngPage + 5
	Else
		lngEnd = rsTable.PageCount
	End If
	If lngEnd - lngStart < 11 And lngEnd <> rsTable.PageCount Then
		lngEnd = lngStart + 10
		If lngEnd > rsTable.PageCount Then lngEnd = rsTable.PageCount
	End If
End If
If lngStart > 1 Then
	Response.Write("<a href=""page.asp?page=1"">1</a> ")
	If lngStart > 2 Then Response.Write("... ")
End If
For lngCounter = lngStart To lngEnd
	If lngCounter = lngPage Then
		Response.Write(lngCounter & " ")
	Else
		Response.Write("<a href=""page.asp?page=" & lngCounter & """>" & lngCounter & "</a> ")
	End if
Next
If lngEnd < rsTable.PageCount Then
	If lngEnd < rsTable.PageCount - 1 Then Response.Write("... ")
	Response.Write("<a href=""page.asp?page=" & rsTable.PageCount & """>" & rsTable.PageCount & "</a> ")
End If
If lngPage < rsTable.PageCount Then Response.Write("<a href=""page.asp?page=" & (lngPage + 1) & """>Next</a> ")

Show Sample

Complex Keyword Searches

2007-08-07T12:00:00+10:00

This article is about how you can generate complex SQL queries for searching database columns for keywords.

The examples assume that your keywords are in a variable called strKeywords.

First we need to remove multiple spaces.

Do While InStr(strKeywords, "  ") > 0
	strKeywords = Replace(strKeywords, "  ", " ")
Loop

The next step is to filter out any invalid characters. Only letters and numbers, spaces, quotes and hyphens are accepted.

strKeywords = LCase(strKeywords)
For lngChar = 1 To Len(strKeywords)
	If Not (Asc(Mid(strKeywords, lngChar, 1)) = 34 Or _
	  Asc(Mid(strKeywords, lngChar, 1)) = 45 Or _
	  (Asc(Mid(strKeywords, lngChar, 1)) >= 48 And _
	  Asc(Mid(strKeywords, lngChar, 1)) <= 57) Or _
	  (Asc(Mid(strKeywords, lngChar, 1)) >= 97 And _
	  Asc(Mid(strKeywords, lngChar, 1)) <= 122) Or _
	  Asc(Mid(strKeywords, lngChar, 1)) = 32 Or _
	  Asc(Mid(strKeywords, lngChar, 1)) = 46) Then
		strKeywords = Left(strKeywords, lngChar - 1) & _
		  " " & Mid(strKeywords, lngChar + 1)
	End If
Next

This next procedure is optional and can be used to lookup phrases in a user dictionary and change them. For example, if you wanted searches for the word "Landrover" to also find the words "Land" and "Rover" you could do this with this procedure. A database table is required for the dictionary called "Dictionary", with the columns (text) "Lookup" and "Replace" which contain the phrase to find and the phrase to replace with.

strKeywords = " " & strKeywords & " "
Set rsDictionary = Server.CreateObject("ADODB.Recordset")
rsDictionary.Open "SELECT * FROM Dictionary", database object
Do Until rsDictionary.Eof
	strLookup = " " & rsDictionary("Lookup") & " "
	If InStr(strKeywords, strLookup) > 0 Then
		strReplace = rsDictionary("Replace")
		strReplace = " " & strReplace & " "
		strKeywords = Replace(strKeywords, strLookup, _
		  strReplace)
	End If
	rsDictionary.MoveNext
Loop
rsDictionary.Close
Set rsDictionary = Nothing
strKeywords = Trim(Mid(strKeywords, 2))

This next procedure makes sure that phrases that are surrounded by quotation marks are treated as one and must match exactly.

intStart = InStr(strKeywords, """")
Do While intStart > 0
	If intStart = Len(strKeywords) Then Exit Do
	intEnd = InStr(intStart + 1, strKeywords, """")
	If intEnd = 0 Then Exit Do
	For intCount = intStart To intEnd
		If Mid(strKeywords, intCount, 1) = " " Then
			strKeywords = Left(strKeywords, intCount - 1) & _
			  "+" & Mid(strKeywords, intCount + 1)
		End If
	Next
	If intEnd = Len(strKeywords) Then Exit Do
	intStart = InStr(intEnd + 1, strKeywords, """")
Loop
strKeywords = Replace(strKeywords, """", "")

Now that all the manipulation of the keywords has been done we can create an array containing all the keywords of phrases if surrounded by quotation marks.

intWords = 0
intStart = 1
intEnd = 1
ReDim strWords(0)
Do While Len(strKeywords) >= intEnd
	If intStart > 1 Then intStart = InStr(intEnd, strKeywords, " ", 0)
	If intStart = 0 Then Exit Do
	intEnd = InStr(intStart + 1, strKeywords, " ", 0)
	If intEnd = 0 Then intEnd = Len(strKeywords) + 1
	intWords = intWords + 1
	redim preserve strWords(intWords)
	strWords(intWords) = Replace(LTrim(RTrim(Mid(strKeywords + " ", _
	  intStart, intEnd - intStart))), "+", " ")
	If intStart = 1 Then intStart = 2
Loop

Finally we can generate a SQL query to search for the keywords of phrases in one or more database columns.

strQuery = ""
If intWords <> 0 Then
	If strQuery <> "" Then strQuery = strQuery & " AND"
	strQuery = strQuery & " ("
	For intCounter = 1 To intWords

If you wish to be able to do searches based on finding ALL of the keywords or ANY of the keywords set the variable strAndOr accordingly on the following line (to AND for all, or OR for any).

		If intCounter > 1 And strQuery <> "" Then strQuery = _
		  strQuery & " " & strAndOr

The next line will vary depending on how many columns you want to search in. For one column it would be as follows:

		strQuery = strQuery & " (fieldname LIKE '%" & _
		  strWords(intCounter) & "%')"

But for multiple columns it would be:

		strQuery = strQuery & " (fieldname1 LIKE '%" & _
		  strWords(intCounter) & "%' OR fieldname2 LIKE '%" & _
		  strWords(intCounter) & "%' OR fieldname3 LIKE '%" & _
		  strWords(intCounter) & "%')"

Then we finish off our query.

	Next
	strQuery = strQuery & ")"
End If
strQuery = "SELECT * FROM tablename"
If strQuery <> "" Then strQuery = strQuery & " WHERE" & strQuery

Obviously you can add more search criteria and ORDER BY clauses as neccessary.

ASP Live Help

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

I developed this application for an on-line store. The code is quite messy because it took me lots of attempts to get right but it now operates almost perfectly.

Live Help is a messenger type application written mostly in ASP but with lots of JavaScript. It uses the ASP Application object to store information.

An operator (or multiple operators) can sign in then bring up a messenger type window which displays the status of conversations and requests. They enter their name which is shown to the other party during conversations.

Someone using the web site can see the status of Live Help - either available or not available. If available they can click the link to request a conversation with an operator.

To initiate the conversation they enter their name which is shown to the operator.

The operator(s) will hear a ringing sound to indicate an incoming conversation request. They can either accept the request, ignore it or decline it. If the request is ignored it will eventually expire and (or if cancelled, will occur immediately) the user will be prompted to try again later or leave a messge (which is delivered via normal email).

If the request is accepted a two-way conversation can take place in a messenger style environoment. The system displays messages from both the operator and user including the time of last message received and plays a sound to indicate a message received. The conversation can be ended by either party at any time. It will also display a message if the other party is typing a message.

Download the sample and give it a try on any ASP server.

Installation

Unzip the file into your root web folder. All files will then be unzipped into a foler called 'livehelp'. It will not operate correctly if in any other folder without modification.

The system will now work as is for demonstration/test purposes. If you wish to integrate into a web site then changes are neccessary.

'request.asp' contains an email address where any messages are sent if a user cannot contact Live Help. This should be changed to an email address of your choice. It may also require changes if CDOSYS is not available or if you need to specify a mail server.

'Default.asp' and 'user.asp' are required only for demonstration/testing and should be removed prior to proper use in your own web site. They do contain functions and procedures which will need to be used in your own scripts and pages.

You will need to incorporate the JavaScript functions and operator link contained in 'default.asp' into your own administration or web site back end. The operator pages should only be able to be accessed by authorised persons so you will need to implement your own security. At present these scripts check for a session variable 'admin' being true.

In any web page where you wish for users to be able to use Live Help from you will ned to include the JavaScript function and VB Script Sub contained within 'user.asp'. Then simply call 'LiveHelpStatus' where you would like the status image/link to be displayed.

Demonstration

Simply unzip the file in the root of your web or localhost web folder.

Open the 'default.asp' page.

Click sign in as operator and enter a name for yourself.

Then you can sign in as a user either on the same or another computer and enter a name for yourself to request a conversation.

You should hear a ringing noise and in your operator messenger window you can accept the request.

Conversation windows will then pop-up and you can converse as required.

Download Live Help (updated 16-Jul-2008)

License: Freeware

Updates

16-Nov-2007: There has been reports of the chat application not scrolling down automatically to show messages. 'chat.asp' has been updated which seems to have solved the problem.

20-Nov-2007: I have found the issue causing problems with Firefox. Download the following file for the fix. Unfortunately in Firefox it doesn't display "...is typing a message" or play sounds but at least now it works.

16-Jan-2008: I have fixed a few bugs including the non-functioning decline chat function. If you click the download Live Help link above you will receive the latest version of all parts of Live Help. Changes are to request.asp, getusers,asp, messenger.asp.

16-Jul-2008: I was advised that LiveHelp wasn't working with Safari so I have had to make some modifications for it to work. Click the download link above for the latest version.