MS Access/ODBC error: why??

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

Dear All:

I have the following code in my program, which I use to transfer data from some webpages to an Access table,

$sqlinsert="INSERT INTO $Table[$II] VALUES ('$ID','$Itemname','$Firstb
+id','$Endingprice')";
        $rc=$db->Sql($sqlinsert);
                die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc;
[download]

To some webpages, I got the following error message,

SQL fail "1":-3100[Microsoft][ODBC Microsoft Access Driver] Syntax err
+or (missing operator) in query expression '' 1800's HAND EMBROIDERED 
+MOTTO [I SLEPT...] NR  ','US $9.95','>US $36.72')'.10n at H:\strategy
+trying.pl line 258, <INP> chunk 21.
[download]

What looks weird to me is that to some webpages, I do not get the error message. And the data can be transfered to the Access table.

Can anybody tell me what the problem is? Thank you in advance.....luoina

20040220 Edit by Corion: Added formatting

20040220 Edit by BazB: Changed title from 'Why I got this error message?'

Comment on MS Access/ODBC error: why?? Select or Download Code

Replies are listed 'Best First'.
Re: MS Access/ODBC error: why?? by Abigail-II (Bishop) on Feb 20, 2004 at 10:46 UTC
Ah, the joy of interpolation variables into SQL. If one of your variables contains a quote, the resulting string is likely to not be a valid SQL command, and hence, your database will barf. The solution is to use placeholders. Like this: `# Assume 'RaiseError' is set, and 'AutoCommit' is off. eval { my $sth = $db -> prepare (<<" --") INSERT INTO $Table[$II] VALUES (?, ?, ?, ?); -- $sth -> execute ($ID, $Itemname, $Firstbid, $Endingprice); }; if ($@) { $dbh -> rollback; die $@; } else { $dbh -> commit; }` [download] This assumes that `@Table` contains valid table names. Abigail	[reply] [d/l]
Re: MS Access/ODBC error: why?? by Skeeve (Parson) on Feb 20, 2004 at 08:43 UTC
Most certainly it's because of an apostrophe in one of your values. unfortunately your posting is very difficult to read as you didn't use <code> tags	[reply]
Re: MS Access/ODBC error: why?? by pelagic (Priest) on Feb 20, 2004 at 09:07 UTC
Why don't you just print out `$sqlinsert` and analyze what you really feed into sql? I'm sure, i'd be completey clear then ... pelagic ------------------------------------- I can resist anything but temptation.	[reply] [d/l]