What could be the problem here when I run the following code as it does not return the age?
<% ' calculate age of employee
bsql = "select DATEDIFF(yyyy,DateOfBirth,GETDATE()) from employees "
set brs = Server.CreateObject("ADODB.Recordset")
brs.open bsql, dbconn, 1,2%>
<%=rs("DateOfBirth") %>
As @ar34z has pointed out in the comments;
"you are not selecting the column, but are creating a new one"
Once you add any computation to a field in a table you are creating what is known as a "Computed Column" or "Expression", specifying DateOfBirth as a named field isn't going to work because ADO doesn't know about the column DateOfBirth just a unnamed computed column.
Ideally you want to use alias to make computed columns easier to recognise
SELECT DATEDIFF(yyyy, DateOfBirth, GETDATE()) [DateOfBirth]
FROM employees
or
SELECT DATEDIFF(yyyy, DateOfBirth, GETDATE()) AS [DateOfBirth]
FROM employees
both work fine using SQL Server and have never encountered any issues using either variation.
As the computed column is returning the age (not the Date Of Birth) you could just alias the column [Age] then use <%= rs("Age") %> to output it from the ADODB.Recordset.
Side-Note: As @damien-the-unbeliever has mentioned in the comments your Age computation isn't very accurate interestingly on this point there is a whole other question already on StackOverFlow with some interesting answers (just don't take the accepted answer as gospel).
As it stands (and this is only my personal opinion) this answer (https://stackoverflow.com/a/1572938/692942) is the best of the bunch.
Just for completeness you can also instruct the ADODB.Recordset to return fields by ordinal position rather then name by passing a 0 based index of the field position in this case
<%= rs(0) %>
would work and return the first field in the SELECT.
Taking all this into account my suggested solution (for the immediate problem) would be;
<%
' calculate age of employee
bsql = "SELECT DATEDIFF(yyyy, DateOfBirth, GETDATE()) [Age] FROM employees"
Set brs = Server.CreateObject("ADODB.Recordset")
brs.open bsql, dbconn, adOpenKeyset, adLockPessimistic
Response.Write rs("Age")
%>
You will notice that I've replaced numeric values in brs.Open() ADODB DLL Constants to see how to use these I recommend reading Using METADATA to Import DLL Constants.
