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

Hi! I'm not able to insert data into an MS Access database. The script executes perfectly but the database does not get the new values......... PLEASE HELP :-) THANKS. 
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/d
+b1.mdb");
my $rs = CreateObject OLE "ADODB.Recordset" or die "can not create rec
+ordset"; 

my $sql;
    
    if ($formfriend eq 'surya')
    {
    $sql = "INSERT INTO surya VALUES ($formemail,$formpass)";
    }

    if ($formfriend eq 'rahul')
    {
    $sql = "INSERT INTO rahul VALUES ($formemail,$formpass)";
    }

    if ($formfriend eq 'deepika')
    {
    $sql = "INSERT INTO deepika VALUES ($formemail,$formpass)";
    }

    if ($formfriend eq 'everyone')
    {
    $sql = "INSERT INTO everyone VALUES ($formemail,$formpass)";
    }


    $rs = $dbh->Execute( $sql );

[download]

Replies are listed 'Best First'.
Re: Inserting data into MS Access databse.........
by boo_radley (Parson) on Dec 17, 2000 at 21:27 UTC
    and, based on your snippet, you should also be able to remove most of those if statements, and reduce them to 
    $sql = "INSERT INTO $formfriend VALUES ($formemail,$formpass)";

    [download]
    . Also, 
    $rs = $dbh->Execute( $sql );

    [download]
    can be renooberated thusly : 
    $rs = $dbh->Execute( $sql ) or die ("Can't execute SQL statement. Perl
+ says $!, DBI says ",$DBI::errstr,"\n");

    [download]
    $DBI::errstr will give you a much more descriptive error if something doesn't work in between (I think, please correct if wrong) DBI and your DBD.
[reply]
[d/l]
[select]
Re: Inserting data into MS Access databse.........
by turnstep (Parson) on Dec 17, 2000 at 20:30 UTC

    As pointed out above, it is legal to not specify the columns...but you better be very sure about the exact order and type. At any rate, you could add some simple error checking to see *why* nothing was inserted. Most likely the execute statement will be able to tell you what went wrong:

    $rs = $dbh->execute($sql) or die qq!"$sql" failed: $DBI::errstr\n!;

    [download]

    That should get you started, although I am pretty sure you are sending the wrong types into the tables. When in doubt, specify the columns.

[reply]
[d/l]
Re: Inserting data into MS Access databse.........
by wardk (Deacon) on Dec 18, 2000 at 02:38 UTC

    instead of 4 tables that all appear to look like: 

    create table x {
  formeamil varchar(n),
  formpass  varchar(n)
}
# where x = surya rahul deepika everyone

    [download]
    how about: 
    create table y {
  formfield  varchar(n),
  formeamil varchar(n),
  formpass varchar(n)
}

    [download]

    then you can get rid of the if testing and just do 

    my $dbh;
$dbh = CreateObject OLE "ADODB.Connection" or die "Can't create connec
+tion to DataBase: $!" unless $dbh;  $dbh->Open("Driver={Microsoft Acc
+ess Driver (*.mdb)};DBQ=../_private/db1.mdb");
my $rs = CreateObject OLE "ADODB.Recordset" or die "can not create rec
+ordset"; 
my $sql = "INSERT INTO y (formfield,formemail,formpass) VALUES ($formf
+ield,$formemail,$formpass)";
$rs = $dbh->Execute( $sql );

    [download]

    As for why it's not currently working, you may want to do some error handling on the execute().

    good luck!

[reply]
[d/l]
[select]
Re: Inserting data into MS Access databse.........
by 2501 (Pilgrim) on Dec 17, 2000 at 11:06 UTC
    > $sql = "INSERT INTO surya VALUES ($formemail,$formpass)";

    Just by eyeballing your code your SQL looks abit weird.
    what you want is:
    insert into <table> (<column>,<column2>) values (<value>,<value2>)

    [download]

    Also, It looks like you might have too many tables, or you might want to be updating instead of inserting.
    good luck!
[reply]
[d/l]
[reply]

Back to Seekers of Perl Wisdom