DBI, MS Access, inserting/updating a Access Date/Time value

Hi,

Does anyone have any idea what is up with Microsoft Access and its 
ridiculous Date/Time formatting options? It doesn't seem to matter what 
format I attempt to insert with. The directory and db file permissions 
are all Full Control. I'm using the "#" around the Date/Time format as 
required. The database has nothing as the Format in Design view on the 
General tab. The insert below has never worked once. On the up side if I 
run an insert that has no mention of the Date/Time field then it will 
just insert the record using the =Now() function as the default value 
but that doesn't help much considering I would want to update the field. 
There are no question marks in any of the field names as suggested on 
many pages dicussing this error below. Thanks for any help!  I'm using 
MS Access, Perl, DBI & DBD::ODBC win32 modules, IIS on XP.

Here is the error:
........................................................................
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access 
Driver]COUNT field incorrect  (SQL-07002)(DBD: st_execute/SQLExecute 
err=-1) at C:\Inetpub\Scripts\somedir\dsntest.pl line 18.
........................................................................


Here is the code:
........................................................................
#!C:\perl\bin\perl.exe
#
#print "Content-type: text/html\n\n";
use DBI;
#         2005-04-12 20:48:31
#         4/5/2004 5:3:45 PM
#         4/12/2005 10:28:14 PM
#         d m yyyy h:m:s AM/PM

$dt = "#4/5/2004 5:3:45 PM#";

$sql = "INSERT INTO tblTest (TimeStamp) VALUES ($dt)";

my $dbh = DBI->connect("dbi:ODBC:dsntest", "", "", { PrintError => 0, 
RaiseError => 1 })
          || die "database connection can't connect to database 
$DBI::errstr";

my $sth = $dbh->prepare($sql) ||  die "prepare statement- $DBI::errstr";
$sth->execute() || die "execute statement- $DBI::errstr";
$sth->finish;
........................................................................























0
robb
4/13/2005 6:34:09 AM
perl.dbi.users 11085 articles. 1 followers. Follow

9 Replies
928 Views

Similar Articles

[PageSpeed] 14

On Tue, 12 Apr 2005 23:34:09 -0700, Robb wrote:

>Does anyone have any idea what is up with Microsoft Access and its 
>ridiculous Date/Time formatting options? 

I am guessing that MS-Access formats dates according to your settings in
the "Regional Settings" control panel.

-- 
	Bart.
0
bart
4/13/2005 11:13:44 AM
On Tue, 2005-04-12 at 23:34 -0700, Robb wrote:
> Does anyone have any idea what is up with Microsoft Access and its 
> ridiculous Date/Time formatting options?

I asked that question daily in a previous life working with access, and
that that was with ms tools! I never got a sane answer.
I don't now if newer versions are any better but access 97 and 2000 both
had a nasty habit of sometimes ignoring any regional settings and just
using American dates.

As Bart suggested checking the regional settings for the box is worth
it. I would also check how the dsn is setup.

> $dt = "#4/5/2004 5:3:45 PM#";
> 
> $sql = "INSERT INTO tblTest (TimeStamp) VALUES ($dt)";
> 
> my $dbh = DBI->connect("dbi:ODBC:dsntest", "", "", { PrintError => 0, 
> RaiseError => 1 })
>           || die "database connection can't connect to database 
> $DBI::errstr";
> 
> my $sth = $dbh->prepare($sql) ||  die "prepare statement- $DBI::errstr";
> $sth->execute() || die "execute statement- $DBI::errstr";
> $sth->finish;
> .......................................................................

Do you get the same error if you use a bind value instead of putting $dt
directly in the string? ie

 $sql = "INSERT INTO tblTest (TimeStamp) VALUES (?)";
 $sth->execute($dt) || die "execute statement- $DBI::errstr";

Also have you tried ODBC date formats as your going through a DSN?

mark
--


This email (and any attachments) is intended solely for the individual(s) to whom addressed. 
It may contain confidential and/or legally privileged information. 
Any statement or opinions therein are not necessarily those of ITN unless specifically stated. 
Any unauthorised use, disclosure or copying is prohibited. 
If you have received this email in error, please notify the sender and delete it from your system. 
Security and reliability of the e-mail and attachments are not guaranteed. 
You must take full responsibility for virus checking.



Independent Television News Limited, 

Registered No. 548648 England,

VAT Reg. No: GB 756 2995 81, 

200 Gray's Inn Road, London WC1X 8XZ,

Telephone: 020 7833 3000.

0
mark
4/13/2005 12:08:04 PM
Robb wrote:

> Hi,
>
> Does anyone have any idea what is up with Microsoft Access and its
> ridiculous Date/Time formatting options?

:)

In VB, I use the format:

#dd-mmm-yyyy HH:mm:ss#

Your example:
$dt = "#4/5/2004 5:3:45 PM#";

I would write as:
$dt = "#04-MAY-2004 17:03:34#";

You have to pad your numbers. You can't just use "5:3:45" and expect
Access ( or others ) to know what you want.
Converting the month to a 3-character string version forces Access to
recognise the 1st part as the day and the last part as the year. If you
don't do this, it's selection of day/month/year positions appears to be
random.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au
0
dkasak
4/13/2005 11:19:00 PM
On Thu, Apr 14, 2005 at 09:19:00AM +1000, Daniel Kasak wrote:
> Robb wrote:
> 
> > Hi,
> >
> > Does anyone have any idea what is up with Microsoft Access and its
> > ridiculous Date/Time formatting options?
> 
> :)
> 
> In VB, I use the format:
> 
> #dd-mmm-yyyy HH:mm:ss#
> 
> Your example:
> $dt = "#4/5/2004 5:3:45 PM#";
> 
> I would write as:
> $dt = "#04-MAY-2004 17:03:34#";

Wouldn't single quotes also work?:

  $dt = "'04-MAY-2004 17:03:34'";

> Converting the month to a 3-character string version forces Access to
> recognise the 1st part as the day and the last part as the year. If you
> don't do this, it's selection of day/month/year positions appears to be
> random.

Doesn't putting the year first force YYYY-MM-DD interpretation?

Tim [who doesn't know Access (and doesn't want to) but is curious]
0
Tim
4/14/2005 11:12:20 AM
>=20
>=20
> On Thu, Apr 14, 2005 at 09:19:00AM +1000, Daniel Kasak wrote:
> > Robb wrote:
> >=20
> > > Hi,
> > >
> > > Does anyone have any idea what is up with Microsoft=20
> Access and its=20
> > > ridiculous Date/Time formatting options?
> >=20
> > :)
> >=20
> > In VB, I use the format:
> >=20
> > #dd-mmm-yyyy HH:mm:ss#
> >=20
> > Your example:
> > $dt =3D "#4/5/2004 5:3:45 PM#";
> >=20
> > I would write as:
> > $dt =3D "#04-MAY-2004 17:03:34#";
>=20
> Wouldn't single quotes also work?:
>=20
>   $dt =3D "'04-MAY-2004 17:03:34'";
>=20
> > Converting the month to a 3-character string version forces=20
> Access to=20
> > recognise the 1st part as the day and the last part as the year. If=20
> > you don't do this, it's selection of day/month/year=20
> positions appears=20
> > to be random.
>=20
> Doesn't putting the year first force YYYY-MM-DD interpretation?
>=20
> Tim [who doesn't know Access (and doesn't want to) but is curious]

Sorry for responding late.  The best way with DBI & ODBC is to use the =
ODBC date and time
formats, which are in the DBD::ODBC tests.  Perhaps it should be in the =
documentation too
....  The short version is to use ODBC escapes:
	{d 'YYYY-MM-DD'}
Or
	{ts 'YYYY-MM-DD HH:MM:SS'}

This works on most ODBC drivers correctly, even when binding parameters, =
although I recall
that I had issues with Oracle drivers at one point.

Note also that since you are using DBI and ODBC, you are in effect =
bypassing some of the
Access front end specifics.

Regards,

Jeff

>=20

0
jurlwin
4/14/2005 11:50:43 AM
Tim Bunce wrote:

>On Thu, Apr 14, 2005 at 09:19:00AM +1000, Daniel Kasak wrote:
>  
>
>>Robb wrote:
>>
>>    
>>
>>>Hi,
>>>
>>>Does anyone have any idea what is up with Microsoft Access and its
>>>ridiculous Date/Time formatting options?
>>>      
>>>
>>:)
>>
>>In VB, I use the format:
>>
>>#dd-mmm-yyyy HH:mm:ss#
>>
>>Your example:
>>$dt = "#4/5/2004 5:3:45 PM#";
>>
>>I would write as:
>>$dt = "#04-MAY-2004 17:03:34#";
>>    
>>
>
>Wouldn't single quotes also work?:
>
>  $dt = "'04-MAY-2004 17:03:34'";
>
>  
>
It depends on what your drivers are doing to the data before it gets to
Access. Access by itself certainly won't allow single quotes for dates.
I haven't used Perl to talk to Access before. I've seen it done with
DBD::Proxy or something like that, but I've never tried to store
anything useful in Access.

>>Converting the month to a 3-character string version forces Access to
>>recognise the 1st part as the day and the last part as the year. If you
>>don't do this, it's selection of day/month/year positions appears to be
>>random.
>>    
>>
>
>Doesn't putting the year first force YYYY-MM-DD interpretation?
>
>  
>
Ha! You'll never get a Microsoft product to reliably recognise that date
format. For a while I thought I'd convinced SQL Server to understand it,
but I must have been mistaken ... it will understand the year bit
alright, but it swaps the month and day around if the day is less than 13.

>Tim [who doesn't know Access (and doesn't want to) but is curious]
>  
>
Tim, if I were you, I'd keep it like that ( not knowing Access ). Move
your data to MySQL or something else that at least follows standards and
doesn't explode when you feed it more than 100 rows.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au
0
dkasak
4/14/2005 11:00:32 PM
>=20
> On Thu, Apr 14, 2005 at 07:50:43AM -0400, Jeff Urlwin wrote:
> > > >=20
> > > > I would write as:
> > > > $dt =3D "#04-MAY-2004 17:03:34#";
> > >=20
> > > Wouldn't single quotes also work?:
> > >=20
> > >   $dt =3D "'04-MAY-2004 17:03:34'";
> > >=20
> > > > Converting the month to a 3-character string version forces
> > > Access to
> > > > recognise the 1st part as the day and the last part as=20
> the year.=20
> > > > If
> > > > you don't do this, it's selection of day/month/year=20
> > > positions appears
> > > > to be random.
> > >=20
> > > Doesn't putting the year first force YYYY-MM-DD interpretation?
> > >=20
> > > Tim [who doesn't know Access (and doesn't want to) but is curious]
> >=20
> > Sorry for responding late.  The best way with DBI & ODBC is=20
> to use the=20
> > ODBC date and time formats, which are in the DBD::ODBC=20
> tests.  Perhaps=20
> > it should be in the documentation too ...  The short=20
> version is to use ODBC escapes:
> > 	{d 'YYYY-MM-DD'}
> > 	{ts 'YYYY-MM-DD HH:MM:SS'}
>=20
> Thanks Jeff. But do you know the answers to the two questions?

In terms of Access, I don't really know what it will and won't do in =
"ODBC" mode, rather
than "JET" mode.  Access has two (ugly) heads and DBI/DBD::ODBC only tap =
into the ODBC
side, while the Access interface side has different rules/behavior (you =
can create certain
column types in the engine that you can't in ODBC, as far as I can =
tell). =20

In the strictest ODBC sense, no, it doesn't seem to work that way...in =
either case.  THE
thing to do is escape the dates and times as per above.  That will also =
ensure some level
of compatibility across database systems.  Typically, I find that you =
get compatibility
with the host DBMS, but Access is different.  The "jet" / front end side =
does things
differently than the ODBC "front end" -- it's just two heads accessing =
the same tail
(again, as far as I can tell).

Jeff

>=20
> Tim.
>=20

0
jurlwin
4/14/2005 11:14:33 PM
Well Thanks for all the feedback.  I thought I had figured it out using 
a combination of various pieces from everyone who posted to the 
questions. But again, it wasn't working ... I had one script working and 
another that wasn't ... what the !@#$. Anyway,  "I think" I've figured 
out what the problem is.  Ya, its annoying as hell.  At this point I'm 
assuming that the whole "#" thing for Date/Time fields is really for 
Micorosoft centric technologies such as ASP.  But for Perl &  DBI/DBD 
used with Access the problem was seemingly bizarre because I had 
multiple problems.  The cdate('$dt') function works .... and so does 
this other format method {ts '2010-10-11 07:07:07'} ... but also does 
simple single quotes as Tim suggested.  As it turns out what was really 
nailing me is that the field name has a lot to do with it.  I originally 
had the Date/Time field named "TimeStamp" which doesn't work, I have 
also tried DateTime which doesn't work, dt worked which was why my 
simple prototype worked while my app didn't, Date_Time works .... so 
apparently I can't pick a decent field name.  Funny thing is, this was 
someone else's database I usually don't use Upper Case at all.  So, I'm 
going to rename all these TimeStamp fields and hope for the best.  I 
hope the next project is a MySQL one!  Any additional info would be 
appreciated immensely.

$dt = "4/14/2005 23:10:57 PM";
$sql = "UPDATE tblTest SET name = 'Robb', dt = cdate('$dt') WHERE id = 1";

$sql = "UPDATE tblTest SET name = 'Robb', Date_Time = {ts '2010-10-11 
07:07:07'} WHERE id = 1";

my $sql = "UPDATE tblUser SET EmailAddress = '$newemail', Date_Time = 
'$dt' " .
              "WHERE Password = '$password'";


TimeStamp < ---- BAD
DateTime <---- BAD


Robb




0
robb
4/15/2005 8:25:46 AM
Robb wrote:
> As it turns out what was really 
> nailing me is that the field name has a lot to do with it.

[snip]

> TimeStamp < ---- BAD
> DateTime <---- BAD

Not to encourage any further deviation from standards, but with Access and SQLServer databases you can still use reserved words as table or 
column names, as long as you qoute them properly.
With MS databases, you have to surround those names in square brackets:

	select [TimeStamp] from [DateTime]

Not recommended though.

Rhesa
0
perl
4/15/2005 11:46:20 AM
Reply:

Similar Artilces:

Accessing MS Access through the DBI ODBC
Hi all, I am trying to access an MS Access database via perl DBI and I am unable = to. I checked the security and there is only one 'Admin' user with no = password and I use that in my connection string. However, I cannot = connect. Please help. Regards, Javier Moreno Hi Javier, > I am trying to access an MS Access database via perl DBI and I am unable to. I checked the security and there is only one 'Admin' user with no password and I use that in my connection string. However, I cannot connect. Please help. > 1. Create an ODBC connection via %Sys...

Dbi objects to access an MS Access Db
I'm having a problem getting Tran-ide to recognize my access 2000 database. I've created a system dsn called Midas and created the attached nnsyreg.dat file and put it into e:\sybase\ImpactClient-5_3\config directory. I've also put this file in my projects directory and still get the following message. Unable to connect to session Midas. Any Advice? Thanks Brent begin 666 nnsyreg.dat M4V5S<VEO;BY-:61A<PT*"4Y.3U1?4TA!4D5$7TQ)0E)!4ED@/2!D8G0R-VUS M;V1B8PT*"4Y.3U1?1D%#5$]265]&54Y#5$E/3B ]($Y.4V5S3T1"0T9A8W1O M<GD-"@E.3E]315-?4T525D52(#T@24U...

How to remove the date part of a Date/Time value in MS Access
I am inserting a time value into a MS Access Date/Time field and notice that MS Access does not display the date portion of the value but when I look at the same data in my programming using a TADOTable component I am seeing the date of 12/30/1899 in conjunction with my time value. I read that Access is handling this within MS Access so that when a 0.sometime value is inserted it does not display the date. How do I handle this within my Delphi program so that I can display the time only in a Date/Time type field? Jeff Howard wrote: > I am inserting a time value into a MS Access Date/T...

Re: Re: Accessing MS Access through the DBI ODBC
> From: Kevin Carothers <kevindotcar@gmail.com> > Date: 2005/03/30 Wed PM 06:44:48 CST > 1. Create an ODBC connection via %SystemRoot%\system32\odbcad32.exe > Call the database connect name "ODBCName" > > 2. In your Perl code add: > [---] > use DBI; > [---] > $dbh = DBI->connect("dbi:ODBC:ODBCName",'',''); > [---] Just my opinion, but I don't think this is good advice. For one, it creates extra administration on the system, by forcing the user/programmer /sysadmin/webadmin to ...

Insert a null value for a date field in a MS access dDB
I have to update a record where the date field must be changed in a null value. I use this method : Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("../web_mdb/utenti.mdb") & ";" Dim SQL as string = "Update DBTable Set DITTA = @DT, DATA_REG = @REG Where utente = '" & UT & "' and password = '" & PSWR & "'" Dim Conn as New OleDBConnection(strConn) Dim Cmd as New OleDBCommand(SQL, Conn) ...

Retrieve Only Date from Date/Time in Ms Access
Hello,When retrieving data from an ACCESS database table, I want the generated ASP page to display only the date inputted by the user. It currently displays the date and then the time is always 12:00. Anyone have any suggestions as how I can have it display only the time, but leave the access format as Date/Time? Format the output - use {0:d} Something like:label1.text=String.Format("{0:d}",YourDate)David WierMCP/ASPInsiderASPNet101.com - where to look first!Please Vote for ASPNet101 - 'Best Community Resource'!Control Grouper - easily control properties for multipl...

Inserting Date into Access Date/Time Field
Everytime i capture a date from an asp:dropdownlist and try to insert it into an access date/time field i keep getting a Data type mismatch in criteria expression error (0x80040e07). is there a way i need to format the data before inserting? Any help would be ace!1 using System; 2 using System.Data; 3 using System.Data.OleDb; 4 using System.Configuration; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.WebControls; 9 using System.Web.UI.WebControls.WebParts; 10 using System.Web.UI.HtmlControls; 11 12 ...

DBI and MS Access
Is there a DBD module for Microsoft Access databases? I've searched around on search.cpan.org, but I haven't come across anything. -- Andrew Gaffney Network Administrator Skyline Aeronautics, LLC. 636-357-1548 Andrew Gaffney wrote: > Is there a DBD module for Microsoft Access databases? I've searched > around on search.cpan.org, but I haven't come across anything. Let me elaborate. This will be from Linux, so the Windows ODBC interface to Access isn't an option. -- Andrew Gaffney Network Administrator Skyline Aeronautics, LLC. 636-357-154...

DBI and MS Access????
Hi all, I'm looking for the quick and painless (idiots guide) instructions to connect to an MS Access database running on NT from a Digital UNIX machine. Need to knows: What DBD:: ?? needs installing How should the database handle look seeing as how there is no instance for an Access DB : $dbh1 = DBI->connect( "dbi:??:??", "username", "password" ) . Do I need some software for this? Cheers, Mark _____________________________________________ Mark Martin Computer Centre National University of Ireland Maynooth Tel: (01)708 4716/...

Access DB Nul values insert, update & Date Formatting
Hi Guys, I have problems with Access Db when I try to insert values. What is the exact solution? The next problem is how can I format dateTime according to local users of my system. Using prameters, you need to set the parameter value to DBNull.Value rather than null or nothing. Show some code and perhaps we can help more. Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My Blog Thank you very much :-) I tried this code. And I works fine. I used Email as DBNull.value private void Form1_Load(object sender, System.EventArgs e) { OleDbConnec...

prb using Date/time field with DBI , odbc connected with Access 2000
Hi, I have some problems using date/time field with DBI and a MS Access Database over ODBC. I try to insert a record containig a date : $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ, ......) values (......,$rec->{majdate},.....) $rec->{majdate} come from another table and i got it without a problem with a SELECT query. DBI trace shows that : dbd_preparse scanned 2 distinct placeholders dbd_st_prepare'd sql f61546664 INSERT INTO SIT_PREVISIONNEL_HISTO (...,...,...,DATEMAJ,...) ...

GridView data table and MS Access table. Some time I like MS Access...
Hi: I want all the rows and coloums data showing in the GridView can be editable, just like the MS Access table on the form, you can modify and update any cell and it will be saved if your mouse leave that cell (or you click "Save" button). The reason I ask is: everyday I have about hundrands of data need to be set the date(Today's date), to be set the status (on/off), and input Receipt Num, if I using Gridview's "Edit" "Update" feature, I find there are still two many clicks, and also the selected rows changing the size and p...

MS SQL through MS Access using DBI:ODBC
Hi When connecting an MS SQL database from MS Access it is necessary to use the option dbSeeChanges with db.openrecordset. This is ok when using Visual Basic but is it possible to access the data using DBI:ODBC. Win2000 MS SQL version 7 MS Access 97 perl 5.005 (build 509) W32ODBC.pm,v 10.1 1998/08/14 Thanks. Ian ...

Time out error is occur when one user inserting or updating records in table using transaction and another accessing the record
Hi, I am facing a problem in my application when a user inserting record in table or updating using transaction and another user fething the record at sae time then time out is occur.  I am also trying to use no lock with select statement but it's also not work. Please provide me a appropriate solution.Rakesh Gupta It will good to understand your problem If you can provide the code.  Thanks,santosh_maharajaPlease mark as answer if you got expected solution. Hi,   I am using given below code for insertion : - -  DECLARE @TranName VARCHAR(20);SELECT @TranName...

Web resources about - DBI, MS Access, inserting/updating a Access Date/Time value - perl.dbi.users

Resources last updated: 11/25/2015 6:57:57 PM