Syntax error SQL statement

[beltijs]

Hi!
I having some problems with a ClientDataset with a Datasetprovider. When i make clientdataset.applyupdates(-1) it shows an error message: 'Syntax error un sqlstatement'. The database is Ado, the datasetprovider has his updatemode set un UpWhereKeyOnly, and the key fields are specified. I have tried everything, this model is working in other forms.
It's a simple table with 3 fields, there's no master detail relationship or something rare.
Can i capture de sql statement that is appliyng to the database? that could give me a clue about the problem.


Thanks

Paula

 

[hilfy]

What is the original SQL statement that selects the data for the clientdataset?

-D

 

[beltijs]

it's a Tadoquery with this sql:

SELECT ficha_tec_e.nro_maquina, ficha_tec_e.id_cliente, ficha_tec_e.ubicacion_nombre
FROM ficha_tec_e;

Because of this problem i have take away all relationship with other tables until i solve this problem. I have take away all indexes, none of the fields are required or autonumeric. All fields are strings, and all are marked for update and as keys.

 

[svanels]

The original tables are in access?
Access allows a bad habbit in naming field columns:
allowing white spaces in the column name and allowing lonf column names. This can complicate things when trying to use plain sql statements.

I did an upscale from access to interbase and in the conversion the white spaces were substituted by the underscore something like : ficha tec maquina would come like ficha_tec_maquina.
The tables were recreated in Interbase but sometimes the data wasn't transfered, thus empty tables.

The workaround was to rename the column names in access to get a seamless upscale.

Particularly when you have a field like "Sample before filter" the SQL engine will get confused

Steven van Els
SAvanEls@cq-link.sr

 

[hilfy]

Are any of the fields in your select statement key fields?

If you have a primary index, you'll need to include the field(s) from that primary index in the select statement. That's the only way the system will know what the fields are so that it can update the record.

If you have no indexes, there are no key fields that you could use upWhereKeyOnly with. In this case you should use upWhereChanged.

-D

 

[beltijs]

i solve the problem in this way:
i have modified de sql statement that select the data like this:
SELECT nro_maquina,
ubicacion_nombre,
cliente.nombre,
ficha_tec_e.id_cliente
FROM ficha_tec_e
INNER JOIN cliente on
ficha_tec_e.id_cliente = cliente.id_cliente;

The table that has to be updated is ficha_tec_e, with all their fields (nro_maquina, ubicacion_nombre, id_cliente), and all fields are in one Primary index. When i add the join with cliente, works perfect.

Thanks all for your replys.

 

[VintageWine]

To answer your original question. TADOConnection.OnWillExecute event. CommandText parameter will give you the update statement that is about to be executed.

Simon