Get data from DB to Edit box and visa versa
lazarus / 16-Databases

----------------------------------------------------------------------
EdmundasLT · 2014-09-15 18:58

Hello, im new in programing and making a lot of mistakes. But this is
some other problem. I need help with making a database application
which takes data from Edit# boxes like Name, Surname, City etc and
tharfers that data to Firebird database. After that I want to use
another Form to read DB entries and print to Edit# boxes. I already
know how to read and print from and to Edit# boxes, but writing and
reading from DB slips by me. And I cant figure it Out.

Firebird database table:
http://s3.postimg.org/3kcn9d203/Firebird_duomenu_bazes_lentele.png
Form for data entry:
http://s3.postimg.org/e5rx7mixv/Form2_duomenu_ivedimui.png

Thanks for your help.

P.S. Sorry It's in my language, but you get a point

----------------------------------------------------------------------
Never · 2014-09-15 19:10

hello @ EdmundasLT
This will kick you started
[ http://wiki.freepascal.org/firebird ]
[ http://wiki.freepascal.org/SQLdb_Tutorial2 ]<---Ckeck at the bottom
to follow the tut from begining if you want

----------------------------------------------------------------------
EdmundasLT · 2014-09-17 17:17

Hello again  I have this code, it compiles without error, but it
doesnt insert data to my Database. Where could be a problem?

Code: [Select]procedure TForm2.Button1Click(Sender: TObject);

var
  vardas: String;
  pavarde: String;
  kk: STRING;
  ak: String;
  miestas: String;
  tel: String;
  email: String;
begin
   vardas:=Edit1.Text;
   pavarde:=Edit2.Text;
   kk:=Edit3.Text;
   ak:=Edit4.Text ;
   miestas:=ComboBox1.Text;
   tel:=Edit5.Text;
   email:=Edit6.Text;

      SQLQuery1.SQL.Clear;
      SQLQuery1.SQL.Add ('INSERT INTO KLIENTAI');
      SQLQuery1.SQL.Add ('(VARDAS, Pavarde, kk, ak, Miestas, tel,
email )');
      SQLQuery1.SQL.Add ('VALUES
(:vardas,:pavarde,:kk,:ak,:miestas,:tel,:email)');
      SQLQuery1.Params.ParamValues['vardas']:=Edit1.Text;
      SQLQuery1.Params.ParamValues['pavarde']:=pavarde;
      SQLQuery1.Params.ParamValues['kk']:=kk;
      SQLQuery1.Params.ParamValues['ak']:=ak;
      SQLQuery1.Params.ParamValues['miestas']:=miestas;
      SQLQuery1.Params.ParamValues['tel']:=tel;
      SQLQuery1.Params.ParamValues['email']:=email;

      SQLQuery1.ExecSQL;

end;

----------------------------------------------------------------------
taazz · 2014-09-17 17:26

you forgot the command Code: [Select]   SQLQuery1.Transaction.commit;
after the execsql call.

----------------------------------------------------------------------
Stygian · 2014-09-17 17:29

If you want to insert datas into a database first you have to put it
in "edit mode".
I suggest install zeosdb, beacuse if you want to use firebird you have
to manually write all of the sql command(read, write...etc). Zeos do
this for you.

Writin into a database someting like this:

Code: [Select]Zconnection.Append;
Zmyquery.FieldByName('NAME').AsString := Edit1.Text;
Zconnection.Post;

----------------------------------------------------------------------
EdmundasLT · 2014-09-17 18:13

Thank you very much. SQLTransaction1.Commit; command worked lika a
charm
Stygian - I have to use Firebird  but thanks, for personal use I will
try ZeosDB.

One more quick question if you dont mind my silly questions:
How to make a scrollbox to display database entries?

My try gives no errors but list is empty.

Code: [Select]procedure TForm3.ScrollBox1Click(Sender: TObject);
var
  LCount: String;
begin
      SQLQuery1.SQL.Clear;
      SQLQuery1.SQL.Add ('Select VARDAS,PAVARDE');
      SQLQuery1.SQL.Add ('FROM KLIENTAI');
      SQLQuery1.Open;
      LCount := SQLQuery1.Fields[0].AsString;
      ScrollBox1.Caption :=  LCount;
end;

----------------------------------------------------------------------
Mike.Cornflake · 2014-09-17 19:03

Scrollbox doesn't do what you think it does.  It's a control that
provides TScrollbars - that's it. 

Easiest way to present the data is in a grid:
Try dropping a TDatasource on your form.
Connect the TDatasource.Dataset to your TSQLQuery.
Drop a TDBGrid on the form.
Connect the TDBGrid.Datasource to the TDatasource you created in the
first step.

----------------------------------------------------------------------
EdmundasLT · 2014-09-22 18:24

Hi, I managed to find how to create a stored procedure, now i want it
to read data from edit boxes and pass that data to firebird database.

My code compiles without error, but in application if i press  button
to execute Stored procedure i get error:
(http://Photo of the Error I get)

Heres my button code:
Code: [Select]procedure TForm2.Button4Click(Sender: TObject);
var

   vardas: String;
   pavarde: String;
   ak: String;
   miestas: String;
   telefonas: String;
   elpastas: String;
begin
   vardas:=Edit1.Text;
   pavarde:=Edit2.Text;
   ak:=Edit4.Text ;
   miestas:=ComboBox1.Text;
   telefonas:=Edit5.Text;
   elpastas:=Edit6.Text;


   SQLQuery1.SQL.Add ('INSERT INTO KLINSERT');
   SQLQuery1.Params.ParamValues['vardas']:=vardas;
   SQLQuery1.Params.ParamValues['pavarde']:=pavarde;
   SQLQuery1.Params.ParamValues['ak']:=ak;
   SQLQuery1.Params.ParamValues['miestas']:=miestas;
   SQLQuery1.Params.ParamValues['telefonas']:=telefonas;
   SQLQuery1.Params.ParamValues['elpastas']:=elpastas;

  SQLQuery1.ExecSQL;
  SQLTransaction1.Commit;
end;       

And here is stored procedure code:
Code: [Select]SET TERM ^ ;
ALTER PROCEDURE KLINSERT (
    CLIENTID Integer,
    VARDAS Varchar(255),
    PAVARDE Varchar(255),
    AK Varchar(255),
    MIESTAS Varchar(255),
    TELEFONAS Varchar(255),
    ELPASTAS Varchar(255) )
AS
begin
    INSERT INTO KLIENTAI (CLIENTID, VARDAS, PAVARDE, AK, MIESTAS,
TELEFONAS, ELPASTAS)
    VALUES (:CLIENTID, :VARDAS, :PAVARDE, :AK, :MIESTAS, :TELEFONAS,
:ELPASTAS);
end^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE KLINSERT TO  TEST;

Thanks for help in advance, I;m stuck really bad here

----------------------------------------------------------------------
BigChimp · 2014-09-22 18:30

Please open a new thread for a new question...

Also when you do so, do you know what your actual .SQL.Text is?
Here you are adding the insert statement
Code: [Select]SQLQuery1.SQL.Add ('INSERT INTO KLINSERT');
... no idea if the SQL.Text was empty before that.

INSERT INTO KLINSERT is not valid Firebird SQL, I think. Always try
executing the stored procedure in a database tool such as Flamerobin
first to see if the SQL actually works (hint: it probably is something
like EXECUTE KLINSERT (...))

As said, please open a new thread and I'll be happy to have a look for
you...

----------------------------------------------------------------------
EdmundasLT · 2014-09-22 18:46

Thank BigChimp, but I do not think that this is a new question,
because it's goal is to do exacly the same thing as mentioned in
topic, sorry if Im wrong.

I tried executing it in flamerobin and it worked like it should have.
It added data about client to database.

Sorry, but because I have so little knowledge of Delphi I have no Idea
how to answer about .SQL.Text.  I think its empty because after
starting application I immidiately use Button to execute procedure.

If INSERT INTO KLINSERT  if wrong, what should I use to execure
procedute to pass data from edit boxes to DB?

Sorry again if this is wrong topic or thread to ask these questions. 
But didn't wanted to have two threads about the same thing by the same
person.

Thanks

----------------------------------------------------------------------
BigChimp · 2014-09-23 06:14

Of course they are different subjects: first you were asking about
insert statements now you are asking about executing stored
procedures...

(OK, they all have to do with databases, but if that is the criterium,
you can just put all db questions in one big thread).

Thanks.

----------------------------------------------------------------------
EdmundasLT · 2014-09-24 17:19

[SOLVED]   Thanks everyone!