Hi all

I have a very simple table

CREATE TABLE "MYUSER"
(
  "USERNAME"  VARCHAR(8) NOT NULL,
  "USERPASSWORD"      VARCHAR(8),
  "USERLOGGEDIN"      "D_BOOLFIELD",
  "USERFULLNAME"      VARCHAR(25) NOT NULL,
  "USERPRIVILEGED"    "D_BOOLFIELD"
);

D_BoolField is defined here.

CREATE DOMAIN d_BoolField   as CHAR(1) DEFAULT 'N' CHECK (VALUE IN ('Y',
'N'));

I have a form on which there is a DBGrid. I have a DataSource pointing
to an IBQuery (all default properties - just added a db, transaction and
updateobject) on one side and the DBGrid on the other.

The SQL statement of the Query (on form show, the query is opened) is

Select UserName, UserFullName, UserPrivileged from MyUser.

(UserPassword is changed elsewhere and UserLoggedIn is not implemented
yet).

I display the results in the DBGrid. Now, what I want is to be able to
edit that data and save it back to the database through the DBGrid.

So, what I did was hook up an IBUpdateSQL to the system. I put my three
fields UserName, UserFullName and UserPrivileged as both the Key Fields
and Update Fields. Then I pressed GenerateSQL and it generated the SQL
for me, which is fine - see SQL at bottom of post.

The problem that I am having is that when I update or add a record
(deleting is fine!) I get an error

Project Blah raised Exception Class EIBInterBaseError with message
Dynamic SQL Error
SQL Error Code = -104
Token Unknown - line 2, char -1
from'. Process stopped. User step or run to continue.

This is the message verbatim.

And the record that I have added disappears. However, when I go back
into the database, the record is there updated or added and when I
relaunch the programme, the record appears in the grid.

It appears to be something to do with the refresh SQL, but I'm stumped
at this point. Can anyone help me out with what I'm doing wrong here? It
should be relatively simple - I just want to use the grid to edit my
users - do I have to call commit(); at some stage or is this done for
me?

TIA.

Paul...

SQL....

-------Modify-------
update MyUser
set
  USERNAME = :USERNAME,
  USERFULLNAME = :USERFULLNAME,
  USERPRIVILEGED = :USERPRIVILEGED
where
  USERNAME = :OLD_USERNAME and
  USERFULLNAME = :OLD_USERFULLNAME and
  USERPRIVILEGED = :OLD_USERPRIVILEGED

-------- Insert

insert into MyUser
  (USERNAME, USERFULLNAME, USERPRIVILEGED)
values
  (:USERNAME, :USERFULLNAME, :USERPRIVILEGED)

---------Delete (works no problem!)

delete from MyUser
where
  USERNAME = :OLD_USERNAME and
  USERFULLNAME = :OLD_USERFULLNAME and
  USERPRIVILEGED = :OLD_USERPRIVILEGED

--------Refresh (think problem is here)

Select
from MyUser
where
  USERNAME = :USERNAME and
  USERFULLNAME = :USERFULLNAME and
  USERPRIVILEGED = :USERPRIVILEGED

--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

This SQL is not valid at all.  You need to provide some fields... Try:

Select UserName, UserFullName, UserPrivileged
from MyUser
 where
   USERNAME = :USERNAME and
   USERFULLNAME = :USERFULLNAME and
   USERPRIVILEGED = :USERPRIVILEGED

In these cases (when I need to edit the data directly), I prefer to use a
TIBDataSet.

Note that the UpdateSQL editor occasionally does this.  I have yet to get a
reproducible case to try and track it down though.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
If there is somebody up there could they throw me down a line.  Just a
little helping hand just a little understanding.  Just some answers to the
questions that surround me now.  If there's somebody up there could
they throw me down a line.               (Fish)

jeffoverc...@mindspring.com says...

Maybe I can provide one?    8-)

Paul...

--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

frederic_geli...@hotmail.com says...

            ^^^^^^^ *_nothing_*

Of course, now that this has been pointed out, there is obviously
something wrong - I will try and put in the relevant code this
afternoon! The problem is that I have completely rewritten the whole
form in various attempts to overcome the problem.

Could you explain what you mean here exactly? I have a DBGrid which I
wish to act as an editor upon a very simple table. How does a TIBDataSet
work under these circumstances and what is the difference between using
a TIBQuery with a TIBUpdateSQL object and using a TIBDataSet?

Paul...

--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

frederic_geli...@hotmail.com says...

Yes, indeed. I suppose that subconsciously I was seeing a "Select *" or
something.

YAAAAAYYY! Now it works!!!!! You've no idea how happy I am - Thanks a
million. (Si vous etes francophone, merci du fond de mon coeur!)

Just one question - How do I know if I've done a Commit or not, or
should I just assume that all changes made on the DBGrid are made to the
underlying database?

Thanks again/Merci encore.

Paul...

--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

Oui, je suis francophone...  L'tes-vous?  Je suis de la rive-sud de
Montreal.

No.  The changes are in the transaction context.  They are not commited
until the transaction is committed.  Just like in the data grid of
IBConsole.

Concerning the IBDataSet, it is just the same thing as an IBQuery with a
TIBUpdateSQL, but in only one component.  I never use an IBUpdateSQL.  When
I need to edit the data, I use an IBDataSet, it makes less components on the
form (or datamodule).

frederic_geli...@hotmail.com says...

Oui, mais je ne suis pas "pur laine" comme on dit au Qubec! Je suis
irlandais, mais j'ai pass cinq ans en France ou j'ai appris le
francais.

Maintainant je suis de la rive nord de Dublin!

How do I explicitly commit so?

DBGrid1->DataSource->Dataset->Commit();?

Je jeterai un coup d'oeil - merci encore.

Paul...

--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

You probably have an IBTransaction somewhere...

It's something like
DBGrid1->DataSource->Dataset->Transaction->Commit();
or, more accuretaly
TIBQuery(DBGrid1.DataSource.Dataset).Transaction.Commit();