By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,544 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,544 IT Pros & Developers. It's quick & easy.

access database count

P: n/a
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>

Jul 25 '08 #1
Share this Question
Share on Google+
13 Replies


P: n/a
wrote on 25 jul 2008 in microsoft.public.inetserver.asp.general:
>
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 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)
Jul 25 '08 #2

P: n/a
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.
Jul 25 '08 #3

P: n/a
<%
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
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.


Jul 25 '08 #4

P: n/a
Old Pedant wrote:
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.
.... or use parameters ... :-)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 26 '08 #5

P: n/a
"Bob Barrows [MVP]" wrote:
.... or use parameters ... :-)
I'd say "that goes without saying" except it doesn't, does it? All we can
do is repeat it, like a mantra.

Passing in date values to Access doesn't make me really nervous, though. If
you ensure you have #...# around the date value and you've done a CDATE() to
ensure it *IS* a data value, you should be safe. Not that Access is too
vulnerable to attack, in any case, since it doesn't support multiple SQL
statements in a request.

But... Yep, it's the principle of the thing.

Heh...Evertjan wouldn't have made the mistake he did if he'd used a
parameter, come to think of it. So there's another good reason to use them!
Jul 26 '08 #6

P: n/a
Have you ever used the Java PreparedStatement class?

I wish ADO had used something as simple as it.

Goes something like this:

String SQL = "INSERT INTO sometable ( id, name, image ) VALUES(?,?,?)"
PreparedStatement ps = conn.prepareStatement(SQL);
ps.setInt(1, id);
ps.setString(2, name);
ps.setBlob(3,imageBlob);
ps.execute( ); // yes, returns a recordset if query is appropriate

*SO* much easier than having to get all that gobbledy gook with
ADODB.Parameter objects correct. Granted, it's not all-powerful (and there
are of course other ways to do this in Java), but it's nearly perfect for
working with simple DBs and simple queries, such as you'd use with Access.
Jul 26 '08 #7

P: n/a
Old Pedant wrote:
Have you ever used the Java PreparedStatement class?

I wish ADO had used something as simple as it.

Goes something like this:

String SQL = "INSERT INTO sometable ( id, name, image )
VALUES(?,?,?)" PreparedStatement ps = conn.prepareStatement(SQL);
ps.setInt(1, id);
ps.setString(2, name);
ps.setBlob(3,imageBlob);
ps.execute( ); // yes, returns a recordset if query is appropriate

*SO* much easier than having to get all that gobbledy gook with
ADODB.Parameter objects correct. Granted, it's not all-powerful (and
there are of course other ways to do this in Java), but it's nearly
perfect for working with simple DBs and simple queries, such as you'd
use with Access.
I would do nearly the same, except in vbscript I would use a variant array
to pass the parameter values rather than working through the Parameters
collection. The only time I mess with the parameters collection is when I'm
executing a procedure with output parameters or I need to read the value of
the return parameter. Otherwise, I pass a variant array via the second
argument of the Command's Execute method.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 26 '08 #8

P: n/a
I don't know why I keep forgetting about that method!

I *do* remember you can use the array with AddNew, and that's actually just
as efficient (provide you created the recordset using a query that didn't
actually return any records).

But people like you have to keep banging me over the head to make me
remember the array with Execute. DOH.

The advantage of the Java PreparedStatement is that the driver doesn't have
to go fetch the field info before doing the INSERT, to ensure that each data
item can indeed be converted to the right type. I'm assuming with
ADODB.Command and the array that it has to go find all the data types, so it
can coerce the variants to the right DB types. Still, that's a minor matter
in the scheme of things.

"Bob Barrows [MVP]" wrote:
Old Pedant wrote:
Have you ever used the Java PreparedStatement class?

I wish ADO had used something as simple as it.

Goes something like this:

String SQL = "INSERT INTO sometable ( id, name, image )
VALUES(?,?,?)" PreparedStatement ps = conn.prepareStatement(SQL);
ps.setInt(1, id);
ps.setString(2, name);
ps.setBlob(3,imageBlob);
ps.execute( ); // yes, returns a recordset if query is appropriate

*SO* much easier than having to get all that gobbledy gook with
ADODB.Parameter objects correct. Granted, it's not all-powerful (and
there are of course other ways to do this in Java), but it's nearly
perfect for working with simple DBs and simple queries, such as you'd
use with Access.

I would do nearly the same, except in vbscript I would use a variant array
to pass the parameter values rather than working through the Parameters
collection. The only time I mess with the parameters collection is when I'm
executing a procedure with output parameters or I need to read the value of
the return parameter. Otherwise, I pass a variant array via the second
argument of the Command's Execute method.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 26 '08 #9

P: n/a
=?Utf-8?B?T2xkIFBlZGFudA==?= wrote on 26 jul 2008 in
microsoft.public.inetserver.asp.general:
Oh, and the semicolon on the end of the query is truly unnecessary.
Won't hurt; doesn't help.
Traditionnnnn !

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Jul 26 '08 #10

P: n/a
Old Pedant wrote:
I don't know why I keep forgetting about that method!

I *do* remember you can use the array with AddNew, and that's
actually just as efficient (provide you created the recordset using a
query that didn't actually return any records).

But people like you have to keep banging me over the head to make me
remember the array with Execute. DOH.

The advantage of the Java PreparedStatement is that the driver
doesn't have to go fetch the field info before doing the INSERT, to
ensure that each data item can indeed be converted to the right type.
I'm assuming with ADODB.Command and the array that it has to go find
all the data types, so it can coerce the variants to the right DB
types. Still, that's a minor matter in the scheme of things.
This is unlikely to be satisfactory for you: neither the docs nor the two
ADO books I have expalin what ADO does behind the scenes here. The Execute
Method topic makes no mention of what ADO does internally to create and
populate the implicitly-created Parameters collection.

I'm assuming it does the same thing it does when a developer uses the
procedure-as-connection-method technique to execute stored procedures:

***********************************
To execute a stored procedure, issue a statement where the stored procedure
name is used as if it were a method on the Connection object, followed by
any parameters. ADO will make a "best guess" of parameter types.
***********************************
I've never run a trace against Access, but I have with SQL Server, and I
have never detected any extra database activity when using a variant array
to pass parameter values. Of course, I was never specifically looking for
such activity and I may have missed it.

The "best guess" wording does scare many people away from using either of
these techniques: what if ADO guesses wrong?? So far, I've had no bad
results ... :-)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 26 '08 #11

P: n/a
"Bob Barrows [MVP]" wrote:
I'm assuming it does the same thing it does when a developer uses the
procedure-as-connection-method technique to execute stored procedures:
Yes, essentially the same thing.

It's up to the driver to go get the needed info. I would assume that in the
case of the SQL Server driver it does so by querying the system tables.
Could clearly do it by parsing the results of a call to sp_HelpText for a SP,
but that would be horribly inefficient, so I assume there's something better
than that. Have never looked for it.

Clearly the info is available for tables, as ADODB.Connection.OpenSchema can
get it all.
I've never run a trace against Access, but I have with SQL Server, and I
have never detected any extra database activity when using a variant array
to pass parameter values. Of course, I was never specifically looking for
such activity and I may have missed it.
Almost surely something pretty simple but maybe not what you are expecting.
But you know it *has* to be doing it. Now, I could imagine that the driver
would cache the info, so that it only has to make the call once. So it might
be harder to find.
Jul 26 '08 #12

P: n/a
Old Pedant wrote:
"Bob Barrows [MVP]" wrote:
>I'm assuming it does the same thing it does when a developer uses the
procedure-as-connection-method technique to execute stored
procedures:

Yes, essentially the same thing.

It's up to the driver to go get the needed info. I would assume that
in the case of the SQL Server driver it does so by querying the
system tables. Could clearly do it by parsing the results of a call
to sp_HelpText for a SP, but that would be horribly inefficient, so I
assume there's something better than that. Have never looked for it.

Clearly the info is available for tables, as
ADODB.Connection.OpenSchema can get it all.
Right, but I've never seen any queries against system tables, or calls to
FormatOnly when running traces against my code.
>
>I've never run a trace against Access, but I have with SQL Server,
and I have never detected any extra database activity when using a
variant array to pass parameter values. Of course, I was never
specifically looking for such activity and I may have missed it.

Almost surely something pretty simple but maybe not what you are
expecting. But you know it *has* to be doing it. Now, I could
imagine that the driver would cache the info, so that it only has to
make the call once. So it might be harder to find.
Actually, by "best guess", I was assuming that it was going by the datatypes
(or subtypes in the case of vbscript) of the passed data. So if you pass a
Date value, it creates an adDBTimestamp parameter (in the case of sql
server). IN the case of a string, I assume it creates an advarWchar.

In many cases, if ADO guesses wrong, the guesses are probably not so far off
that implicit conversions within the database are not possible. For example,
if SQL Server is expecting varchar instead of nvarchar, it has no problem
doing the implicit conversion. But that leaves the onus on the developer to
make sure he does the relevant datatype conversions when building the
variant array.

This is all conjecture of course.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 26 '08 #13

P: n/a

"Bob Barrows [MVP]" wrote:
Actually, by "best guess", I was assuming that it was going by the datatypes
(or subtypes in the case of vbscript) of the passed data. So if you pass a
Date value, it creates an adDBTimestamp parameter (in the case of sql
server). IN the case of a string, I assume it creates an advarWchar.

In many cases, if ADO guesses wrong, the guesses are probably not so far off
that implicit conversions within the database are not possible.
I dunno. Most VBS programmers don't even try to get the correct types set
up when making a call. Typically, they'll just use
foo = Request("foo")
and then pass that as a parameter. Which means it's a string.

How good is SQL Server at doing implicit conversion of strings (nvarchar,
presumably) to int and bit and real???

I was assuming that those conversions would need to be done by ADO, not by
the DB.

And even if SQL Server is that flexible, is Access? Or Oracle? Or FoxPro?

I dunno. It's an interesting question, but I don't think I've got the
energy to drag out a primitive enough debugger that I could trace all the
calls to find out.


For example,
if SQL Server is expecting varchar instead of nvarchar, it has no problem
doing the implicit conversion. But that leaves the onus on the developer to
make sure he does the relevant datatype conversions when building the
variant array.

This is all conjecture of course.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 27 '08 #14

This discussion thread is closed

Replies have been disabled for this discussion.