BuildQuery - A Simple SQL Query Tool (Classic ASP version)
Skip Navigation LinksHome  >>  Projects  >>  Classic ASP  >>  BuildQuery - A Simple SQL Query Tool (Classic ASP version)

by Michael Bielski

Download the BuildQuery Class.

The BuildQuery class is used to make the creation of large or complex SQL INSERT or UPDATE queries faster and easier in Classic ASP pages using VBScript. The class will accept sets of data and output valid queries. There is also a method to flush the data from the internal storage so that the class may be used for any number of queries without repeatedly creating and destroying an object.

Methods
The Build and FlushAllData methods are used to do things to the data, and the Insert, Update, and Delete methods are used to do things with the data. Of the five methods, only the Build method has arguments.

Build The Build method of BuildQuery is how the query fields and their values get into the methods that do things with the data. The Build method has three arguments (all required) and is called as follows:

obj.Build inputfield, inputdata, inputquotes

inputfield - The name of the column in the database table that will be holding the data.
inputdata - The data you need to use in the database.
inputquotes - Must be either True (the data must be surrounded by quotes) or False (the data should not be surrounded by quotes.) The default is false. If another type of data is used, the default value is used.

Dim objBuildQuery
Set objBuildQuery = New BuildQuery
MyField = "Name"
MyData = "John Smith"
MyQuotes = True
 
objBuildQuery.Build MyField, MyData, MyQuotes

FlushAllData
The FlushAllData method of BuildQuery is used to clear out any values that have been stored with the Build method. The FlushAllData method is called as follows:

obj.FlushAllData
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.Table = "MyTable"
objBuildQuery.Build MyField, MyData, MyQuotes
strQuery = objBuildQuery.Insert
objBuildQuery.FlushData
'The object is now ready to work on a different set of data

Insert
The Insert method of BuildQuery is used to generate a valid SQL INSERT query that is ready for execution in the database. This method requires that the Table property be set and that at least one instance of the Build method has been successfully called. The Insert method called as follows:

obj.Insert
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.Table = "MyTable"
objBuildQuery.Build MyField, MyData, MyQuotes
strQuery = objBuildQuery.Insert

Update
The Update method of BuildQuery is used to generate a valid SQL UPDATE query that is ready for execution in the database. This method requires that the Table, IDField, IDValue, and IDQuotes properties be set and that at least one instance of the Build method has been successfully called. The Update method is called as follows:

obj.Update
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.Table = "MyTable"
objBuildQuery.IDField = "MyID"
objBuildQuery.IDValue = MyValue
objBuildQuery.IDQuotes = False
objBuildQuery.Build MyField, MyData, MyQuotes
strQuery = objBuildQuery.Update

Delete
The Delete method of BuildQuery is used to permanently remove one row from a specified table in the database. This method requires that the Table, IDField, IDValue, and IDQuotes properties be set. The Delete method is called as follows:

obj.Delete
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.Table = "MyTable"
objBuildQuery.IDField = "MyID"
objBuildQuery.IDValue = 7
objBuildQuery.IDQuotes = False
strQuery = objBuildQuery.Delete

Properties
All properties of the class are write-only.

Table
The Table property sets the table to be used during operations with the BuildQuery class. Until this property has a value, the class will not function properly. The Table property is set as follows:

obj.Table = value
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.Table = "MyTable"

IDField
The IDField property sets the column to be used in the WHERE clause during operations with the BuildQuery class. Until this property has a value, the class will not function properly. The IDField property is set as follows:

obj.IDField = value
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.IDField = "MyIDField"

IDValue
The IDValue property sets the criteria to be used on the right side of the WHERE clause during operations with the BuildQuery class. Until this property has a value, the class will not function properly. The IDValue property is set as follows:

obj.IDValue = value
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.IDValue = "7"

IDQuotes
The IDQuotes property determines whether the criteria to be used on the right side of the WHERE clause during operations with the BuildQuery class must be surrounded by quotes. The default value of this property is False. If another type of data is put into the property, the default value is used. The IDQuotes property is set as follows:

obj.IDValue = True or False
Example:
Dim objBuildQuery
Set objBuildQuery = New BuildQuery
objBuildQuery.IDQuotes = True

Addendum
This class could easily be modified to accept multiple ID fields for more complex UPDATE conditions. However, as Classic ASP is being phased out I doubt that it will be needed, and therefore have not included it.

This code and documentation is distrubuted under the W3C® SOFTWARE NOTICE AND LICENSE.

blog comments powered by Disqus