ASP: how to create a reusable function to query databases

Tweet

Today we are going to create something probably out of the ordinary. We will create two reusable functions (with parameters) in order to query a database.
The first function will open a recordset and execute a query. It will accept 3 parameters: recordset name, connection and query string.
The second function will close the recordset. It will accept 1 parameter: recordset name.

In order to use the parametrised functions, we will benefit from the Execute() VBScript function.
So, before building the two functions, let's try to understand Execute().

Execute()
The Execute() function can execute anything that is passed as parameter. It is more complicate to explain than to see it in action. Let's see an example:

<%
Dim StringVar
StringVar = "response.write 1"
Execute(StringVar)
%>

The output of the above is 1. The Execute() function is actually executing our response.write.
As a side note, consider that Execute() works like Eval()... but that's another story.
However, in order to have parameters for our two recordset functions, we are going to use Execute().

Connex

We are now building the connex function, which will open a recordset and perform a query. I want to explain it step by step so that everything is clear.

<%
function connex(rsn, conn, query)

The function will accept three parameters, as said:
1) rsn = the recordset name;
2) conn = the connection string;
3) query = the query string.

Dim openRS
openRS = "openRS" & rsn

We set a variable called openRS which will be our recordset name.
If we pass rsn as "Test", openRS will be "openRSTest" which will be a unique recordset name.

execute "Set " & openRS & " = Server.CreateObject(""ADODB.Recordset"")"

We use the Execute() function in order to create the server object (our recordset).
Please note the use of double quotes.

execute openRS & ".ActiveConnection = """ & conn & """"

Here we set the active connection.

execute openRS & ".Source = """ & query & """"

That is our query.

execute openRS & ".CursorType = 0"
execute openRS & ".CursorLocation = 2"
execute openRS & ".LockType = 1"
execute openRS & ".Open()"
execute openRS & "_numRows = 0"

Finally we set the cursor type, the location, lock type and we open the recordset. And then we set the number of rows to 0, because we might need to paginate results.

end function
%>

The above is the function end.
The complete function will look like:

<%
function connex(rsn, conn, query)
  Dim openRS
  openRS = "openRS" & rsn
  execute "Set " & openRS & " = Server.CreateObject(""ADODB.Recordset"")"
  execute openRS & ".ActiveConnection = """ & conn & """"
  execute openRS & ".CursorType = 0"
  execute openRS & ".CursorLocation = 2"
  execute openRS & ".LockType = 1"
  execute openRS & ".Open()"
  execute openRS & "_numRows = 0"
end function
%>

Connclose

The second function (connclose) is used to close the recordset. Again I will explain it step by step.

<%
function conclose(rsnc)

The function accepts one parameter: rsnc which is the name of the recordset to be closed.

Dim closeRS
closeRS = "openRS" & rsnc

In the above section we are going to build the closeRS variable which will be identical to the openRS variable seen in the connex function. We need to be sure to close the same recordset we opened before.

execute closeRS &".Close()"
execute closeRS &".ActiveConnection.close()"
execute "Set " & closeRS &".ActiveConnection = Nothing"
execute "Set " & closeRS &"= Nothing"

With the above code we close the recordset and the active connection, then we set the active connection and the recordset to nothing. Job done!

end function
%>

And that is the end of our function.
The complete code:

<%
function conclose(rsnc)
  Dim closeRS
  closeRS = "openRS" & rsnc
  execute closeRS &".Close()"
  execute closeRS &".ActiveConnection.close()"
  execute "Set " & closeRS &".ActiveConnection = Nothing"
  execute "Set " & closeRS &"= Nothing"
end function
%>

How to use the functions

The best way to implement the whole experiment is to use an inclusion. We put all the above code in an ASP page and call it connex.asp. Place the file where you prefer - in our example we will place it in a folder named "connections".
In the page where we want to use the functions, in the head of the document, we include the connex.asp file:

<!--#include virtual="/Connections/connex.asp" -->

At the beginning of the page, we set some variables:

<%
Dim rsn
Dim conn
Dim query
Dim rsnc
%>

Those are the functions parameters.
Wherever we need to call the connex function, we place the following snippet:

<%
conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/path-to/database.mdb")
query = "SELECT *  FROM table"
rsn = "table"
call connex(rsn, conn, query)
%>

The above is just an example, as you can see.
The result will be a recordset called "openRStable". So if we want to interact with the recordset, we need to use that name. For example when displaying results, we use:

<%
= openRStable.Fields.Item("TableField1").Value
%>

When we need to close the connection, we use the conclose function:

<%
rsnc = "table"
call conclose(rsnc)
%>

Easy, isn't it?

Possible advantages

With the use of parametrised functions, we do not need to write the same code more than once. If we create an asp file with the functions code, we can include it where we need it and call it when we need it.
Because we pass parameters to the functions, we might decide to create global variables, so that we do not need to pass the connection string every time, for example. The same for the other variables.
I think that the above exercise is quite a good starting point, but I'm sure it can be refined and improved. So, it's up to you!

If you need more information on the functions or you want to share your experience, please use the comments section below.
Happy coding.