Firebird - updating values in record

[lennit]

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.itemindex];
  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]

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]

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]

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 Updatemode 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 UpdateMode and UsePrimaryKeyAsKey

[lennit]

Why 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.