problem in DBD::ODBC

i have installed the dbi and DBD::ODBC module in my computer.  Now i am trying to update and add record to my logintable.  There record cannot be added and updated due to don't know what reason.  Here is my code :

       my $dbh = DBI->connect('DBI:ODBC:Login','yeeling','111');
       $sql= "INSERT INTO LoginTable (LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "'," . scalar localtime(time). ")";
       $sth = $dbh->prepare($sql);
       $sth->execute($sql) ||
          die "unable to save record";

i even have tried other way , the code is :

     my $sql = qq/
               INSERT INTO LoginTable
               (LoginCount, UserName, Password, Email, Folio, LoginDate)
               (0,$UserName,$Pass, $Email, $Folio, scalar localtime(time))
     $sth = $dbh->prepare( $sql);
     my $rv = $dbh->do($sql) || die print "Error while updating";

The Select statement works well.
please help me !

Who is Participating?
makerpConnect With a Mentor Commented:
no take the auto field entry out of your insert. dont list it in the first lot of () or the values ()

"INSERT INTO LoginTable ( LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "',Date())";

that date you are putting ! if your on access the date must be wrapped in #'s, if sql server then you must use the convert function.



now the date you attempt to enter must be in US format, i dont know why but it does even if you date settings specify (dd/mm/yyyy).

now if your attempting to enter the current date do this

UserName,Password,Email,Folio,LoginDate) VALUES (0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "',Date()")";

Date() is a vb fucntion that works in access/sql server. do not wrap this in #'s only do this for dates you build up and want to enter

Hope this helps

Also check if the user that you are logging in with has the right to insert, update & delete from the database.

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

riyugieAuthor Commented:
I have tried the statement u suggested , however the new record still cannot be added into the table.  

For you information there is an auto increment field in the table - the LoginID , do i need to include this field in my sql statement as well??

I have tried to construct the sql statement to look loke this

$sql= "INSERT INTO LoginTable (LoginID, LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (,0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "',Date(),Date())";

$sth = $dbh->prepare($sql);
$sth->execute($sql) ||
     die "unable to save record";

is it a valid sql statement??

i know that the date might be the main cause for this problem because i have tried a sql UPDATE statement that update the LoginCount field and LoginDate it cannot be executed as well.

But the Update statement works when i choose to NOT update the LoginDate field.

Hope u can give more suggestion on  this!  Thank you makerp and maneshr.
i would suggest you try this....

Before the $sth = $dbh->prepare($sql);  statement
add the following

print $sql,"\n";

Now you can see the actual SQL statement that will be executed in your browser.

Next copy that SQL statement and try executing it from the Command Prompt. if the SQL fails you know the reason could be due to lack of proper params to the insert.

If the insert succeeds from the prompt, you know the problem could be due to insufficient insert permissions.

Hope that helps.
christopher sagayamCommented:
Have you tried

$t = scalar localtime(time) ;

$sql= "INSERT INTO LoginTable(LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (0,'$UserName','$Pass','$Email','$Folio','$t')";
riyugieAuthor Commented:
i found that even the sql seems to not executed for the xitami server, perl or whatever.
Because i have tried to create one simple table that have 2 text fields inside and try to INSERT into the table new record from the cgi page .  The record cannot be added as well.

Do anybody knows what is the cause of this problem??
can you insert the record from the command prompt??
christopher sagayamCommented:
Well then it is definetly permission problem ..

what type of dsn are your using ?

System DSN or File DSN ?

What database are you using ? Ms-Access or Ms-SQL-server ?
riyugieAuthor Commented:
I have set it to be the system dsn and it is MS ACCESS database.

and i would like to know also on how to access the dbase(*.dbf) database???

i am also having problem with the pagfe redirection thing.. This is what i want to do:

firstly, i try to validate and check whether the username and password is valid.  AND if it is a valid login, i would like to redirect the page to let the user to view their inbox or else the user will need to reenter the login info.

Can i know how to redirect the page to the inbox page??

i have tried to use the object

print $query->redirect(-location=>"http://localhost/cgi-bin/home/inbox.cgi"

however i does not seem to work, i want to redirect it only after verified that the login is valid , not straight away redirect it whenever the page is browsed.

thank you

christopher sagayamCommented:
You can access a dbf file using

DBD::Xbase or

even ODBC driver for dbf files ...

You can use the redirect command ONLY if you have NOT used any print $query->header .. or any print "" statements before the redirect query in your program ...


print $query->redirect("http://localhost/cgi-bin/home/inbox.cgi")

and It will work ..

BUT The below will not work !
print $q->header;

print "hello" ;

print $query->redirect("http://localhost/cgi-bin/home/inbox.cgi")

riyugieAuthor Commented:
how to solve this problem?
riyugieAuthor Commented:
Adjusted points from 30 to 50
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.