Recommended way to use "constants" in queries

[Augie Dawg]

I'd like to be able to use something a bit more self-documenting than
integers in my queries, and was wondering what other people do. The goal
would be to use code like:

SELECT * FROM Members
WHERE MemberTypeID = 1
OR MemberTypeID = 5

instead of

SELECT * FROM Members
WHERE MemberTypeID = PaidMember
OR MemberTypeID = HonoraryMember

My first thought was to produce an include file that contains VBScript to
assign these "constants" values, like the following:

<%
PaidMember = 1
HonoraryMember = 5
%>

Will this work, or is there a better way to do this?

Thanks,
A.D.

 

[Jim Buyens]

Typically you would code:

<%
Const PaidMember = 1
Const HonoraryMember = 5
Dim sql

sql = "SELECT * FROM Members " & _
"WHERE MemberTypeID = " & PaidMember & " " & _
"OR MemberTypeID = " & HonoraryMember & " "
%>

Of course, this is ASP code. I'm not sure you can do this
sort of thing within the Database Results Wizard.

For ASP.NET, I often create a SyntaxChecker class
instantiated as:

Dim synChk As New SyntaxChecker

Then, within SyntaxChecker, I use public constants to
define all magic numbers. This provides a single
definition for both syntax checking and code lookup.

The definition inside the SyntaxChecker class would be

Public Const PaidMember As Integer = 1

Other classes that instantiate SyntaxChecker can then
access the constant as synChk.PaidMember

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------