access database count

Discussion in 'ASP General' started by craigchalmers@gmail.com, Jul 25, 2008.

  1. Guest

    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, 2008
    #1
    1. Advertising

  2. Evertjan. Guest

    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)
     
    Evertjan., Jul 25, 2008
    #2
    1. Advertising

  3. Old Pedant Guest

    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.
     
    Old Pedant, Jul 26, 2008
    #3
  4. Old Pedant Guest

    > <%
    > 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.
     
    Old Pedant, Jul 26, 2008
    #4
  5. 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"
     
    Bob Barrows [MVP], Jul 26, 2008
    #5
  6. Old Pedant Guest

    "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!
     
    Old Pedant, Jul 26, 2008
    #6
  7. Old Pedant Guest

    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.
     
    Old Pedant, Jul 26, 2008
    #7
  8. 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"
     
    Bob Barrows [MVP], Jul 26, 2008
    #8
  9. Old Pedant Guest

    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"
    >
    >
    >
     
    Old Pedant, Jul 26, 2008
    #9
  10. Evertjan. Guest

    =?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)
     
    Evertjan., Jul 26, 2008
    #10
  11. 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"
     
    Bob Barrows [MVP], Jul 26, 2008
    #11
  12. Old Pedant Guest

    "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.
     
    Old Pedant, Jul 26, 2008
    #12
  13. 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"
     
    Bob Barrows [MVP], Jul 26, 2008
    #13
  14. Old Pedant Guest

    "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"
    >
    >
    >
     
    Old Pedant, Jul 27, 2008
    #14
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MSDN
    Replies:
    0
    Views:
    686
  2. Fernando Lopes
    Replies:
    0
    Views:
    868
    Fernando Lopes
    Mar 29, 2005
  3. JC
    Replies:
    3
    Views:
    598
    =?ISO-8859-2?Q?Dra=BEen_Gemi=E6?=
    Jun 19, 2006
  4. Tony Johansson
    Replies:
    7
    Views:
    519
    Gregory A. Beamer
    Dec 23, 2009
  5. efelnavarro09
    Replies:
    2
    Views:
    953
    efelnavarro09
    Jan 26, 2011
Loading...

Share This Page