StringGrid as DBGrid

[lespaul]

I have a process that I need to allow the user to update data. I started out with a DBGrid which would have been PERFECT, but the query I'm using has an order by clause, which makes the dataset READ ONLY. So now I must figure out how to do all of the DB aware components in a string grid.

It's fairly simple:

PANELID NAME RTM (Round Trip Miles)
030707P01J01 Joe Blow 15
030707P01J02 Jane Doe 40


I need to allow the user to change the RTM. Is there an easy way to update the database then refresh the string grid data to reflect the change?

Does anyone know why the order by clause restricts the dataset to read only?

Thanks!


Leslie
landrews@metrocourt.state.nm.us

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

 

[Svanhooft]

hi,

if you use a query, you also have to use an update component. A selecr alwyas makes the retrieved dataset read only. So in order to change something use connect the Tquery which holds the select statement with TUpdateSQL component. The last one will hold the insert, update and delete queries. When you add the last onde to the tquery you will be able to update the data as you where intend to.

Steph [Bigglasses]

 

[lespaul]

I have other DBGrids that use a SELECT query and they update fine without the Update query. It's only when I add the order by or use a joined query that it doesn't update.

Leslie
landrews@metrocourt.state.nm.us

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

 

[towerbase]

You could specify the TQuery property RequestLive to be TRUE.

The Delphi help indicates that some database systems may return an updateable result set and some may not. It depends on the database.

If you've tried RequestLive=true and it doesn't work then I guess your (AS400?) DBMS falls into category 10.

I suspect that using a StringGrid will be messy.


Andrew

 

[lespaul]

Yes the string grid will be messy. If I don't use the ORDER BY then the result set is updatable, but I need it ordered. And yes, it's an AS400.

Will Steph's idea of the UpdateSQL not work? What do I need to put in the UpdateSQL (in the ModifySQL?) in order for it to update correctly?

Les

 

[lespaul]

Ok, so 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

when i open the DBGrid now, I can change the first row with out an error (but it doesn't post), but when I try to modify any record but the first it gives error:

Could not find record

My other question is how and where do I set the parameters in the above update query? How do I get the information to post to the database?

Thanks for all the info!

leslie

 

[lespaul]

So I have made a little bit of progress, but still not working right.

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?

Thanks,
Leslie

 

[lespaul]

I'm still trying to get the TUpdateSQL statement to work properly. If anyone has any ideas or suggestions, I'm open!!

I have a TQuery component on my Data Module, I have a TUpdateSQL component in the Data Module. I have RequestLive and CachedUpdates set to TRUE. Do I need something in the BeforePost/AfterPost of the query? What am I missing.

Thanks!
leslie

 

[EricDraven]

Set CachedUpdates to False and leave RequestLive as true.

If you are using the query to order your results (order by), then unfortunately you cannot update the queries returned records.


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

 

[lespaul]

I thought that was the purpose of the TUpdateQuery was to allow you to edit a local copy of the dataset and then run a Modify/Insert/Delete query and then have the changes written the the database?

 

[EricDraven]

Sorry, misunderstood what you were aiming for!

You can do that with the CachedUpdates. If you do indeed set this value to True, then posts will take effect when you refresh the query (I believe from my somewhat hazy memory! - I would check in the dlephi help). I found this method to be a bit hit or miss though and prefer to take a second or so longer and transfer my results queries into a temporary table. Then I know that the users can do whatever they like to the data before I finally post the changes back to the query.


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