Updating record with join in SQL

[daveinhull]

Hi,

Hope some one can help with what is probably a simple answer.

I've got a small test program using an MS Access DB with the following SQL and using a DBGrid but the records returned are not editable. The same query in MS Access produces editable records.

Am I missing something?

Code: [Select]

SELECT
Contract_Orders.Location
FROM Contracts INNER JOIN Contract_Orders ON Contracts.ID = Contract_Orders.T_Contract;

Many thanks
Dave
                                               

[LacaK]

if you use sqlDB (TSQLQuery), then resultset returned by SELECT involving multiple tables is not updateable by default.

You must supply UPDATE ststement in UpdateSQL property.
See: http://wiki.freepascal.org/Working_With_TSQLQuery#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_Parameters

[daveinhull]

Many thanks LacaK, confirmed what I suspected .....

However, the link talks about the SQL statement which I agree was the flavour of my question, but the DBGrid doesn't even allow me to edit its contents - is this because SQL resultset is not updatable?

Also, I'm not quite sure how to work this in practice. Assuming that I can actually edit the contents of a cell in the DBGrid, how would the UPDATE statement get triggered, through an event procedure?

Thanks in advance
Dave

[LacaK]

However, the link talks about the SQL statement which I agree was the flavour of my question, but the DBGrid doesn't even allow me to edit its contents - is this because SQL resultset is not updatable?

Check SQLQueru1.CanModify and SQLQuery1.ReadOnly properties
If you set UpdateSQL (or InsertSQL or DeleteSQL) manually then dataset should be updateable

Also, I'm not quite sure how to work this in practice. Assuming that I can actually edit the contents of a cell in the DBGrid, how would the UPDATE statement get triggered, through an event procedure?

You must set UpdateSQL (or InsertSQL or DeleteSQL) of your SQLQuery1
Once they are set all is performed in background during ApplyUpdates call.

[daveinhull]

Lacak, many thanks for the quick reply.

I've checked SQLQuery1.Readonly and its false, but there doesn't appear to be a SQLQuery1.CanModify in the properties view of the SQLQuery control.

I've read through the link you sent me and it talks about setting up an UPDATE query and using variables such as :USER, :e-mail etc., but I don't have variables as I've just got a DBGrid with cells in it; what kind of UPDATE query would I be using?

Code: [Select]

UPDATE fpdev SET UserName=:USER, InstEmail=:e-mail WHERE UserName=:OLD_User;

Many thanks
Dave

[rvk]

I've read through the link you sent me and it talks about setting up an UPDATE query and using variables such as :USER, :e-mail etc., but I don't have variables as I've just got a DBGrid with cells in it; what kind of UPDATE query would I be using?

The "variables" OLD_xxx are automatically set (when the record is retrieved).
The other variables (like :xxx) should match your fieldnames from the SELECT. So the Username and InstEmail should be in your SELECT.
So it would be something like this:

Code: [Select]

UPDATE fpdev SET UserName=:UserName, InstEmail=:InstEmail WHERE UserName=:OLD_UserName;

But you say your SQL was like this:

Code: [Select]

SELECT
Contract_Orders.Location
FROM Contracts INNER JOIN Contract_Orders ON Contracts.ID = Contract_Orders.T_Contract;
So I imagine your DBGrid would only display Contract_Orders.Location.
(Or am I missing something?)

So I'm not sure why you want to change the fields in the query you showed. And if it's not the complete query... If you have fields from both Contracts and Contract_Orders-table, how would you like to change those fields. In that case DBGrid-editing is not the right choice.

[LacaK]

I've checked SQLQuery1.Readonly and its false, but there doesn't appear to be a SQLQuery1.CanModify in the properties view of the SQLQuery control.

You must use in code after Open:

Code: [Select]

if SQLQuery1.CanModify then ShowMessage('Updateable');
if SQLQuery1.ReadOnly then ShowMessage('ReadOnly');
to check curent status of dataset

[daveinhull]

Hi LacaK and rvk,

OK thanks, all becoming much clearer and I think I see now, obvious really - but after working ones way down the wrong path the correct path just seem more wrong.....

rvk, I think I knew that the DBGrid was the wrong method to do an update and yes the query is incomplete, the DBGrid (and the real query) shows multiple fields, but I'd reduced it to just one to see if that was causing the problems. This query is actually just designed to provide a front page index of all contracts and I actually don't really want to edit it, but to select an entry and for all the other behind the scene forms to go to the right record entry for each of the link tables. However, in Access (I originally wrote it in Access) I can edit this SQL and it became handy for adjust minor changes on the underlying tables - so just wanted to see if I could replicate what I already had - not a bad way to run in parallel.

Thanks again, 'windows' cleaned now - I hope.

Dave

[daveinhull]

Hi again,

OK, so I thought I was getting somewhere. I put an update query in the UpdateSQL property and the DBGrid became editable. I also added an event to ApplyUpdates after Post, but I'm getting an error message saying that "Could not update, currently locked by user......". Its an MS Access DB and this is a native error message.

If I don't apply updates then clearly nothing gets saved and when I refresh the grid using the navigator I get the (obvious) error message "must apply updates before refreshing data"

Any help would be much appreciated and I'm sure once that last bit of the jigsaw is fitted the whole picture will become clear - good way to learn :-)

Dave

[LacaK]

but I'm getting an error message saying that "Could not update, currently locked by user......". Its an MS Access DB and this is a native error message.

Do you use TODBCConnection to connect to MS Access DB ?
When you get this message, are there multiple simultaneous connections at same time ?
Is in the message mentioned another user name ?

[daveinhull]

Hi LacaK,

Thanks for the reply and sorry for the delay in answering your questions; I'm travelling at the moment.

Yes I'm using a TODBCConnection to connect to a MS Access DB
No, there are not multiple connections as far as I know
Yes it does mention another user (I think it was Admin), but can't now check as I'm getting another error message SQL statement not set - I must have done something?

Thanks
Dave

[LacaK]

Can you create simple project , which shows error (you get) and all with DB zip and attach here.
It will be simpler and faster have your code and try it ...

[daveinhull]

Hi Lacak,

Ok, many thanks - will keep me occupied while I fly across the Pacific 

Dave

[daveinhull]

Hi Lacak,

Sorry I've not replied sooner to your offer of help, but some travel issues got in the way.

However, I tried uploading a zipped example of the problem I was having and it said that I'd exceeded the upload limit. The zip file appears to be 4.63Mb even though it is a very small example.

Can you let me know which files would be expressly needed out of all those in the actual project directory.

Many thanks
Dave

[rvk]

You don't need to include the .res for example.

But the best option is to use to choose Project > Publish project and give a directory. Lazarus will copy all the essential files to that directory after which you can zip-it and upload here. It will be the smallest possible zip with all the needed files.