• Registered: 2015-12-25
  • Posts: 211

Topic: Please, Help with a few questions :)

Hello.
First of all, i'm very very very sorry to bother everyone in the forum.
I probably should say this but i am not a programmer, at least not before i started using my visual database, i started learning a little bit about delphi and sql,  but i am still newbie , and i don't have a professional method to do what i want.

Saying this, i am stuck in a part of my project.

Here's what i need to know:

- How do i retrieve all id's from a tablegrid?

i know sqlvalue gives me the id for the selected row, but how do i retrieve all the id's from one tablegrid?

- How do i count multiple quantities with SQL?

i know that i can use SQLExecute('SELECT COUNT(Quant) FROM stock WHERE upper(Quant)=upper("'+id+'"); (Where ID var is the SQLValue
But this only gives me one count, can i use something like Where upper(quant)=upper(1,3,7,14 etc...?); And if i can do this, how can i then count the individual quantities to check if they are below 0?

- How do i update multiple quantities into SQL?

After counting the avaiable stock, i will want to update the values with the new quantities, if so, can i use commas to seperate the diferent id's ?
something like: SqlExecute('Update Stock Set Quant ' 1,2,3,5 Where Id = 1,3,7,14); and if i do this will id 1 have 1 quantity, id 3 have 2 quant, id 7 have 3 and id 14 have 5 ? or will this not work?


After knowing all this, all i need to know, is how can i use this new info to update the stock when someone accepts an invoice.
That's all i need to finaly finish my project! big_smile

Please help me, and i'll be eternatly grateful!

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Please, Help with a few questions :)

Hello.


- How do i retrieve all id's from a tablegrid?

procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    i, c: integer;
    id: integer;
begin
    c := Form1.TableGrid1.RowCount-1;
    for i := 0 to c do
    begin
        id := Form1.TableGrid1.dbIndexToID(i);
    end;
end;


How do i count multiple quantities with SQL?

 SQLExecute('SELECT COUNT(Quant) FROM stock WHERE id IN (1, 4, 7, 9)


- How do i update multiple quantities into SQL?

UPDATE tablename SET Quant=4 WHERE id IN (1, 4, 7, 9)



SqlExecute('Update Stock Set Quant ' 1,2,3,5 Where Id = 1,3,7,14); and if i do this will id 1 have 1 quantity, id 3 have 2 quant, id 7 have 3 and id 14 have 5 ? or will this not work?

You can do it only with separate queries:

Update Stock Set Quant=1 Where Id = 1
Update Stock Set Quant=2 Where Id = 3
Update Stock Set Quant=3 Where Id = 7
Dmitry.
  • Registered: 2015-12-25
  • Posts: 211

Re: Please, Help with a few questions :)

hi Dmitry.

Would  SQLExecute('SELECT COUNT(Quant) FROM stock WHERE id IN (1, 4, 7, 9) work if i use the var id in Where id in +id ?

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Please, Help with a few questions :)

SQL query it's just text, so you should make necessary text to archive it. Example:

var
   id: string;
begin
   id := '1, 4, 7, 9';
   SQLExecute('SELECT COUNT(Quant) FROM stock WHERE id IN ('+id+')');
Dmitry.
  • Registered: 2015-12-25
  • Posts: 211

Re: Please, Help with a few questions :)

that's it! many thanks

  • Registered: 2019-12-20
  • Posts: 26

Re: Please, Help with a few questions :)

help me . stock .+.- qty script

  • Registered: 2019-12-20
  • Posts: 26

Re: Please, Help with a few questions :)

help me . stock + qty      -qty  total scripthelp me . stock + qty      -qty  total script

  • Registered: 2019-12-20
  • Posts: 26

Re: Please, Help with a few questions :)

stock update qty exampl