sprakash has asked for the wisdom of the Perl Monks concerning the following question:

Hi! I've been trying to dump data from a web form (http://prakashfamilyonline.com (the lower form)) and get the script to execute perfectly but the data does not go into the database........ the code is below.. PLEASE HELP! Thanks. Surya PS: It gives me a "Can't execute SQL statement. Perl says , DBI says " error but doesnt say what error and I'm not able to figure it out............. 
#!/perl/bin

require 'cgi-lib2.pl';

use CGI qw(:standard);        # Must be used to get the param() functi
+on
use strict;
use OLE;

my $formemail = param("formemail");
my $formpass = param("formpassword");
my $formcpass = param("cformpassword");
my $formfriend = param("formfriendOf");


# This section is for appending the data to the database.
sub new_record {
my $dbh;
$dbh = CreateObject OLE "ADODB.Connection" or die "Can't create connec
+tion to DataBase: $!" unless $dbh;
$dbh->Open("Driver={Microsoft Access Driver (*.mdb)};DBQ=_private/db1.
+mdb");
my $rs = CreateObject OLE "ADODB.Recordset" or die "can not create rec
+ordset"; 

my $sql;
    
$sql = "INSERT INTO $formfriend (email,password) VALUES ($formemail,$f
+ormpass)";
$rs = $dbh->Execute( $sql ) or die print ( "Can't execute SQL statemen
+t. Perl says $!, DBI says ",$dbh::errstr,"\n");



}

 print &PrintHeader;
 print &HtmlTop ("Success!");
 new_record();
 print "Email: $formemail<br>\n";
 print "Password: $formemail<br>\n";
 print "Friend Of: $formfriend<br>\n";
 print &HtmlBot;

[download]

Replies are listed 'Best First'.
Re: Help needed to insert data into MS Access database table........
by sutch (Curate) on Dec 18, 2000 at 22:10 UTC
    My guess is that the database is choking on some characters (such as @ in the email address). I don't use ADODB, but there is probably some sort of quoting mechanism to handle special characters.
[reply]
      You are absolutely correct about the @ character. Microsoft Access' query engine will now allow you to use it unless it is either within a single or double quoted string, such as the following: 
      INSERT INTO foobaric (email,password) VALUES ("foobar@foobaric.com","a
+mbidexterous");

      [download]
      I don't know why this code is requiring cgi-lib2.pl and CGI, since similar header and footer functions are part of CGI as well.
[reply]
[d/l]
Re: Help needed to insert data into MS Access database table........
by davemabe (Monk) on Dec 18, 2000 at 23:14 UTC
    You might try using the most recent version of Win32::OLE instead of plain ol' OLE. The OLE module you are using is no longer maintained. I started out the same way as you have and eventually converted everything to Win32::OLE and several of my problems were resolved.

    Dave
[reply]
Re: Help needed to insert data into MS Access database table........
by c-era (Curate) on Dec 18, 2000 at 22:24 UTC
    Adding single quotes around the values sometimes helps me: 
    $sql = "INSERT INTO $formfriend (email,password) VALUES ('$formemail',
+'$formpass')";

    [download]
    It's worth a try at least.
[reply]
[d/l]
      THANKS A TON!!! THAT MADE IT WORK!! I owe you BIG time! THANK YOU ALL! Surya
[reply]
Re: Help needed to insert data into MS Access database table........
by wardk (Deacon) on Dec 18, 2000 at 22:24 UTC

    You are not getting the error message from DBI.

    Use $DBI::errstr to print the error.

    Update: Also, in your post yesterday, you had 4 tables. No you have one. did you mean to add the additional columns to the insert (assuming you added the identifier column to replace the 4 tables). perhaps you are missing your primary key on the insert?

    Update2daveorg nailed the first issue... how about a 

    use DBI;

    [download]
    for starters.
[reply]
[d/l]

      That won't achive anything as he's not using DBI. This can, of course, be seen as a major problem with the script :-)

      --
      <http://www.dave.org.uk>

      "Perl makes the fun jobs fun
      and the boring jobs bearable" - me

[reply]
        No, but he is using OLE with adodb, so he doesn't need to use DBI.
[reply]

Back to Seekers of Perl Wisdom