How to insert more than one column into Access database table?

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

Hi, Perlmonks, I am quite new in Perl. I am trying to insert into Access database table one row, more than one columns of data. But I did not succeed. Here is the script and the debug result.

 #script 1: 
        use Win32::ODBC;
        $DSN="Alex";
        $DATABASE_NAME="Edabase";


        unless ($db=new Win32::ODBC($DSN)) {
                print " Error: Cant't open database [$DATABASE_NAME] !
+.\n";
        exit(8);
        }
        print "database [$DATABASE_NAME] successfully opened. \n";

$ID=1234567890;
$Folder="Product";
$Webpage="B2";

 $sqlinsert ="INSERT INTO T_table VALUES($ID,$Folder, $Webpage)";

        $rc=$db->Sql($sqlinsert);
                die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc;
                
              
        $db->Close();
[download]

Debug result: database Edabase successfully opened. SQL fail "1":-3010Microsoft ODBC Microsoft Access drive insufficient parameters，expected is 2. 10n at C:\WINDOWS\Desktop\trydbi2.pl line #. Process terminated with exit code 0 I also tried another way:

 #script 2:

  use Win32::ODBC;
        $DSN="Alex";
        $DATABASE_NAME="Edabase";


        unless ($db=new Win32::ODBC($DSN)) {
                print " Error: Cant't open database [$DATABASE_NAME] !
+.\n";
        exit(8);
        }
        print "database [$DATABASE_NAME] successfully opened. \n";

$ID=1234567890;
$Folder="Product";
$Webpage="B2";

 INSERT INTO T_table VALUES($ID,$Folder, $Webpage0;
 $db->Close();
[download]

Debug result: ......forgot to load "values"?.... I can not understand that. Please help me. Helen Cook

Comment on How to insert more than one column into Access database table? Select or Download Code

Replies are listed 'Best First'.
Re: How to insert more than one column into Access database table? by Rich36 (Chaplain) on Oct 08, 2002 at 21:30 UTC
It might help to specify the column names in the INSERT query. If you have more columns than the number of values you are inserting, that can cause problems. Try this and specify your column names. `$sqlinsert ="INSERT INTO T_table (Column1, Column2, Column3) VALUES($I +D,$Folder, $Webpage)";` [download] This page contains some good SQL references. «Rich36»	[reply] [d/l]
Thanks a lot, Rich36 by Yelenna2003 (Initiate) on Oct 17, 2002 at 01:27 UTC
Thanks a lot, Rich36. Yelenna2003	[reply]
Re: How to insert more than one column into Access database table? by robartes (Priest) on Oct 08, 2002 at 21:32 UTC
In your SQL statement, you have to specify which colums to insert the values in: `INSERT INTO T_table (col1,col2,col3) VALUES($ID,$Folder, $Webpage)` [download] Your script 2 is trying to pass off SQL as genuine Perl but is not fooling the compiler at all, hence the error for script 2. CU Robartes-	[reply] [d/l]
Thanks, but it does not work. by Yelenna2003 (Initiate) on Oct 08, 2002 at 21:59 UTC
Hi, Thanks. But I have only three columns in T_table: ID,Folder, Webpage. When I change the SQL statement like this: INSERT INTO T_table (ID, Folder, Webpage) VALUES ($ID, $Folder, $Webpage). The compiler tells me there is a syntax error: bareword found near ")VALUES". I do not know why. Helen	[reply]
Re: Thanks, but it does not work. by runrig (Abbot) on Oct 08, 2002 at 22:12 UTC
You need to quote non-numeric values when using SQL that way. Better would be to use placeholders: `my $sth = $dbh->prepare("insert into t_table (id, folder, webpage) values (?,?,? +)"); $sth->execute($id, $folder, $webpage);` [download] Update:Oops. /me notices you are using Win32::ODBC. Ignore the above code then, unless you switch to DBI. You still need to put quotes around any non-numeric values though...	[reply] [d/l]
Re: Thanks, but it does not work. by rdfield (Priest) on Oct 09, 2002 at 09:09 UTC
You're trying to modify your second code version. The addition of quotes around the non-numeric data recommended by the other monks should be done in the first code sample you posted, ie `$sqlinsert ="INSERT INTO T_table (T_tableID, Folder, Webpage) VALUES($ID,'$Folder', '$Webpage')";` rdfield	[reply] [d/l]