Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > access database count

Reply
Thread Tools

access database count

 
 
craigchalmers@gmail.com
Guest
Posts: n/a
 
      07-25-2008
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>





 
Reply With Quote
 
 
 
 
Evertjan.
Guest
Posts: n/a
 
      07-25-2008
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)
 
Reply With Quote
 
 
 
 
Old Pedant
Guest
Posts: n/a
 
      07-25-2008
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.


 
Reply With Quote
 
Old Pedant
Guest
Posts: n/a
 
      07-25-2008
> <%
> 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.




 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-26-2008
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"


 
Reply With Quote
 
Old Pedant
Guest
Posts: n/a
 
      07-26-2008
"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!


 
Reply With Quote
 
Old Pedant
Guest
Posts: n/a
 
      07-26-2008
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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-26-2008
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"


 
Reply With Quote
 
Old Pedant
Guest
Posts: n/a
 
      07-26-2008
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"
>
>
>

 
Reply With Quote
 
Evertjan.
Guest
Posts: n/a
 
      07-26-2008
=?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)
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Count = Count + 1 Using only std_logic_1164 Doubt efelnavarro09 VHDL 2 01-26-2011 03:49 AM
Count(*) in a Subquery with multiple tables: How does SQL determine which table to generate the Count() from? Kaimuri MCSD 3 12-29-2004 06:38 PM
I am adding a new row to the datagrid dynamically but if i use the Count property of Item it is not showing the count of the new rows being added Praveen Balanagendra via .NET 247 ASP .Net 2 06-06-2004 07:16 AM



Advertisments