Recent

Author Topic: Get data from DB to Edit box and visa versa  (Read 7520 times)

EdmundasLT

  • New member
  • *
  • Posts: 9
Get data from DB to Edit box and visa versa
« on: September 15, 2014, 08:58:51 pm »
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

  • Sr. Member
  • ****
  • Posts: 409
  • OS:Win7 64bit / Lazarus 1.4
Re: Get data from DB to Edit box and visa versa
« Reply #1 on: September 15, 2014, 09:10:14 pm »
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
« Last Edit: September 15, 2014, 09:12:10 pm by Never »
Νέπε Λάζαρε λάγγεψων οξωκά ο φίλοσ'ς αραεύσε

EdmundasLT

  • New member
  • *
  • Posts: 9
Re: Get data from DB to Edit box and visa versa
« Reply #2 on: September 17, 2014, 07:17:43 pm »
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

  • Hero Member
  • *****
  • Posts: 5365
Re: Get data from DB to Edit box and visa versa
« Reply #3 on: September 17, 2014, 07:26:03 pm »
you forgot the command
Code: [Select]
   SQLQuery1.Transaction.commit; after the execsql call.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Stygian

  • Jr. Member
  • **
  • Posts: 90
Re: Get data from DB to Edit box and visa versa
« Reply #4 on: September 17, 2014, 07:29:16 pm »
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

  • New member
  • *
  • Posts: 9
Re: Get data from DB to Edit box and visa versa
« Reply #5 on: September 17, 2014, 08:13:40 pm »
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

  • Hero Member
  • *****
  • Posts: 1251
Re: Get data from DB to Edit box and visa versa
« Reply #6 on: September 17, 2014, 09:03:32 pm »
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.
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

EdmundasLT

  • New member
  • *
  • Posts: 9
Re: Get data from DB to Edit box and visa versa
« Reply #7 on: September 22, 2014, 08:24:46 pm »
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 :)
« Last Edit: September 23, 2014, 04:33:53 pm by EdmundasLT »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Get data from DB to Edit box and visa versa
« Reply #8 on: September 22, 2014, 08:30:33 pm »
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...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

EdmundasLT

  • New member
  • *
  • Posts: 9
Re: Get data from DB to Edit box and visa versa
« Reply #9 on: September 22, 2014, 08:46:37 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Get data from DB to Edit box and visa versa
« Reply #10 on: September 23, 2014, 08:14:43 am »
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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

EdmundasLT

  • New member
  • *
  • Posts: 9
Re: Get data from DB to Edit box and visa versa
« Reply #11 on: September 24, 2014, 07:19:17 pm »
[SOLVED]   Thanks everyone!  :)

 

TinyPortal © 2005-2018