Board index » delphi » Simple data manipulation - where am I going wrong?

Simple data manipulation - where am I going wrong?

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

 

Re:Simple data manipulation - where am I going wrong?


Quote

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

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

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.

Re:Simple data manipulation - where am I going wrong?


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

Quote
Frederic Gelinas wrote:

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

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

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

--
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)

Re:Simple data manipulation - where am I going wrong?


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

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

Maybe I can provide one?    8-)

Paul...

--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

Re:Simple data manipulation - where am I going wrong?


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

Quote
> > Select

            ^^^^^^^ *_nothing_*

Quote
> > from MyUser
> > where
> >   USERNAME = :USERNAME and
> >   USERFULLNAME = :USERFULLNAME and
> >   USERPRIVILEGED = :USERPRIVILEGED
> This SQL is not valid at all.  You need to provide some fields... Try:

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.

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

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

Re:Simple data manipulation - where am I going wrong?


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

Quote
> > Select
> > from MyUser
> > where
> >   USERNAME = :USERNAME and
> >   USERFULLNAME = :USERFULLNAME and
> >   USERPRIVILEGED = :USERPRIVILEGED
> This SQL is not valid at all.  

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

Quote
> You need to provide some fields... Try:
> Select UserName, UserFullName, UserPrivileged

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

Re:Simple data manipulation - where am I going wrong?


Quote
> 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!)

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

Quote
> 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?

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

Re:Simple data manipulation - where am I going wrong?


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

Quote
> > 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!)
> Oui, je suis francophone...

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.

Quote
> L'tes-vous?  Je suis de la rive-sud de
> Montreal.

Maintainant je suis de la rive nord de Dublin!

Quote
> > 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?
> 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.

How do I explicitly commit so?

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

Quote
> 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).

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

Re:Simple data manipulation - where am I going wrong?


Quote
> How do I explicitly commit so?

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

You probably have an IBTransaction somewhere...

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

Other Threads