How to create update statement

How to create update statement

Postby Hans » Fri 04 Nov 2005 18:29

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
Hans
 
Posts: 52
Joined: Wed 24 Aug 2005 20:05

Postby GEswin » Fri 04 Nov 2005 20:06

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
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby Hans » Sat 05 Nov 2005 22:07

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
Hans
 
Posts: 52
Joined: Wed 24 Aug 2005 20:05

Postby Ikar » Tue 08 Nov 2005 08:45

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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components