At 02:55 PM 04-09-01 +0200, you wrote:

>Hi
>Believe it or not I have never done a fancy delete clause.
>
>Is deletesql the right property to modify when I want to
>delete data out of a table(one or more rows). The deletesql
>is property of a iboquery.
>If I put my delete clause in the sql property i
>get a error:error creating cursor handle
>here is how my delete clause typically looks.
>
>delete
>FROM BILLOFMATERIAL
>WHERE WORKSORDERNO = 1
>and linktype = 'GLASS'
>
>It works when I put it in the deletesql clause but gives me
>an exeception :EIB_DataSetError "Multiple records deleted"

It depends.
With a "simple" dataset (all rows from the same underlying table and good keylinks ensuring each row is unique, you don't need deletesql - just have RequestLive true and call the dataset's Delete method to delete the current row.

With a joined dataset, you can still get this behaviour but, of course, you can't delete rows from multiple tables using the single SQL statement that is created underneath when you call Delete. If you only want to delete from one of the tables in the join, you can select that table by entering its name into the KeyRelation property.

You can use DeleteSQL when
(a) you have a delete statement that involves parameters for fields that are not in your dataset - still confined to a single statement, though, and it can only involve the single row currently selected in the dataset - as you observed.
(b) you want to call a stored procedure to do the entire task associated with deleting the currently selected row, e.g. delete rows from more than one dataset, delete more than one row, update log tables, etc. etc. Generally, you are a lot more "free" in what you can achieve in your delete operation, because the dataset doesn't "know" what the SP will do.
In this case, your SP should be designed to take the unique keys of the selected row as input parameters.

When you use DeleteSQL, it doesn't matter whether you set RequestLive true or not (from the point of view of deleting, anyway) since a valid DeleteSQL automatically makes the dataset "live for deleting".

Generally, you should not try to do "fancy deletes" with dynamic sql. It's a lot safer and easier to test if complex processing associated with deleting rows is done on the server, through triggers.

hth
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________