freelanceprogrammers.org Forum Index » Perl

View previous topic :: View next topic

Dates with Access

biddy420z Posted: Fri Apr 19, 2002 8:35 pm

Joined: 19 Apr 2002

Posts: 2

Dates with Access

I`ve asked this question in a couple other discussion groups:

I have data in an Access database, and one of the fields is Date/Time
with format 99/99/00;0 (the zero at the end means that the slashes
are stored with the numbers).
When I query the database on the server using Perl "select * from
CallLog where Date like `6/4/01`", I get the results "2001-06-04
00:00:00". This is fine, I really don`t care since I can extract what
I need from that string. However, when I try to insert a row
using "insert into CallLog (Date) values (`6/4/01`)", it doesn`t
work. I`ve tried several ways, `06/04/01`, `2001-06-04 00:00:00`,
etc. and nothing seems to work. FYI, I have no trouble with any other
fields. Any suggestions?

And get the same answer:

Use # (pound signs) around the date #6/4/01#

So, I`ve shortened the code, only inserting the required
field "CustomersID". This works. Then I have the same query,
adding the field "Date". This doesn`t work. Please tell me where I
went wrong?

-Amy

############################
#!C:Perlinperl.exe -wT
use Win32::ODBC;
use CGI `:standard`;

my $intCID;

my $strQuery = "select CustomersID from CallLog order by CustomersID";

$strDSN = "CCC";

print header;

if (!($Data = new Win32::ODBC($strDSN))) {
print "Error connecting to $strDSN
";
print "Error: " . Win32::ODBC::Error() . "
";
exit;
}

if ($Data->Sql($strQuery)) {
print "SQL failed.
";
print "Error: " . $Data->Error() . "
";
$Data->Close();
exit;
}

while ($Data->FetchRow()) {
%Data = $Data->DataHash();
$intCID = $Data{CustomersID};
}

$intCID = $intCID + 1;

$Data->Close();

my $strQuery1 = "insert into CallLog (CustomersID) values ($intCID)";
#my $strQuery1 = "insert into CallLog (CustomersID, Date) values
($intCID, #6/4/01#)";

if (!($Data = new Win32::ODBC($strDSN))) {
print "Error connecting to $strDSN
";
print "Error: " . Win32::ODBC::Error() . "
";
exit;
}

if ($Data->Sql($strQuery1)) {
print "SQL failed.
";
print "Error: " . $Data->Error() . "
";
$Data->Close();
exit;
}

print "Record saved successfully.";

print " ";
print $intCID;
print " ";
print $strQuery1;
print " ";

print end_html;

$Data->Close();
###############################

Using the first query1, I get these results:

Record saved successfully.
60
insert into CallLog (CustomersID) values (60)

################################

Using the second query1, I get these results:

SQL failed. Error: [-3502] [2] [0] "[Microsoft][ODBC Microsoft Access
Driver]
Syntax error in INSERT INTO statement."

########################################

biddy420z Posted: Mon Apr 22, 2002 11:53 pm

Joined: 19 Apr 2002

Posts: 2

Dates with Access

(thanx to another newsgroup, I have finally solved this irritating
problem...figured I`d post the solution, even though I now feel like
an idiot - it`s always the little things, isn`t it)

I found out that the word "date" is a reserved word in Access (hit me
over the head), and that I need to enclose the word "date" in
brackets, as in:

insert into CallLog (CustomersID, [Date]) values (99, #6/4/01#)

-- where 99, of course, is the next highest number since
CustomersID is type autonumber. So, not only did I not enclose my
date in (#), I used a reserved word which meant no matter what I did
to that stupid date string, it would`ve never worked. What kind of
luck is that!?!

-Amy

--- In perl2@y..., "biddy420z" <agatewood@n...> wrote:
> I`ve asked this question in a couple other discussion groups:
>
> I have data in an Access database, and one of the fields is
Date/Time
> with format 99/99/00;0 (the zero at the end means that the slashes
> are stored with the numbers).
> When I query the database on the server using Perl "select * from
> CallLog where Date like `6/4/01`", I get the results "2001-06-04
> 00:00:00". This is fine, I really don`t care since I can extract
what
> I need from that string. However, when I try to insert a row
> using "insert into CallLog (Date) values (`6/4/01`)", it doesn`t
> work. I`ve tried several ways, `06/04/01`, `2001-06-04 00:00:00`,
> etc. and nothing seems to work. FYI, I have no trouble with any
other
> fields. Any suggestions?
>
> And get the same answer:
>
> Use # (pound signs) around the date #6/4/01#
>
> So, I`ve shortened the code, only inserting the required
> field "CustomersID". This works. Then I have the same query,
> adding the field "Date". This doesn`t work. Please tell me where I
> went wrong?
>
> -Amy
>
> ############################
> #!C:Perlinperl.exe -wT
> use Win32::ODBC;
> use CGI `:standard`;
>
> my $intCID;
>
> my $strQuery = "select CustomersID from CallLog order by
CustomersID";
>
> $strDSN = "CCC";
>
> print header;
>
> if (!($Data = new Win32::ODBC($strDSN))) {
> print "Error connecting to $strDSN
";
> print "Error: " . Win32::ODBC::Error() . "
";
> exit;
> }
>
> if ($Data->Sql($strQuery)) {
> print "SQL failed.
";
> print "Error: " . $Data->Error() . "
";
> $Data->Close();
> exit;
> }
>
> while ($Data->FetchRow()) {
> %Data = $Data->DataHash();
> $intCID = $Data{CustomersID};
> }
>
> $intCID = $intCID + 1;
>
> $Data->Close();
>
> my $strQuery1 = "insert into CallLog (CustomersID) values
($intCID)";
> #my $strQuery1 = "insert into CallLog (CustomersID, Date) values
> ($intCID, #6/4/01#)";
>
> if (!($Data = new Win32::ODBC($strDSN))) {
> print "Error connecting to $strDSN
";
> print "Error: " . Win32::ODBC::Error() . "
";
> exit;
> }
>
> if ($Data->Sql($strQuery1)) {
> print "SQL failed.
";
> print "Error: " . $Data->Error() . "
";
> $Data->Close();
> exit;
> }
>
> print "Record saved successfully.";
>
> print " ";
> print $intCID;
> print " ";
> print $strQuery1;
> print " ";
>
> print end_html;
>
> $Data->Close();
> ###############################
>
> Using the first query1, I get these results:
>
> Record saved successfully.
> 60
> insert into CallLog (CustomersID) values (60)
>
> ################################
>
> Using the second query1, I get these results:
>
> SQL failed. Error: [-3502] [2] [0] "[Microsoft][ODBC Microsoft
Access
> Driver]
> Syntax error in INSERT INTO statement."
>
> ########################################

freelanceprogrammers.org Forum Index » Perl

All times are GMT
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Freelace Website Designer - Customer web design and software building.
China Wholesale - Electronics Products
Character Studio - Tutorials and Help