Example of SQLInsert, SQLUpdate and SQLDelete requested

[bernr]

I was wondering if someone could give me an example on how to use SQLInsert, SQLUpdate and SQLDelete. when using TMSQuery.

Thank You.

[Dimon]

You can find the detailed description of its properties in the MyDAC help.

[bernr]

I already read the help, all it says is

property SQLInsert: TStrings;

Description
Use SQLInsert property to specify SQL statement that will be used when applying an insertion to a dataset. Statements can be parameterized queries. Names of the parameters should be the same as field names. Parameters prefixed with OLD_ allow to use current values of fields prior to actual operation.

Use TCustomMSDataSet.Options.ReturnParams to return OUT parameters back to dataset.

To create SQLInsert statement at design time, use query statements editor.

Note:
If you specify SQLInsert not depending onTCustomMSDataSet.Options.QueryIdentity the value of Identity filed won't be returned on execution Insert(Append)..Post.To avoid the problem, you should add following code to the end of SQLInsert:

SQL Server 2000:
SET : = SCOPE_IDENTITY()

SQL Server 7:
SET : = @@Identity

- name of Identity field.

Also you should set type of the last parameter to ptInputOutput for
TCustomDADataSet.BeforeUpdateExecute method:

if stInsert in StatementTypes then
Params[Params.Count - 1].ParamType := ptInputOutput;

Example

INSERT INTO Orders
(Shipname)
VALUES
(:Shipname)

See Also
SQLUpdate
SQLDelete

This does tell my how to use it. I set up the SQLInsert, but now how do I call it using a TMSQuery? Some code example would be nice.

Thanks Bern[/quote]

[Dimon]

You can not call SQLInsert query directly. SQLInsert is an SQL statement that is been called automatically when applying an insertion to a dataset.

For example, you have the following SQL query:

Code: Select all

MyQuery.SQL.Text := 'SELECT * FROM TableName';

Then when you use Insert .. Post, like this:

Code: Select all

MyQuery.Insert;
MyQuery.FieldByName('FieldName').AsInteger := 1;
MyQuery.Post;

when the Post method is called the SQLInsert query will be executed.