• Registered: 2014-09-04
  • Posts: 98

Topic: Can I run a sql query in a script

for example:

in my form I want to do an onchange on a combobox
it should run a sql query on that table to get the whole row from the database into an array, then write those to the correct fields in the form.

I can set a variable and do math on it and then put the results in the fields already so I really just need to figure out how to get the database row that matches the value seleced in the combobox so I can get the values that I need to manipulate into the script.

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

Re: Can I run a sql query in a script

You should use function SQLExecute ('SQL query here');
but using SQLExecute you can get only one value from database.

example:

procedure Form1_ComboBox1_OnChange (Sender: string);
var
    Value1, Value2, Value3: string;
begin
    Value1 := SQLExecute('SELECT field1 FROM table WHERE id=' + IntToStr(Form1.ComboBox1.dbItemID) );
    Value2 := SQLExecute('SELECT field2 FROM table WHERE id=' + IntToStr(Form1.ComboBox1.dbItemID) );
    Value3 := SQLExecute('SELECT field3 FROM table WHERE id=' + IntToStr(Form1.ComboBox1.dbItemID) );

    Form1.Edit1.Text := Value1;
    Form1.Edit2.Text := Value2;
    Form1.Edit3.Text := Value3;
end;
Dmitry.
  • Registered: 2014-09-04
  • Posts: 98

Re: Can I run a sql query in a script

thank you very much!

  • Registered: 2014-09-04
  • Posts: 98

Re: Can I run a sql query in a script

It is working very good except for a date field - I am trying to figure out if it is a data type or what the problem is

I get an error Undeclared identifier 'Text' at 32:37  which is

var
   Ccapacity, Ctare, Cdotdate: string;

begin
     Ccapacity := SQLExecute('SELECT ContainerCapacity FROM Containers WHERE id=' + IntToStr(FillAContainer.Containerid.dbItemID) );
     Ctare := SQLExecute('SELECT ContainerEmpty FROM Containers WHERE id=' + IntToStr(FillAContainer.Containerid.dbItemID) );
     Cdotdate := SQLExecute('SELECT DOTInspDate FROM Containers WHERE id=' + IntToStr(FillAContainer.Containerid.dbItemID) );

     FillAContainer.contcapacity.Text := Ccapacity;
     FillAContainer.prodTare.Text := Ctare;
     FillAContainer.dotinspDate.Text := Cdotdate;
end;

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

Re: Can I run a sql query in a script

Please, change
FillAContainer.dotinspDate.Text := Cdotdate;
to
FillAContainer.dotinspDate.DateTime := SQLDateTimeToDateTime(Cdotdate);

Dmitry.
  • Registered: 2014-09-04
  • Posts: 98

Re: Can I run a sql query in a script

Thank you!