ADODB: How to query a database (the command and recordset objects)

Tweet

In almost every article on The web thought, we are talking about retrieving data from a database. I recently realized that we've never really talked about how to do it. I don't want to get into it in a too technical way, however I would like to share the two ways I use to get data from a database using the command object and the recordset object. We will concentrate only on data collection, while manipulation (update, insert and delete) could be the topic for future posts.

Ok, let's begin then.

The recordset object

Getting data from a database (whichever that db might be) with the recordset object is the most used method, while, when we need to manipulate data, the command object is preferred by developers. That's a general opinion, however using either the command object or the recordset object produces the same effect.
Moreove, there's a quite widespread idea that there might be a performance difference between the two methods, but in my experience, it's minimal (if any).
To retrieve data using the recordset object, we use the following code:

<%
Dim RS
Dim RS_numRows

Set RS= Server.CreateObject("ADODB.Recordset")

RS.ActiveConnection = "Driver={SQL Server};Server=ServerName\DataBaseName;Database=DataBase;Uid=UserID;Pwd=Password;"

RS.Source = "SELECT * FROM Table"

RS.CursorType = 0
RS.CursorLocation = 2
RS.LockType = 1
RS.Open()
RS_numRows = 0
%>

I believe that the only particular section of the above code is the connection string. In the example we are connecting to a SQL Server database, but we can connect to any type of database. For a reference to all the possible connection strings, please refer to this page.
With the above code we have opened the recordset. We can then show the result with a repeat region, for example displaying our data in a tabular way. In any case, it is very important to close the recordset at the end of all operations. Usually the last lines of code in our page will be destined to do that:

<%
RS.Close()
Set RS= Nothing
%>

And that is all.

The command object

The command object is very similar to the recordset object:

<%
Dim CMD
Dim CMD_cmd
Dim CMD_numRows

Set CMD_cmd = Server.CreateObject ("ADODB.Command")

CMD_cmd.ActiveConnection = "Driver={SQL Server};Server=ServerName\DataBaseName;Database=DataBase;Uid=UserID;Pwd=Password;"

CMD_cmd.CommandText = "SELECT * FROM Table WHERE id = ?"

CMD_cmd.Prepared = true
CMD_cmd.Parameters.Append CMD_cmd.CreateParameter("param1", 5, 1, -1, Request.QueryString("id"))

Set CMD = CMD_cmd.Execute
CMD_numRows = 0
%>

In the above example I added a parameter to the query (WHERE id = ?) and I used the .Append .CreateParameter to use the parameter in the query.
The syntax of the .CreateParameter method is

commandobject.CreateParameter (Name, Type, Direction, Size, Value)

That's a big advantage of the command object. In fact, people prefer to use the command object because of its flexibility and the possibility to use it to run stored procedures. At the same time, the use of parameters help us in creating appropriate update, insert and delete queries.

The command object releases the connection when the command object goes out of scope at the end of the page. So basically we do not need to close anything, but I suggest to do it anyway (it seems to free up memory and - in any case - keeps things tidy):

<%
CMD.Close()
Set CMD= Nothing
%>

That's all for today. Happy coding to all!