[SOLVED] SQLite Zeoslib delete record
lazarus / 16-Databases

----------------------------------------------------------------------
Pe3s · 2022-03-15 20:06

Hi I wrote a code that is supposed to delete a record, but it displays
an error ZQuery1 Field not found "id"
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button2Click(Sender: TObject);begin   if MessageDlg('Pytanie',
'Czy usunac rekord', mtWarning, mbYesNo, 0) = mrYes then  begin   
ZQuery1.SQL.Text := 'DELETE FROM links WHERE id=:id';   
ZQuery1.ParamByName('id').AsInteger :=
ZQuery1.FieldByName('id').AsInteger;    ZQuery1.ExecSQL;  end;end; 
How can I correct this error ?

----------------------------------------------------------------------
marcov · 2022-03-15 20:28

It is not exactly clear what is wrong. It might be simply reuse of the
zquery component, possibly  setting the new SQL clears the old
results.

Save the result of ID to an integer before you set it, or use a
different query  object.

----------------------------------------------------------------------
Zoran · 2022-03-15 21:21

Yes, setting SQL property clears the dataset. As Marco says, use
another query object.

----------------------------------------------------------------------
dseligo · 2022-03-16 00:29

Do it like this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button2Click(Sender: TObject);var iId: Integer;begin   if
MessageDlg('Pytanie', 'Czy usunac rekord', mtWarning, mbYesNo, 0) =
mrYes then  begin    iId := ZQuery1.FieldByName('id').AsInteger;   
ZQuery1.SQL.Text := 'DELETE FROM links WHERE id=:id';   
ZQuery1.ParamByName('id').AsInteger := iId;    ZQuery1.ExecSQL; 
end;end;

----------------------------------------------------------------------
af0815 · 2022-03-16 06:12

In your sample the using of ID is not correct. You have to give a
valid value. If the ID is comming from an old state of the query, with
the new declaration of the sql-statement the query is cleared in the
background. This is the reason for your error. With the setting of th
SQL all the information about the fields/data in the 'old' resultset
is lost.

The next is, make a close followed by a clear in the query before you
set a new statement. This makes a stable enviroment before setting new
SQL text. So it is meaningless you work with SQLdb, ZEOS or another
SQL-framework. I know ZEOS do it in the background actual. But doing
this explcit avoid problems if something changes internally. And you
can better see/understand why your code is not working as expected.

----------------------------------------------------------------------
miab3 · 2022-03-16 11:22

Just like dseligo does or use second ZQuery or simply:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ZQuery1.Delete;
You can even define yourself what to do ZQuery connected to DBGrid
(other than the default) for Delete, Insert, Modify in the attached
ZUpdateSQL

Michał

----------------------------------------------------------------------
Pe3s · 2022-03-17 15:57

I have this question we have a table which has fields id, name , email
. We are reading data select name, email from table.
How can we read the id?

----------------------------------------------------------------------
dseligo · 2022-03-17 17:39

Quote from: Pe3s on March 17, 2022, 04:57:10 pmI have this question we
have a table which has fields id, name , email . We are reading data
select name, email from table.
How can we read the id?

What is exact problem?

Why don't you use something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ZQuery1.SQL.Text := 'select id, name, email from
your_table_name';ZQuery1.Open;

----------------------------------------------------------------------
Pe3s · 2022-03-17 18:39

I want the id not to be visible in the DBGrid table, and, for example,
when you clicked on a position, you could display the id
You could use   Code: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i DBGrid1.Columns[0].Visible:=false;

----------------------------------------------------------------------
JanRoza · 2022-03-17 21:33

DbGrid does not have to have all fields from the query as columns, why
don’t you define just the columns you want visible. Even if the query
contains a hundred fields you can have a dbgrid on that query
contaning only four columns.

----------------------------------------------------------------------
dseligo · 2022-03-17 23:36

As JanRoza said, best is if you define columns you want to see in
DBGrid.

But you can also programaticaly remove existing column from DBGrid
like this (I used this, I don't know, maybe there is a better way):
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var
col:TColumn;begin  col := GetDBGridColumn(DBGrid1, 'id');  while col
<> nil do  begin    col.Destroy;    col := GetDBGridColumn(DBGrid1,
'id');  end;end;

----------------------------------------------------------------------
Pe3s · 2022-03-18 17:21

Thank you for your help