Problem with UniQuery.Delete

[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.

[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?

[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.

[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.

[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.

[bork]

Hello

When you write any SELECT query like this:

Code: Select all

  select * from MyTable

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';

[Infonet]

Ok, thanks for the answer, we will check those 2 properties, UpdatingTable and KeyFields.