Topic: Using AND in a sql statement

I have written a procedure that is used to receipt a complete order assuming there are no discrepancies. The amount of items on the order can vary from 1........ Many.


The plain SQL tested in SQL Studio is:

UPDATE orders SET receivedQty = '10', receivedDate = date('now') WHERE orders.orderRef = 'REQ 277/12' AND orders.id = 3;

Now I have converted it to the SQLExecute statement below, but it is not working, it never updates the database. Does MVD work with logical ANDs in the SQL statements? Or will I have to write the above SQL as two lines of code?


procedure receiveOrder_btnReceiveAll_PO_OnClick (Sender: TObject; var Cancel: boolean);
var
 indx, received : integer;
 s :  string;
 ds : TDataset;
begin


   indx := 0; {index counter for iterating through available rows of DB grid}

   { Get the Order ID from Orders table for all matching purchase orders, place result into the dataset}
   sqlQuery('SELECT orders.id FROM orders WHERE orders.orderRef = "' +receiveOrder.cmbPO.Text+'"', ds);

   ds.First; {Make sure we are at the beginning of the dataset}

   {All the work is done here.
    Assign the orders.id to 'S', copy qty ordered to qty received store in 'received' then update the records in ORDERS
    with 'received Qty' the Date received matching the order Id and PO number}
   while not ds.EOF do
   begin
       s := ds.FieldByName('ID').asString;
       received := strToInt(receiveOrder.tgReceiveOrder.Cells[3,indx]);
       sqlexecute('UPDATE orders SET receivedQty = '+ intToStr(received) +', receivedDate = date(''now'') WHERE (orders.orderRef = "'+ receiveOrder.cmbPO.Text +'") AND (+ orders.id = "'+ s +'")');
       showmessage('order Id ' + s); // temporary debug check to make sure order.id is correct
       ds.next; {move to the next record}
       indx := indx + 1; {increment counter for the tablegrid row.}
   end;

   ds.Close;  {Close the dataset and free the database}
   ds.Free;
   showMessage('Complete Purchase Order Receipted');

end;
On a clear disk you can seek forever

Re: Using AND in a sql statement

.

Post's attachments

Attachment icon test.7z 1.21 mb, 584 downloads since 2020-05-30 

Re: Using AND in a sql statement

Thank you sibprogsistem,

As you suggested, I separated the two fields into two statements and that works fine.

On a clear disk you can seek forever