Recent

Author Topic: sqlite database for administration  (Read 2211 times)

JLommen

  • Newbie
  • Posts: 4
sqlite database for administration
« on: March 27, 2018, 05:26:30 pm »
Hi all,

The last couple of weeks I have been struggling with the creation of an administration program for a friends private company. The purpose of this program is to connect to an SQLite database, show the data of the people using a dbgrid and be able to add or delete files. However, I struggle to find a good guide and started using many different guides to get a result. I don't seem to find a solution on how to insert files into the database using Lazarus. The methods I tried are shown below. ( excuse me for my variables I am Dutch and for the enormous lines.)

SQLquery1.SQL.Add(Format('Insert into
IZHdata(Relatie_code,Achternaam,Voornaam,Geboortedatum,Straat,Postcode,Plaats,gsm,telnr,emailadress,aantal_behandelingen) values("%s","%s","%s","%s","%s","%s","%s","%d","%d","%s","%d")', [Edit1.text, Edit2.text, [/font]Edit3.text,Edit10.text,Edit4.text,Edit5.text,Edit6.text,Edit7.text,Edit8.text,Edit9.text,Edit11.text]));
[/i]

SQLquery1.SQL.Add('Insert into IZHdata(Relatie_code,Achternaam,Voornaam,Geboortedatum,Straat,Postcode,Plaats,gsm,telnr,emailadress,aantal_behandelingen) values('+QuotedStr(Edit1.Text)+','+QuotedStr(Edit2.Text)+','+QuotedStr(Edit2.Text)+','+QuotedStr(Edit10.Text)+','+QuotedStr(Edit4.Text)+','+QuotedStr(Edit5.Text)+','+QuotedStr(Edit6.Text)+','+QuotedStr(Edit7.Text)+','+QuotedStr(Edit8.Text)+','+QuotedStr(Edit9.Text)+')');

This is my first time on the forum so excuse me if I have created my post a bit messy.
Do you know a solution to this problem or could help me with a decent guide on SQLite and Lazarus?
Thank you for your help.

sincere,
JLommen

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: sqlite database for administration
« Reply #1 on: March 27, 2018, 06:31:56 pm »
I do not understand the problem exactly. To make things a bit more clear you do not append commands to an existing query each query can handle only one command as a generic rule there are occasions that mutiple commands can be executed but its a bit down the road for you, so use sqlQuery1.SQL.Text := .........
after a command has been set to the query you need to other call open if the command returns data (select) or execute if the command does not return any data (delete, update, insert etc) and at the end of your processing you need to call commit to persist the changes to the database.

Code: Pascal  [Select]
  1. sqlquery1.Append;
  2. sqlquery1.FieldByName('Relatie_code').asstring := Edit1.Text;
  3. .
  4. .
  5. .
  6. .
  7. sqlquiery1.applyupdates;
  8. sqlquery1.commit;
  9.  

Be warned the code above was keyed in directly on the browser and not tested if you can't make it work post an error message so we can see what went wrong.
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

JLommen

  • Newbie
  • Posts: 4
Re: sqlite database for administration
« Reply #2 on: March 27, 2018, 08:40:31 pm »
Hi Taazz and thank you for your fast reply.

I have tried to understand what you said and I have come up with the following code.

SQLite3Connection1.DatabaseName:='Database1.sqlite';
 SQLite3Connection1.Transaction:=SQLTransaction1;
 SQLTransaction1.Database:=SQLite3Connection1;
 SQLQuery2.Database:=SQLite3connection1;
 SQLQuery2.Transaction:=SQLTransaction1;


 SQLite3Connection1.Connected := True;
 SQLQuery2.SQL.Clear;
 SQLQuery2.open;


 SQLQuery2.SQL.Add('Insert into IZHdata(Relatie_code,Achternaam,Voornaam,Geboortedatum,Straat,Postcode,Plaats,gsm,telnr,emailadress,aantal_behandelingen) values(:Relatie_code,:Achternaam,:Voornaam,:Geboortedatum,:Straat,:Postcode,:Plaats,:gsm,:telnr,:emailadress,:aantal_behandelingen');
 SQLQuery2.Params.paramByName('Relatie_code').AsString := Edit1.Text;
 SQLQuery2.Params.paramByName('Voornaam').AsString := Edit2.Text;
 SQLQuery2.Params.paramByName('Achternaam').AsString := Edit3.Text;
 SQLQuery2.Params.paramByName('Geboortedatum').AsString := Edit10.Text;
 SQLQuery2.Params.paramByName('Straat').AsString := Edit4.Text;
 SQLQuery2.Params.paramByName('Postcode').AsString := Edit5.Text;
 SQLQuery2.Params.paramByName('Plaats').AsString := Edit6.Text;
 SQLQuery2.Params.paramByName('gsm').AsInteger := Edit7.Text;
 SQLQuery2.Params.paramByName('telnr').AsInteger := Edit8.Text;
 SQLQuery2.Params.paramByName('emailadress').AsString := Edit9.Text;
 SQLQuery2.Params.paramByName('Aantal_behandelingen').AsInteger := Edit11.Text;
 SQLQuery2.ExecSQL;
 SQLTransAction1.Commit;
 SQLQuery2.Close;


It doesn't seem to recognize the integer as an integer. Could you also check if I have done the rest of the code correctly?

Again thanks for your help.

JLommen

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: sqlite database for administration
« Reply #3 on: March 28, 2018, 01:12:39 am »
well not what I was going for but close enough. I assume that sqlQuery2 is not linked with any DB controls and its there to only execute the commands, in that case you do not need call open at all.
as for the last parameter asinteger you need to keep in mind that Tedit only handles strings so you to convert it to an integer eg
Code: Pascal  [Select]
  1. SQLite3Connection1.DatabaseName:='Database1.sqlite';
  2.  SQLite3Connection1.Transaction:=SQLTransaction1;
  3.  SQLTransaction1.Database:=SQLite3Connection1;
  4.  SQLQuery2.Database:=SQLite3connection1;
  5.  SQLQuery2.Transaction:=SQLTransaction1;
  6.  
  7.  
  8.  SQLite3Connection1.Connected := True;
  9.  SQLQuery2.SQL.Clear;
  10. SQLQuery2.SQL.clear; //<------ make sure that only one command is in the sql.
  11.  
  12.  SQLQuery2.SQL.Add('Insert into IZHdata(Relatie_code,Achternaam,Voornaam,Geboortedatum,Straat,Postcode,Plaats,gsm,telnr,emailadress,aantal_behandelingen) values(:Relatie_code,:Achternaam,:Voornaam,:Geboortedatum,:Straat,:Postcode,:Plaats,:gsm,:telnr,:emailadress,:aantal_behandelingen');
  13.  SQLQuery2.Params.paramByName('Relatie_code').AsString := Edit1.Text;
  14.  SQLQuery2.Params.paramByName('Voornaam').AsString := Edit2.Text;
  15.  SQLQuery2.Params.paramByName('Achternaam').AsString := Edit3.Text;
  16.  SQLQuery2.Params.paramByName('Geboortedatum').AsString := Edit10.Text;
  17.  SQLQuery2.Params.paramByName('Straat').AsString := Edit4.Text;
  18.  SQLQuery2.Params.paramByName('Postcode').AsString := Edit5.Text;
  19.  SQLQuery2.Params.paramByName('Plaats').AsString := Edit6.Text;
  20.  SQLQuery2.Params.paramByName('gsm').AsInteger := Edit7.Text;
  21.  SQLQuery2.Params.paramByName('telnr').AsInteger := Edit8.Text;
  22.  SQLQuery2.Params.paramByName('emailadress').AsString := Edit9.Text;
  23.  SQLQuery2.Params.paramByName('Aantal_behandelingen').AsInteger := strtoint(Edit11.Text);
  24.  SQLQuery2.ExecSQL;
  25.  SQLTransAction1.Commit;
  26.  SQLQuery2.Close;
  27.  
that should get you going.
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

JLommen

  • Newbie
  • Posts: 4
Re: sqlite database for administration
« Reply #4 on: March 28, 2018, 05:06:27 pm »
Thanks again for your help,

With debugging no mistakes are found but when I fill in the textboxes and press the button I get an error message saying 'sqlite3connection1: input incomplete'. When searching this on google I found no answers to this problem.

I'm still in search of a good guide in order to learn this properly. A functioning example program would be very nice.

Sincere,
JLommen

Hansvb

  • Sr. Member
  • ****
  • Posts: 347
Re: sqlite database for administration
« Reply #5 on: March 28, 2018, 10:11:11 pm »
I think that line 12 is wrong. You are missing a parenthesis. Insert into (...) values (.....). The last parenthesis is missing

JLommen

  • Newbie
  • Posts: 4
Re: sqlite database for administration
« Reply #6 on: March 29, 2018, 09:22:03 am »
You are a legend!

The code finally seems to work :D

Thanks, Taazz for the great help and of course Hansvb for the final touch.

JLommen