access database count

C

craigchalmers

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>
 
E

Evertjan.

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#));")
Click to expand...

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 %>]
 
O

Old Pedant

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

Old Pedant

<%
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
Click to expand...

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

Bob Barrows [MVP]

Old said:
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.
Click to expand...

.... or use parameters ... :)
 
O

Old Pedant

Bob Barrows said:
.... or use parameters ... :)
Click to expand...

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!
 
O

Old Pedant

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

Bob Barrows [MVP]

Old said:
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.
Click to expand...

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

Old Pedant

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

Evertjan.

=?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.
Click to expand...

Traditionnnnn !
 
B

Bob Barrows [MVP]

Old said:
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.
Click to expand...

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

Old Pedant

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

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.
Click to expand...

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

Bob Barrows [MVP]

Old said:
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.
Click to expand...

Right, but I've never seen any queries against system tables, or calls to
FormatOnly when running traces against my code.
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.
Click to expand...

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

Old Pedant

Bob Barrows said:
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.
Click to expand...

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date filed in Access Database 3
Date in database 32
Can anybody plz help me outof this problem in mah code 0
Jet Database Error 4
With YYYYMMDD Trying to change MM to August 7
Connect to Access 2002 on local computer 3
Newbie need help - SELECT by date in Access 3
Dynamically add posted form items into array 1

Members online

Total: 304 (members: 1, guests: 303)
Robots: 344

Forum statistics

Threads
473,968
Messages
2,570,149
Members
46,695
Latest member
StanleyDri

Latest Threads

Top