Board index » delphi » SQL Update don't work

SQL Update don't work

Hello,

I use to change the value of a field following code and get no error -
BUT the field don't change the value :-(

Any Idea?

-----------------Code begin-------------------
With Table1 do begin

         if (NOT Table1['Gelesen']) then begin

            with qDummy do begin
               Close;
               SQL.CLEAR;
               SQL.ADD('UPDATE ".\Data\' + __Name + '.DBF" ');
               SQL.Add('SET Gelesen = :mNeuwert');
               SQL.ADD(' WHERE (SektionsNr = :mSektionsnr)');
               SQL.ADD(' AND (ID = :mID)');
               SQL.ADD(' AND (Thread = :mThread)');
               SQL.ADD(' AND (Parent = :mParent)');

               ParamByName('mNeuwert').AsBoolean := TRUE;
               ParamByName('mSektionsNr').AsString :=
Table1['SektionsNr'];
               ParamByName('mID').AsInteger := Table1['ID'];
               ParamByName('mThread').AsInteger := Table1['Thread'];
               ParamByName('mParent').AsInteger := Table1['Parent'];
               ExecSQL;

               Close;
            end;
         end;

   end;   // WITH Table1

--------- Code end -------------------------------------

Regards

Reinhard

 

Re:SQL Update don't work


What database are you using? How are you checking to see if the field has
changed?

--
Bill
(TeamB cannot answer questions received via email)

Re:SQL Update don't work


Hello Bill!

Quote
> What database are you using? How are you checking to see if the field has
> changed?

I use a dBase database and check first with the de{*word*81} and second in the
application if the right radiobutton is marked.

Regards
Reinhard

Re:SQL Update don't work


I am confused. I thought you said that the data in the database table was
not being changed. Are you calling TTable.Refresh to see if the field has
been updated?

--
Bill
(TeamB cannot answer questions received via email)

Re:SQL Update don't work


  Please change your code to a select query to see whether you can find records meeting your condition.
  Good Luck.

Deluxe Lee

Re:SQL Update don't work


Hello,

thank you for reply :-)

Quote
> Please change your code to a select query to see whether you can find records meeting your condition.

Oh - the record is the current record where I want change one field, so I must no search for them. for
for them.

Regards

Reinhard

Re:SQL Update don't work


Hello Bill,

thank you for replay :-)

Quote
>> I am confused. <<

Sorry for that :-(

Quote
>> I thought you said that the data in the database table was not being

changed. <<

Thats right. I want to change one field of the current dataset but the code
don't work...

While the Dataset is 'ReadOnly' i must use a query (very slow) to change
because set Table1.ReadOnly to FALSE the Dataset Pointer move to another
Dataset :-(

Quote
>> Are you calling TTable.Refresh to see if the field has been updated? <<

I have use a breakpoint to have a look with the de{*word*81}, I have a look
with the databasemanager in the table and yes - I have also used
TTable.Refresh.
But nothing - the field wan't go from FALSE to TRUE

Regards

Reinhard

Re:SQL Update don't work


You could test qDummy.RowsAffected after ExecSQL to see how many rows
where changed...

One possible reason why the record might not get updated: if the string
field value is '' (EmptyStr), the the query

where
  AField = ''

might not be able to find the record. You might need to use

where
  AField is null

Gert

Quote
> I use to change the value of a field following code and get no error -
> BUT the field don't change the value :-(

Re:SQL Update don't work


Hello Gert!

Quote
> You could test qDummy.RowsAffected after ExecSQL to see how many rows
> where changed...

A good idea :-)

Just do it and - qDummy.Rowsaffected shows 0 rows changed :-(

Quote
> One possible reason why the record might not get updated: if the string
> field value is '' (EmptyStr), [...]

I know but the field is not empty. If a new dataset is write to the table
the field get the value FALSE by force.

Regards
Reinhard

Re:SQL Update don't work


The current record should not change when you change the ReadOnly property
of the TTable. Perhaps you have a corrupt table. Try rebuilding it with the
table repair utility and see if that helps.

--
Bill
(TeamB cannot answer questions received via email)

Re:SQL Update don't work


Quote
> > You could test qDummy.RowsAffected after ExecSQL to see how many rows
> > where changed...

> A good idea :-)

> Just do it and - qDummy.Rowsaffected shows 0 rows changed :-(

Have You checked the current directory at the moment of query execution?
Also, verify that the Database property of the qDummy is emtpy...

Anyway, relaying on the current directory is not a good idea... Better
set up an TDatabase, and change it's params as appropriate at startup.

Gert

Re:SQL Update don't work


Hello Gert!

Quote
> Have You checked the current directory at the moment of query execution?

Yes :-)

Quote
> Also, verify that the Database property of the qDummy is emtpy...

Database property, Datasource property and SQL property are empty...

Quote
> Anyway, relaying on the current directory is not a good idea... Better
> set up an TDatabase, and change it's params as appropriate at startup.

Mhh - the idea is to mark a read Dataset as 'read' when I change the
Dataset with DBnavigator. I think 'at startup' is not the best place for
this mark...

Or do I understand it wrong (sorry, but my english isn't so good)?

Regards

Reinhard

Re:SQL Update don't work


Hello Bill!

Quote
> The current record should not change when you change the ReadOnly property
> of the TTable.

Change the ReadOnly property was another try - without Query....

Quote
>> Perhaps you have a corrupt table. <<

With a new table I get the same behave :-(

Quote
> Try rebuilding it with the table repair utility and see if that helps.

Table repair utility? Sounds good but where can I find this?

Regards

Reinhard

Re:SQL Update don't work


Quote
> Mhh - the idea is to mark a read Dataset as 'read' when I change the
> Dataset with DBnavigator. I think 'at startup' is not the best place for
> this mark...

> Or do I understand it wrong (sorry, but my english isn't so good)?

Hi.

I do not know what is the logic You are implementing. So, I didn't talk
about it...

Have You looked at TDatabase component? It has a DataBaseName property -
a "nickname" for that database. A database connects several datasets
(like TTable/TQuery-s) to the single data store, in the case of dBase,
to the folder where tables reside. So, if change the configuration of
the TDatabase component, all connected datasets get the new
configuration - a new data directory...

All I did say is that maybe You should set up an Database component and
connect all Your datasets to that component - this way it is much easier
to maintain data connections.

But one more thing You could check:

Check the values of all parameters before You call ExecSQL. If any of
them has "null" or '' (EmptyString) value, then You will need to change
the where clause...

Gert

Re:SQL Update don't work


There is a good table repair utility for Paradox tables at www.rksolution.cz
.

--
Bill
(TeamB cannot answer questions received via email)

Other Threads