Firebird - updating values in record
lazarus / 16-Databases

----------------------------------------------------------------------
lennit · 2014-07-25 08:30

Hello,

As I am a total newbie in databases I got stuck again at a simple
thing. I want to fill some of my fields with values from a regular
combobox, so when i edit a record i try this:

Code: [Select]mainQ.Edit;
mainQ.FieldByName('member').Clear;
  mainQ.FieldByName('member').NewValue:=combobox3.Items[combobox3.item
index];
  dbedit10.Field.AsString:=combobox3.Items[combobox3.itemindex];

These are the two options i stopped at - editing directly the field in
the TSQLQuery or editing it in it's assigned dbedit. None of them
works, because at mainQ.ApplyUpdates; i get this error message:

No update query specified and failed to generate one. (No fields for
inclusion in where statement found).

Actually i receive the same error when i edit even directly the dbedit
and change the information there.

I need to be able to update several tables from different sources like
calendars, memos and so on, but i can't figure out this simple thing.
Where have i messed up?

EDIT: Attached sample.

----------------------------------------------------------------------
mangakissa · 2014-07-25 09:09

Wrong approach.
Code: [Select]mainQ.Edit;
mainQ.FieldByName('member').AsString
:=combobox3.Items[combobox3.itemindex];
mainQ.Post;
mainQ.ApplyUpdates;                                               
 <--- sent your changes
TTransactions(mainQ.transaction).commitretaining;    <--- commit your
changes
I presume dbedit10 is connected to  field member, so changes already
made and visible.
But why do you change it two times? There's a great tutorial for this.

----------------------------------------------------------------------
lennit · 2014-07-25 09:23

Because it doesn't change even a single time. Data is still the same
in the database and if i try refreshing it says it can't before
changes are applied. I use the Post buttin of the dbNavigator and it
doesn't complete the post because of this error.

Also when i change directly the dbedit (supposedly the field member as
well) i get the same error, even though as i understand it, when the
query is edited, the changes in this element should be considered...
who knows... changes  .

Sorry for asking obvious questions, but i have no idea really what i
do wrong.

----------------------------------------------------------------------
rvk · 2014-07-25 12:16

The problem is you set mainQ.UsePrimaryKeyAsKey:=False;

The database components automatically create an insert, update and
delete object with the correct SQL for your SELECT-statement
(according to a unique ID of the record which is usually the primary
key). But if you don't have a correct primary key how would it know
WHAT record to change.

Normally an update-SQL is created like this:
Code: [Select]UPDATE table_name
SET column1=value1,column2=value2,...
WHERE ID=:ID;
(where ID is your primary key)

In your case when you set UsePrimaryKeyAsKey to false it is:
Code: [Select]UPDATE table_name
SET column1=value1,column2=value2,...
In that case all records would be changed. Therefore if you don't
leave UsePrimaryKeyAsKey true the update-SQL will be left empty and
you would need to set it yourself like this (in mainQ.UpdateSQL.Text):
Code: [Select]UPDATE table_name
SET column1=value1,column2=value2,...
WHERE Somefield=:Somefield
(where somefield is some field which can uniquely identify your
record)

But i would advise you to use the primary key (which should be ID).

Why did you set the UsePrimaryKeyAsKey to false?

Edit: BTW. This behavior is also set by the Upda]"]>Blockedde of
mainQ.
You left it at upWhereKeyOnly which means the WHERE clause of
updateSQL is created with only the primary key (and in that case you
shouldn't set UsePrimaryKeyAsKey to false). You could also set it to
upWhereAll. In that case the updateSQL would use all known fields to
check for uniqueness. (But i would still advise using the primary key
because that one is always unique)
See also "]>Blockedde.html]Upda]"]>Blockedde and UsePrimaryKeyAsKey

----------------------------------------------------------------------
lennit · 2014-07-25 12:40

QuoteWhy did you set the UsePrimaryKeyAsKey to false?

  Because i know only this much and this is how it was done in a
tutorial i saw. Of course this is no excuse i am not thinking while
doing things  .
Thanks a lot for the help. I am trying this out the moment my computer
cools down a little.