访问数据库计数 [英] access database count
问题描述
嗨
我是一个完整的新手，所以希望有人可以了解我的
问题/目标。
我有一个访问数据库，里面有一些记录。我有两个字段
1）ArrivalDate 2）ReturnDate
我正在尝试写一个asp页面（非常难以理解）将会
显示特定日期有多少记录，即2008年7月23日。
数据库名为parking.mdb，该表名为mf_tbl。
数据库位于一个名为\db的文件夹中
以下是我在其他人的帮助下积累的内容，但没有
工作。
任何帮助都将不胜感激。
谢谢
克雷格
<％@ LANGUAGE =" VBSCRIPT"％>
<！DOCTYPE html PUBLIC" - // W3C // DTD XHTML 1.0过渡// EN" http：//
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<％
设置oConn = Server.CreateObject（" ADODB.Connection"）
oConn.Open（" PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source ="&
Server.MapPath（" db / parking.mdb"））
设置oRs = oConn.Execute（" SELECT Format（[ArrivalDate]，" dd / mm / yyyy"）
AS ARDate，Count（mf-tbl.ArrivalDate）AS CountOfArrival FROM mf-tbl
GROUP BY格式（[ArrivalDate]，" dd / mm / yyyy"）HAVING
（（（格式（[ArrivalDate]，" dd / mm / yyyy"））=＃23/07/2007＃））;"）
如果不是oRs.EOF那么
Response.Write"< table>
< tr>
rs.Fields中每个x的
<％
response.write（"< th>"& ucase（x.name） &"< / th>"）
next
％>
< / tr>
<％do，直到rs.EOF％>
< tr>
<％
每个x在rs.Fields
如果lcase（x .NAME）="客户id"那么％>
< td>
< input type =" hidden"名称= QUOT; ID" value ="<％= x.value％>">
< / td>
<％else％>
< td><％Response.Write（x.value）％>< / td>
<％end if
next
％>
<％rs.MoveNext％>
< / tr>
<％
循环
conn.close
％>
< / table>
Hi
I am a complete novice so hope someone can shed some light on my
problem/goal.
I have an access database with some records in it. i have two fields
1) ArrivalDate 2) ReturnDate
I am trying to write an asp page (with great difficutly) that will
show me how many records there are for a specific date i.e 23/07/2008.
The database is called parking.mdb, and the table is called mf_tbl.
The database resides in a folder called \db
Below is what i have mustered up with help from others, but does not
work at all.
Any help would be much appreciated.
Thanks
Craig
<%@LANGUAGE="VBSCRIPT"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("db/parking.mdb"))
Set oRs = oConn.Execute("SELECT Format([ArrivalDate],"dd/mm/yyyy")
AS ARDate, Count(mf-tbl.ArrivalDate) AS CountOfArrival FROM mf-tbl
GROUP BY Format([ArrivalDate],"dd/mm/yyyy") HAVING
(((Format([ArrivalDate],"dd/mm/yyyy"))=#23/07/2007#));")
If Not oRs.EOF Then
Response.Write "<table>
<tr>
<%
for each x in rs.Fields
response.write("<th>" & ucase(x.name) & "</th>")
next
%>
</tr>
<% do until rs.EOF %>
<tr>
<%
for each x in rs.Fields
if lcase(x.name)="customerid" then%>
<td>
<input type="hidden" name="ID" value="<%=x.value%>">
</td>
<%else%>
<td><%Response.Write(x.value)%></td>
<%end if
next
%>
<%rs.MoveNext%>
</tr>
<%
loop
conn.close
%>
</table>
推荐答案
写于2008年7月25日的microsoft.public.inetserver.asp.general：
wrote on 25 jul 2008 in microsoft.public.inetserver.asp.general:
>
设置oRs = oConn.Execute（" SELECT Format（[ArrivalDate]，" dd / mm / yyyy"）
AS ARDate，Count（mf-tbl.ArrivalDate）AS CountOfArrival FROM mf-tbl
GROUP BY格式（[ArrivalDate]，dd / mm / yyyy）HAVING
（（（格式（[ArrivalDate]，" dd / mm / yyyy"））=＃23/07/2007＃））;"）
>
Set oRs = oConn.Execute("SELECT Format([ArrivalDate],"dd/mm/yyyy")
AS ARDate, Count(mf-tbl.ArrivalDate) AS CountOfArrival FROM mf-tbl
GROUP BY Format([ArrivalDate],"dd/mm/yyyy") HAVING
(((Format([ArrivalDate],"dd/mm/yyyy"))=#23/07/2007#));")
you不需要所有的计数。
尝试：
<％
d =＃2007/07/23＃
设置oConn = Server.CreateObject（" ADODB.Connection"）
oConn .Open（" PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source =" & _
Server.MapPath（" db / parking.mdb"））
sql =" SELECT count（*）AS Ct FROM mf-tbl WHERE ArrivalDate =&& d&""
''resoponse.write sql''用于调试
''response.end
设置oRs = oConn.Execute（sql）
％>
Count =<％= oRs（" Ct"）％[on<％= d ％>]
-
Evertjan。
荷兰。
（请更改我的电子邮件地址中的点数为x''）
you do not need all that for a count.
Try:
<%
d = #2007/07/23#
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
Server.MapPath("db/parking.mdb"))
sql = "SELECT count(*) AS Ct FROM mf-tbl WHERE ArrivalDate = "& d &";"
'' resoponse.write sql ''for debugging
'' responde.end
Set oRs = oConn.Execute(sql)
%>
Count = <% = oRs("Ct") %[on <% = d %>]
--
Evertjan.
The Netherlands.
(Please change the x''es to dots in my emailaddress)
事情是，Access确实*不*了解日期的DD / MM / YYYY格式
附在＃...＃。
您可以使用#mm / dd / yyyy＃（美国标准）或＃yyyy / mm / dd＃（ISO
标准），这是Evertjan选择做的（以及我推荐的）。
但你也最好*不*做格式调用访问。相反，
在你的ASP代码中使用VBScript进行日期格式化。
************** *
此外，您的表名中还有一个减号。或者至少它看起来像是一个减去SQL的减号。所以你需要把这个名字放在一边。
***************
关于你的代码的另一个时髦的事情：你正在做
如果lcase（x.name）=" customerid"那么％>
< td>
< input type =" hidden"名称= QUOT; ID" value ="<％= x.value％>">
< / td>
<％else％>
但你甚至没有*尝试*来获得一个名为customerid的字段。那么IF测试的
点是什么？
*************
此外，因为你只能从该查询获得* ONE *记录，所以DO中的
点是什么？LOOP？
*************
所以... KISS：
< ;％
设置oConn = Server.CreateObject（" ADODB.Connection"）
oConn.Open（" PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source ="&
Server.MapPath（" db / parking.mdb"））
SQL =" SELECT COUNT（*）FROM [mf-tbl]在哪里ArrivalDate =＃2007/07/23＃"
''*或*如果你真的想要* TODAY *的到来，只需让Access这样做
给你：
SQL =" SELECT COUNT（*）FROM [mf-tbl] WHERE ArrivalDate = Date（）"
''* OR *来自昨天同样简单：
SQL =" SELECT COUNT（*）FROM [mf-tbl] WHERE ArrivalDate =（Date（） - 1）"
Set oRs = oConn .Execute（SQL）
''当你得到一个COUNT时你永远不会得到一个EOF
计数= oRS（0）
oRs.Close
oConn.Close
％>
到达人数是<％= count％>。
***************
不要输入不必要的代码。保持简短。
The thing is, Access does *NOT* understand the DD/MM/YYYY format for dates
enclosed in #...#.
You can either use #mm/dd/yyyy# (USA standard) or #yyyy/mm/dd# (ISO
standard), which is what Evertjan chose to do (and what I would recommend).
But you are ALSO better off *NOT* doing the Format call in Access. Instead,
use VBScript in you ASP code to do the formatting of the date.
***************
Also, your table name has a minus sign in it. Or at least it will look like
a minus sign to SQL. So you NEED to put [...] around the name.
***************
The other funky thing about your code: You are doing
if lcase(x.name)="customerid" then%>
<td>
<input type="hidden" name="ID" value="<%=x.value%>">
</td>
<%else%>
But you never even *TRY* to get a field named "customerid". So what''s the
point of that IF test?
*************
Further, because you will only get *ONE* record from that query, what''s the
poin in the DO ... LOOP?
*************
So...K.I.S.S.:
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("db/parking.mdb"))
SQL = "SELECT COUNT(*) FROM [mf-tbl] WHERE ArrivalDate = #2007/07/23#"
'' *OR* if you really want arrivals from *TODAY*, just let Access do it
for you:
SQL = "SELECT COUNT(*) FROM [mf-tbl] WHERE ArrivalDate = Date()"
'' *OR* arrivals from yesterday similarly simple:
SQL = "SELECT COUNT(*) FROM [mf-tbl] WHERE ArrivalDate = ( Date() - 1 )"
Set oRs = oConn.Execute( SQL )
'' you will never get an EOF when you are just getting a COUNT
count = oRS(0)
oRs.Close
oConn.Close
%>
The number of arrivals was <%=count%>.
***************
Don''t put in unnecessary code. Keep It Short and Simple.
<％
<%
d =＃2007/07/23＃
设置oConn = Server.CreateObject（" ADODB.Connection"）
oConn.Open（" PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source ="& _
Server.MapPath（" db / parking.mdb"））
sql =" SELECT count（*）AS Ct FROM mf- tbl WHERE ArrivalDate ="& d&""
''resoponse.write sql''用于调试
d = #2007/07/23#
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
Server.MapPath("db/parking.mdb"))
sql = "SELECT count(*) AS Ct FROM mf-tbl WHERE ArrivalDate = "& d &";"
'' resoponse.write sql ''for debugging
应该在那里留下调试代码！因为这段代码不会工作！
如果ASP脚本的Locale设置为大多数欧洲国家，那么
SQL的Response.Write会告诉你
SELECT count（*）AS Ct FROM mf-tbl WHERE ArrivalDate = 23/7/2008;
因为什么时候你这么做
d =＃2007/07/23＃
确实你确保正确的日期，2007年，7个月，23天。
* BUT * ...但是现在变量
d
是一个DateTime变量（或者你喜欢的Variant）。
现在，当你这样做时，你需要支付
SQL =" ..." &安培; d $ / $
VBScript必须将该DateTime值转换为* STRING *！根据当前的Session.LCID值，它确实如此。
。因此，在欧洲大部分地区，
值出现在字符串中
" 23/7/2007"
它确实*没有＃...围绕它需要访问!!!
所以实际发生的是
23/7 / 2007
被Access视为
23除以7除以2007
所以你得到一个非常小的数字
0.0016371
等于
1899年12月30日00:02:21
我很怀疑你会发现任何记录那个数据库
特定的日期和时间。
现在，如果你已编码
d ="＃2007/07 / 23＃"
它会起作用，但这并不是非常直观。
我，我创建一个用于Access查询的函数：
<％
功能YYYYMMDD（dt）
如果是IsDate（dt）那么
dt = CDate（dt）''只是为了确保
YYYYMMDD ="＃" &安培;年（dt）& " /" &安培;月（dt）& " /" &安培;日（dt）& "＃"
Else
YYYYMMDD =" NULL"
结束如果
结束函数
％>
然后我可以写
sql =" SELECT count（*）AS Ct FROM [mf-tbl ] WHERE ArrivalDate =" &
YYYYMMDD（d）
（由于错过了桌子的需要）
哦，查询结尾的分号确实是不必要的。不会因为b $ b伤害而受伤;没有帮助。
Should have left the debug code in there! Because this code will *NOT* work!
If the Locale of the ASP script is set to most European countries, that
Response.Write of the SQL would have shown you
SELECT count(*) AS Ct FROM mf-tbl WHERE ArrivalDate = 23/7/2008;
Because when you do
d = #2007/07/23#
indeed you ensure the correct date, 2007 year, 7 month, 23 day.
*BUT*... But now the variable
d
is a DateTime variable (or Variant, as you prefer).
And now, when you do
SQL = "..." & d
VBScript has to convert that DateTime value into a *STRING*! And it does so
according to the current Session.LCID value. So, in most of Europe, that
value comes out as the string
"23/7/2007"
And it does *NOT* have the #...# around it that Access requires!!!
So what actually happens is that
23/7/2007
is seen by Access as
23 divided by 7 divided by 2007
so you get a really really small number
0.0016371
which equates to
30 December 1899 00:02:21
and I seriously doubt you will find any records in the DB for that
particular date and time.
Now, if you had coded
d = "#2007/07/23#"
it would have worked, but that''s not terribly intuitive.
Me, I create a function for use with Access queries:
<%
Function YYYYMMDD( dt )
If IsDate(dt) Then
dt = CDate(dt) '' just to be sure
YYYYMMDD = "#" & Year(dt) & "/" & Month(dt) & "/" & Day(dt) & "#"
Else
YYYYMMDD = "NULL"
End If
End Function
%>
And then I can write
sql = "SELECT count(*) AS Ct FROM [mf-tbl] WHERE ArrivalDate=" &
YYYYMMDD(d)
(missed the need for [...] for the table, by the by)
Oh, and the semicolon on the end of the query is truly unnecessary. Won''t
hurt; doesn''t help.
这篇关于访问数据库计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
