Unexpected T_VARIABLE

[system]

Hi I’m pretty new to all this. I’m trying to create a form to link back to my database but at the moment I keep getting an error about a “Fatal error: Uncaught exception ‘com_exception’ with message 'Source: Microsoft JET Database Engine Description: Syntax error (missing operator) in query expression” around where my Update and Insert SQL statements are.
I’m pretty sure this means I’ve just messed up some of the quotes but I cant seem to work it out, so any help would be appreciated.

Of the 5 Access columns ‘Cust_No’ is an ‘auto number’ field, and all the rest are text.

Here’s the relevant code:
[php]
if ($errorcode==0)
{
if (strlen($Cust_No)>0)
{
$conn=new COM(“ADODB.Connection”) or exit(“Cannot start ADO.”);
$conn->Open(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”. realpath(“amazonia280.mdb”));
if ($Cust_No=="(System Specified)")
{
$rd=new COM(“ADODB.Recordset”);
$rs->open(“Select Max(Cust_No) as pid From Customer”,$conn);
if ($rs->EOF)
$Customer=“1”;
else if (strlen($rs->Fields[“pid”]->value)>0)

   {
    $intpart=intval(substr($rs->Fields["pid"]->value,1));
    $intpart++;
    $Cust_No="".$intpart;
      while (strlen($Cust_No)<2)
       $Cust_No="0".$Cust_No;
       $Cust_No="p".$Cust_No;
   }
else
  $Cust_No="1";
  $rs->close();


      $conn->execute  ("Insert Into Customer (Cust_No,Cust_Name,Address,email,telno) ".
                                 "Values ($Cust_No,".
                                 "$Cust_Name,".
                                 "$Address,".
                                 "$email,".
                                 "$telno)");
     }
    else
     {
      $conn->execute ("Update Customer ".
                      "Set Cust_Name=".$Cust_Name.",".
                         "Address=".$Address.",".
                         "email=".$email.",".
                         "telno=".$telno." ".
                         "Where Cust_No=".$Cust_No.""); 
     }
    $conn->close();
    echo "Saved Customer $Cust_No";
       $Cust_No="";
       $Cust_Name="";
       $Address="";
       $email="";
       $telno="";
   } 
 }

}
[/php]

Thanks

[steamindaemon]

Try putting single quotes around the strings in your insert/update code. For instance

[php]$conn->execute (“Update Customer “.
“Set Cust_Name=’”.$Cust_Name.”’,”.
“Address=’”.$Address."’,".
“email=’”.$email."’,".
“telno=’”.$telno."’ “.
“Where Cust_No=”.$Cust_No.”"); [/php]

[kat]

Thanks so much that worked perfectly. I’m now having a very similar problem on a different page. I’m getting th error Fatal error: Uncaught exception ‘com_exception’ with message ‘Source: Microsoft JET Database Engine Description: Syntax error in UPDATE statement.’ I have tried lots of different combinations with single and double quotes but can’t get it right, I think it may have something to do with the date? A similar error comes up for the ‘Insert Into’ statement as well.

[php]
else
{
$errortotal=0;
for ($x=0;$x<$noitems;$x++)
{
$errortotal+=$errorarray[$x];
if ($errortotal==0)
{
$conn=new COM(“ADODB.Connection”) or exit(“Cannot start ADO.”);
$conn->Open(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”. realpath(“mydatabase.mdb”));
if ($Invoice_No=="(System Specified)")
{
$rs=new COM(“ADODB.Recordset”);
$rs->open(“Select Max(Invoice_No) as mx from Invoice”,$conn);
if ($rs->EOF)
$Invoice_No=“1”;
else if (($rs->Fields[“mx”]->value)==0)
$Invoice_No=“1”;
else
{
$invoicep=intval($rs->Fields[“mx”]->value);
$invoicep++;
$Invoice_No="".$invoicep;
while (($Invoice_No)<1)
$Invoice_No=“0”.$Invoice_No;
}
$rs->close();

            $conn->execute("Insert Into Invoice (Invoice_No, Date, Cust_No, ShippingAddress, BillingAddress) ".
                        "Values($Invoice_No,#".$Date."#,".$Cust_No.",'".$ShippingAddress."','".$BillingAddress."')");  //error occurs here

           } 
         else
           {
            $conn->execute("Update Invoice ".
                                     "Set Date='".$Date."', ".
                                         "Cust_No=".$Cust_No.", ".
                                         "ShippingAddress='".$ShippingAddress."', ".
                                         "BillingAddress='".$BillingAddress."' ".
                                   "Where Invoice_No=$Invoice_No"); //error occurs here
           }
         $conn->execute("Delete From Invoice_Item Where Invoice_No=$Invoice_No");
         $y=0;
         for ($x=0;$x<$noitems;$x++)
           {
            if (strlen($itemnoarray[$x])>0)
             {

              $conn->execute("Insert Into Invoice_Item (Invoice_No, Qty, ISBN, [Sale Price] as SalePrice) ".
                             "Values (".$Invoice_No.",".$y.",".$itemnoarray[$x].",".$pricearray[$x].")");
              $y++;
             }

           }  
         $conn->close();
         echo "Saved invoice $Invoice_No";
         $Invoice_No="";
         $Cust_No="";
         $Cust_Name="";
         $Address="";
         $noitems=0;
         $total=0;
        }

      }
   }

[/php]

[steamindaemon]

OK, so for this part:

[php]$conn->execute(“Insert Into Invoice (Invoice_No, Date, Cust_No, ShippingAddress, BillingAddress) “.
“Values($Invoice_No,#”.$Date.”#,”.$Cust_No.",’".$ShippingAddress."’,’".$BillingAddress."’)"); //error occurs here
[/php]

What is the $Date variable set to? From what you posted, you are not actually setting that variable to anything.

If you’re not already setting a specific date, you could try this:

[php]$Date = date(‘m/d/Y’);[/php]

making sure it appears just before your INSERT INTO line.

As for the UPDATE statement, it looks like you may not be setting the $Invoice_No variable. Try adding a line just before the UPDATE statement:

[php]print(var_export($Invoice_No), true);[/php]

and see what that gives you. It should tell you if the variable is set to anything; and if so, what it is set to. If it’s not set, you’ll need to rework your logic for that section of the code.

As a suggestion, you might want to look at writing functions or classes to handle some of this code, as I can see it’s getting tricky to follow.