Using UpdateSQL component

[lespaul]

Technically this is a continuation of Thread102-604460, but my question has dramatically changed since that posting.

I am trying to use the UpdateSQL component. I have created the component, used the Update Editor to create my Modify SQL:

update JMPMAIN
set
RTM = :RTM
where
JURNUM = :OLD_JURNUM


I have the following code:

procedure TfrmMileageReview.btnOkClick(Sender: TObject);
begin
with JMSData.qryMilesReview do
begin
If UpdatesPending then
ApplyUpdates;
end;
end;

After I open the frmMileageReview, the data is appearing in the DBGrid. If I change the first record but don't move off it and press the ok button, nothing happens; if I change the first record, move to the second record and press ok, the data is changed in the database; if I try to change any other data, when I move off the cell I get the error:
Could not find record.

any ideas on what I'm doing wrong?





Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[EricDraven]

Make sure that your Query values are set as follows.

RequestLive = True
CachedUpdates = False
Use the post command instead of ApplyUpdates.

I have found that this works perfectly (Im using it right now in fact). If you are unsure about your Modify SQL etc, open your Query at design time, double click the UpdateSQL component and select to "Generate SQL". Delphi will then generate your SQL based on the data in your queries SQL.


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[lespaul]

I have also used the Post/Request Live, etc., but since the query has an ordered by clause it makes the dataset read only. If I don't have an UpdateSQL component, then I can't modify the DBGrid at all!

Like I said, it works as long as I'm only changing the first record. As soon as I try to change any other record I get the 'Could not find record' error.

Leslie

 

[BillDoorNZ]

have you set the keyfield for the query component that is doing the select? I've had this same problem if the keyfield (or was it index field) was not set

 

[EricDraven]

LesPaul which version of Delphi are you using? I ran into a similar problem using the interbase queries and I found out that it was because I was making changes to more than one record in a query result. The answer to this was to use the IBDataset component instead of an IBQuery and an IBUpdate. The dataset component is basically the Query and the Update rolled into one and it solved the problem straight away.

The only problem is, I dont think there is a dataset component outside of the interbase tab in anything less than Delphi 6! Im sure it could be downloaded from somewhere though.


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[lespaul]

Eric:

I'm using Delphi 6. I'm using a TQuery component name qryMilesReview to get the data:

SELECT JURNUM, PANELID, FIRSTNAME, LASTNAME, RTM FROM JMPMAIN ORDER BY PANELID

Connecting through a database component to an AS400. There is a datasource component attached to the query (dsMilesReview) which is ultimately the source of the DBGrid. Should I use the BDEClientDataSet component in this situation? How would I set it up instead?

Thanks! This has been driving me to frustration for two days!

Leslie

 

[EricDraven]

I do something virtually identical in my program and have just tried it using the Order By command and it still works spot on which has made me scratch my head. As far as I was aware this wasnt possible via SQL using commands like Order By???

In my program I have a DBGrid connected to a query via a datasource. Instead of using a button to update the table like you, I allow the user to just press enter on the relevant field in the DBGrid. The following code is in the DBGrids OnKeyPress event to handle the post.

if Key = #13 then //The enter key!
begin
  if Query1.state = dsEdit then
  begin
    Query1.Post;
  end;
end;

It sounds to me like your setup is very similar to mine. The only other difference is that as mine is a very small table I use the Select * command instead of selecting the different fields indivdually.

I hope this helps you out!


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[lespaul]

Ok, I've had to attack this from a different angle because I CANNOT get this UpdateSQL to work. If anyone has any other suggestions, feel free!

Thanks
Leslie