How to create update statement

[Hans]

Hi

I try to create an update statement during runtime using TMyQuery:

Qry.SQLUpdate.Add('UPDATE persoon SET FIELD1=');
Qry.SQLUpdate.Add('''');
Qry.SQLUpdate.Add(DataSet.FieldByName('FIELD1').AsString);
Qry.SQLUpdate.Add('"');
Qry.SQLUpdate.Add(', FIELD2="');
Qry.SQLUpdate.Add('''');
Qry.SQLUpdate.Add(DataSet.FieldByName('FIELD2').AsString);
Qry.SQLUpdate.Add('"');

etc.

I can use Add() or Append() but in both cases each added string comes on the next line (CR/LF). This means that I get an error after POST.

Can someone tell me how I should concatenate al these strings into one string without CR/LF

best regards,
Hans

[GEswin]

This should do the job:

Code: Select all

Qry.SQLUpdate.Clear;
Qry.SQLUpdate.Add( 'UPDATE persoon SET FIELD1 = '+QuotedStr(DataSet.FieldByName('FIELD1').AsString)+' , FIELD2 = '+QuotedStr(DataSet.FieldByName('FIELD1').AsString) ); 

But as it's a SQLUpdate, this is more correct and easier:

Code: Select all

Qry.SQLUpdate.Clear;
Qry.SQLUpdate.Add( 'UPDATE persoon SET FIELD1 = :FIELD1, FIELD2 = FIELD2' ); 

But you've forgot important part of query, the WHERE condition, right now it would update all records.

Regards

[Hans]

Thanks! This looks much better than my 'handmade' quotes. I used the first example, since the values come from another Dataset and it works fine.

best regards,
Hans

[Ikar]

Yet more correct and easy it would be to leave SQLUpdate property empty. If there is a unique key field in a table, MyDAC SQL Generator creates such query automatically.