Additional parameter in query

[marsheng]

I'm using Delphi 6.

My SQL query (qryEventBikess) is

Code: Select all

SELECT * FROM eventbikes 
WHERE MemID = :MemID
AND EventID = :EventID

The query result is shown in a DBGrid. I now want select a row in the grid and delete the record.

The delete statement should be (stored under the SQLDELETE properties)

Code: Select all

DELETE FROM eventbikes 
WHERE EventMemID = :EventMemID 

but I cannot add the EventMemID Parameter to the SQL edit form generated by the query icon.

How do I do this?

It looks like EventMemID has to be in the main SQL statement before it is added to the parameter list.

EventBikesID is a auto-increment field.

[marsheng]

Another option is, how do I position the record pointer at the same record in the source table as the record pointer in the query ?

Then all I have to do is maintable.delete to delete the record.

[AndreyZ]

You don't need to provide the value for a parameter of a query that you set to the SQLDelete property. If the parameter name is equal to the field name, the value for this parameter is taken from this field of the current record. For example, if you have the following table:

Code: Select all

CREATE TABLE DEPT (
    DEPTNO  INTEGER  PRIMARY KEY NOT NULL,
    DNAME   VARCHAR(14),
    LOC     VARCHAR(13)
);

, you can use the following code:

Code: Select all

MyQuery.SQL.Text := 'select * from dept';
MyQuery.SQLUpdate.Text := 'update dept set dname=:dname, loc=:loc where deptno=:deptno';
MyQuery.SQLDelete.Text := 'delete from dept where deptno=:deptno';
MyQuery.Open;

When you update or delete records (using TMyQuery.Edit, TMyQuery.Post, and TMyQuery.Delete), parameters for the UPDATE and DELETE statements are taken from the deptno, dname, and loc fields of the current record.
Please note that if you leave the SQLUpdate and SQLDelete properties empty, MyDAC will generate the UPDATE and DELETE SQL statements automatically.

[AndreyZ]

The TDataSet.Delete method deletes the active record. If you want to delete not the active record, you should position a dataset to the needed record. For more information about positioning a dataset, please read the 'Navigating datasets' and 'Searching datasets' articles in the Delphi help.