[solved] DBNavigator with user defined SQL

[Nicole]

How can I user-define the SQL on click?

These I have:
DBNavigator_Kupons
DataSource_Kupons
IBQuery_Kupons
IBUpdate_Kupons
DBGrid_Kupons

This IS in the navigator

Code: MySQL  [Select][+][-]

1.     Select ID_KUPON,BETRAG, DATUM, FK_EINKOMMEN
2.     From TBKUPONS
3.     where id_Kupon = :id_Kupon

This SHALL be in the navigator

Code: MySQL  [Select][+][-]

1.     Select ID_KUPON,BETRAG, DATUM, FK_EINKOMMEN
2.     From TBKUPONS
3.     where id_Kupon = :id_Kupon
4.     and FK_einkommen = :myChosenValue

"myChosenValue" shall be taken from an edit-field, which the uses may change.

[rvk]

This IS in the navigator

Code: MySQL  [Select][+][-]

1.     Select ID_KUPON,BETRAG, DATUM, FK_EINKOMMEN...

Does your (tdb)navigator have a SQL property ????
I think you mean that your IBQuery_Kupons has that SQL statement.

What's wrong with changing it when the user changed the TEdit with myChosenValue? (OnChange or when pressed a Search button)

Then you only have to do IBQuery_Kupons.ParamByName('myChosenValue').AsString := myEdit.Text;

[Nicole]

Thank you for your reply.

The dbnavigator has no SQL property, - this is exactly my problem.

This IBQuery_Kupons-select statement I can change, but it does not work under the line.
My suspect, this is due to "and FK_einkommen = :myChosenValue"

Let me explain the details:
The user (this is me) choses a certain fk_einkommen by keying in the number, fine.
Then I hit the button to list the values = the select statement-result is filled into the DBGrid, fine.
This is fine and can be edited by the DB navigator and within the DGGrid, fine
It can be edited as well, fine.

THEN I want the value to be changed into the DB.
and: No edit is brought to the database.
This means: Pushing the button again does not show the edited values, but the old ones.

This is not too surprising, because the and "FK_einkommen = :myChosenValue" no where finds its way in any update-statement.

It woud not be too hard, to find such a valid update-statement including the "and FK_einkommen = :myChosenValue".

My problem is:
Where to pass this statement into my code for using it in the query or navigator?
It has no SQL-property and the IBQuery only has a select-property.

In other words: I would need something like
myThing.SQL.Text:"'update value1 from tbtable where..."

WHERE can I find a "myThing" to pass such a statement?

[rvk]

In other words: I would need something like
myThing.SQL.Text:"'update value1 from tbtable where..."

I'm a bit surprised you are asking this question.
We've discussed the UpdateSQL property of TSQLQuery many times in the past.

And if you are still using TIBQuery (which you don't mention) then there is a UpdateObject property (which was also discussed).
Then you need to create a TIBUpdateSQL component which contains the Update/Delete/Insert statements, and attach that to the TIBQuery.UpdateObject.

BTW. TDBNavigator doesn't do anything other than just jumping through the databaset (TDataSource which is connected to TSQLQuery or TIBQuery) and putting it into edit or insert mode.
So you don't do anything with input there. Everything goes through the TDataset (which is that TSQLQuery or TIBQuery).

[Nicole]

pls look in my first posting, there I listed all used items, I used including an "IBUpdate_Kupons".

I remember having seen context-menus with properties modifySQL, UpdateSQL or similar. I used them in an old unit long ago, but cannot find anymore how and where.

Unfortunately those properties are not part any more of any of the components listed in my first posting.

Probably the solutions of my question is that easy, that we both will call out "ah, this it was".

[egsuh]

I really don't understand. You said you edit value in the DBGrid. Is the changed value to be used back as :myChosenValue, or some other field and :myChosenValue will come from other control?

You have to understand that the data changed is posted to TDataSet (IBQuery_Kupons in this case),  which is a volatile copy of part of the permanent DB data. Even though you change some data in the DBGrid and post them, they will vapor into air when you close the DataSet (again, IBQuery_Kupons).

In order to save the changed content permanently to the DB, you have to
1) call IBQuery_Kupons.ApplyUpdates with properly defined IBQuery_Kupons.UpdateSQL and others,
2) and then (IBQuery_Kupons.Transaction as TSQLTransaction).Commit (TIBTransaction, instead of TSQLTransaction?).

You may have automated this procedure, but you have to understand the process.

"Commit" will close IBQuery_Kupons. If you don't like this, call CommitRetaining. This will keep IBQuery_Kupons open.

To see the changed values, you have to remake IBQuery_Kupons.SQL and then open IBQuery_Kupons again.

[rvk]

pls look in my first posting, there I listed all used items, I used including an "IBUpdate_Kupons".

IBUpdate_Kupons isn't a classname. It's just a name you gave a component. But what component is that???

I remember having seen context-menus with properties modifySQL, UpdateSQL or similar. I used them in an old unit long ago, but cannot find anymore how and where.
Unfortunately those properties are not part any more of any of the components listed in my first posting.

If that IBUpdate_Kupons is a TIBUpdateSQL then it should have insert/delete and update(modify) sql properties.

[Nicole]

@egsu: Thank you for this explanation, I will save it in my code-snipet-explanation-text-collection.

@rvk you solved in exactly this easy way, we say "oh, just that!"

I was not aware, that there are "TWO of them": IBUpdate and IBUpdateSQL.
I have used IBUpdate and wondered, why my SQL-properties are nowhere to find.

So I changed IBUpdate to IBUpdateSQL.
This is the place, where I can put my SQL statements as strings, which is want I wanted.

Thank you both!