Devart Forums

Posted: **Thu 28 Aug 2008 23:59**

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

Thank You.

Posted: **Mon 01 Sep 2008 07:11**

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

Posted: **Tue 02 Sep 2008 02:50**

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]

Posted: **Wed 03 Sep 2008 14:15**

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.

Devart Forums

Example of SQLInsert, SQLUpdate and SQLDelete requested

Example of SQLInsert, SQLUpdate and SQLDelete requested