年龄不返回当执行查询 [英] Age not Returned When Executing Query

问题描述

当我运行下面的code，因为它不返回年龄有什么可以在这里是什么问题？

 ＆LT;％计算员工的年龄
    BSQL =选择员工DATEDIFF（YYYY，出生日期，GETDATE（））
    设置BRS =的Server.CreateObject（ADODB.Recordset）
    brs.open BSQL，dbconn，1,2％GT;
＆LT;％= RS（出生日期）％＆GT;
 

解决方案

ar34z有<一个href=\"http://stackoverflow.com/questions/34174145/age-from-date-of-birth/34175252#comment56095174_34174145\">pointed出的意见;

  

的你没有选择列，而是创建一个新的的

一旦你创建了被称为一个的计算列或前pression的表添加任何计算到现场，指定 DATEOFBIRTH 作为一场名为不会工作，因为ADO不知道列 DATEOFBIRTH 只是一个无名计算列。

在理想情况下，你想使用别名以使计算列更容易认识到

SELECT DATEDIFF（YYYY，出生日期，GETDATE（））[DATEOFBIRTH]
FROM员工


或

SELECT DATEDIFF（YYYY，出生日期，GETDATE（））AS [DATEOFBIRTH]
FROM员工


都做工精细使用SQL Server，还从来没遇到过使用任一变化的任何问题。

由于计算列返回年龄的（而不是出生日期）的你可能只是别名列 [年龄] 然后用＆LT;％= RS（时代）％＆GT; 从它输出的 ADODB.Recordset

边注： 达米恩最异教徒有<一href=\"http://stackoverflow.com/questions/34174145/age-not-returned-when-executing-query#comment56098884_34174145\">mentioned在评论你的年龄的计算不是很准确有趣的是在这一点上有一个整体的其他问题已经在计算器上有一些有趣的答案的（只是不采取接受的答案是福音）的

• 如何基于出生和GETDATE（）
日期计算年龄（年）

由于它代表的（这只是我的个人意见）的这个答案（的http：//计算器.COM / A /六十九万二千九百四十二分之一百五十七万二千九百三十八）是最好的一群。

---

只是为了完整性，您还可以指示 ADODB.Recordset 来按序号位置返回字段，而再通过传递场位置的0基于指数在这种情况下，名称

 ＆LT;％= RS（0）％＆GT;
 

会工作，并在返回的第一个字段的 SELECT 。

---

考虑到这一切我建议的解决方案的（为眼前的问题）的会;

＆LT;％
计算员工的年龄
BSQL =SELECT DATEDIFF（YYYY，出生日期，GETDATE（））[年龄] FROM员工
设置BRS =的Server.CreateObject（ADODB.Recordset）
brs.open BSQL，dbconn，adOpenKeyset，ADLOCKPESSIMISTICRS的Response.Write（时代）
％GT;


您会注意到，我在替换数值brs.Open（） ADODB DLL常量来看看如何使用这些我建议你阅读的使用元数据导入DLL常量

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).

• How to calculate age (in years) based on Date of Birth and getDate()

As it stands (and this is only my personal opinion) this answer (http://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.

这篇关于年龄不返回当执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！