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="VB SCRIPT"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt d">
<%
Set oConn = Server.CreateOb ject("ADODB.Con nection")
oConn.Open("PRO VIDER=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.val ue%>">
</td>
<%else%>
<td><%Response. Write(x.value)% ></td>
<%end if
next
%>
<%rs.MoveNext %>
</tr>
<%
loop
conn.close
%>
</table> 13  2906 
wrote on 25 jul 2008 in microsoft.publi c.inetserver.as p.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.CreateOb ject("ADODB.Con nection")
oConn.Open("PRO VIDER=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(s ql)
%>
Count = <% = oRs("Ct") %[on <% = d %>]
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
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.val ue%>">
</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.CreateOb ject("ADODB.Con nection")
oConn.Open("PRO VIDER=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#
Set oConn = Server.CreateOb ject("ADODB.Con nection")
oConn.Open("PRO VIDER=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 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]" 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!
Have you ever used the Java PreparedStateme nt class?
I wish ADO had used something as simple as it.
Goes something like this:
String SQL = "INSERT INTO sometable ( id, name, image ) VALUES(?,?,?)"
PreparedStateme nt ps = conn.prepareSta tement(SQL);
ps.setInt(1, id);
ps.setString(2, name);
ps.setBlob(3,im ageBlob);
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 wrote:
Have you ever used the Java PreparedStateme nt class?
I wish ADO had used something as simple as it.
Goes something like this:
String SQL = "INSERT INTO sometable ( id, name, image )
VALUES(?,?,?)" PreparedStateme nt ps = conn.prepareSta tement(SQL);
ps.setInt(1, id);
ps.setString(2, name);
ps.setBlob(3,im ageBlob);
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"
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 PreparedStateme nt 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 PreparedStateme nt class?
I wish ADO had used something as simple as it.
Goes something like this:
String SQL = "INSERT INTO sometable ( id, name, image )
VALUES(?,?,?)" PreparedStateme nt ps = conn.prepareSta tement(SQL);
ps.setInt(1, id);
ps.setString(2, name);
ps.setBlob(3,im ageBlob);
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"
=?Utf-8?B?T2xkIFBlZGF udA==?= wrote on 26 jul 2008 in
microsoft.publi c.inetserver.as p.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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: anthonyberet |
last post by:
I work for an organisation that uses a bespoke document imaging system, the
database of which is an MS sql server.
We have MS Access and already use it for some querying of the database.
The database comprises a large number of distinct cases, which are
differentiated by case reference numbers, in one field (table?) of the
database. Each of these cases may have many documents associated with it,
denoted by the reference number, and these...
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
|
by: Random Person |
last post by:
Does anyone know how to use VBA to relink tables between two MS Access
databases? We have two databases, one with VBA code and the other with
data tables. The tables are referenced by linked tables in the database
where the code resides. If we move the database with the data tables to
a new directory, the links are no longer valid.
I tried to update the links by changing the Connect property and refreshing:
Set td = db.TableDefs(0)...
|
by: Justin Koivisto |
last post by:
I am trying to create a report that displays a name of an advertising
source and count of the number of times it was hit between certain date
ranges. The data is split between two different databases: this access
db, and a remote MySQL server. The MySQL tables are linked in the access db.
What I have done so far is created an On Open event for a report that
does the following:
* prompt user for the date range
* get which sources were...
|
by: Kenneth Courville |
last post by:
Hello, I'm looking for assistance with the Access object model. I know
this is VB, but I'm building an Office Add-using C# directed at Access
2002. I'm literate in VB, so you can reply in VB... I think my problem
mainly lies in knowing the quirks of the Access object model.
Basically, I'm looking for a method of determining if an Access database
is open yet. AND I'm looking for a method that doesn't not require
checking for an...
| | |
by: Andrew Arace |
last post by:
I scoured the groups for some hands on code to perform the menial task
of exporting table data from an Access 2000 database to Oracle
database (in this case, it was oracle 8i but i'm assuming this will
work for 9i and even 10g )
No one had what I needed, so I wrote it myself.
I Rule.
This code isn't going for efficiency, and isn't trying to be dynamic.
It doesn't create the table structure in Oracle, that's up to you. (I
|
by: RSH |
last post by:
Hi,
I have a situation where I have created a little application that makes an
Access database from a SQL Database for reporting purposes. it does the job
well, but it seems a bit slow. Is there anything that i can do to speed the
app up a bit?
namespace SQLToAccessBackup
|
by: etuncer |
last post by:
Hello All,
I have Access 2003, and am trying to build a database for my small
company. I want to be able to create a word document based on the data
entered through a form. the real question is this: can Access create
the document and place it as an OLE object to the relevant table?
Any help is greatly appreciated.
Ricky
|
by: pic078 via AccessMonster.com |
last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files)
that remains stuck in task manager after exiting the application - you can't
reopen database after exiting as a result - I have read every post out there
and spent hours trying to figure out the problem with no success whatsoever -
I have constrained the problem to one form however, and I think it's hiding
somewhere in my code associated with this form, which is...
|
by: KevinC |
last post by:
Hi,
I have a database that contains a table with an administration area
and a number associated with the "count" of a certain characteristic
of that area.
For use in another piece of software I need to convert the count
system to individual records. For example I need to go from:
AREA COUNT
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
