使用数组的SQL PHP UPDATE查询

Question

I am trying to update an MS Access table using an array here is my code

for($i = 1; $i<=$rows; $i++)
{
    $seedsize[$i]      = $_POST['packageType'.$i];
    $RefNo[$i]         = $_POST['field'.$i];
    (int)$qtyOrder[$i] = $_POST['ordered'.$i];
    (int)$qtyDel[$i]   = $_POST['delivered'.$i];
    $unitPrice[$i]     = $_POST['unitPriceDtl'.$i];
    $ref2[$i]          = $_POST['grade'.$i];
    //$date[$i]        = $_POST['myDate'.$i];
}

$InvoiceID = $_SESSION['InvoiceID'];

$sql = "UPDATE 
            [Tbl_Invoice_Details]
        SET 
            [Seed Size]=?, 
            [RefNo]=?, 
            [Quantity Ordered]=?,
            [Quantity Delivered]=?, [Ref2]=?,
            [PricePerUnit]=?
        WHERE 
            [Invoice_ID]=?";

    $data = $conn2->prepare($sql);
    for($x = 1; $x <= 4; $x++)
    {
        $data->execute(array($seedsize[$x], $RefNo[$x], $qtyOrder[$x], $qtyDel[$x], $unitPrice[$x], $ref2[$x], $InvoiceID));
        echo $RefNo[$x]."<br/>"; 
    }

I am getting this error "Invalid character value for cast specification: -3030 [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression."

Thank you for your help!

Answer 1

Have you confirmed that there isn't any null values in the data? Null values might be causing the cast error.

I would suggest to cast all numeric values:

for($i = 1; $i<=$rows; $i++)
{
    $seedsize[$i] = $_POST['packageType' . $i]; // Not sure about this one
    $RefNo[$i] = (int)$_POST['field' . $i]; 
    $qtyOrder[$i] = (int)$_POST['ordered' . $i];
    $qtyDel[$i] = (int)$_POST['delivered' . $i];
    $unitPrice[$i] = (float)$_POST['unitPriceDtl' . $i];
    $ref2[$i] = $_POST['grade' . $i]; // Not sure about this one
    //$date[$i] = $_POST['myDate' .$i];
}

Please note that the cast should be on the right side of the assignment operator.