Page 1 of 1
Problem with UniQuery.Delete
Posted: Mon 03 May 2010 15:28
by Infonet
Hello,
we are very interested on using Unidac in our company. This is the perfect component for us since we make use of Access databases and SQL Server. But when testing it we met a problem with a query that has been working for ages with another component called TDAOQuery from Diamond Access and that with TUniQuery doesn't work.
The query text is like this:
Code: Select all
Select * from Invoices LEFT JOIN Customers on Invoices.CodCustomer = Customers.CodCustomer
And the problem arises when we try to make a UniQuery1.Delete procedure. We get this error message :
Code: Select all
[Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables.
We are using Unidac under Delphi 7.
Any help will be greatly appreciated.
Regards.
Posted: Wed 05 May 2010 09:52
by Infonet
We only have this problem of not being able to delete the current record when using queries with left/right joins. Maybe we are missing any property from TUniquery?
Posted: Thu 06 May 2010 09:45
by bork
Hello
Unfortunately, the information you have provided is not enough to reproduce your issue. We need additional information. Please send us the DDL script for creating the tables that are used in your query. Also please specify the version of UniDAC components, and the providers you use to connect to Access databases and SQL Server.
Posted: Thu 06 May 2010 14:31
by tobias_cd
@Infonet:
whenever you use joins it is best practice (if not required) to set both the TUniQuery.UpdatingTable and TUniQuery.KeyFields properties so that UniDAC knows what table to update exactly. This works for me for at least SQL Server and Oracle.
Posted: Thu 06 May 2010 16:17
by Infonet
Thanks for the answers.
We are using the latest Unidac version, its' UniDAC 3.00 for Delphi 7.
We are using only 2 components for our tests, one TUniConnection and one TUniQuery. The ProviderName for the TUniConnection is Access, we are testing over an mdb file.
The UniQuery SQL String is one similar to the specified above.
And this is what we have implemented under Delphi 7:
Code: Select all
UniConnection1.Connected := True;
UniQuery1.Close;
UniQuery1.Connection := UniConnection1;
UniQuery1.Sql.Clear;
UniQuery1.Sql.Add('select * from Consulta6');
UniQuery1.SqlDelete.Add('select * from Consulta6');
UniQuery1.Open;
UniQuery1.Delete;
ShowMessage(IntToStr(UniQuery1.RowsAffected));
UniQuery1.Close;
We have just added the line "UniQuery1.SqlDelete.Add('select * from Consulta6');" and now we have no errors but it returns -1 RowsAffected and nothing is deleted from the query.
Where can we find more info about the SqlDelete property? we have read the included UniDAC.chm but it's not very clear for us.
Posted: Fri 07 May 2010 11:56
by bork
Hello
When you write any SELECT query like this:
and want to delete a record then UniDAC generates a SQL query for deleting a record automatically.
But if you write a complex query that selects data from several tables then UniDAC can be confused and generate invalid DELETE query. To help UniDAC generate a correct query you can define the UpdatingTable and KeyFields properties. In this case UniDAC will know from which table the record should be deleted and which key field can identify this record as a unique one and the DELETE query will be generated correctly.
Sometimes users don't want to use automatically generated DELETE (or UPDATE, or INSERT) query. In this case you can define DELETE query manually by setting the SqlDelete property:
Code: Select all
UniQuery1.SqlDelete.Text := 'delete from MyTable where ID = :Old_ID';
Posted: Fri 07 May 2010 14:49
by Infonet
Ok, thanks for the answer, we will check those 2 properties, UpdatingTable and KeyFields.