How to do an SQL UPDATE in Delphi

[@prasertarputching1653]

ได้ความรู้เพิ่มขึ้นมากๆ ขอบคุณครับ

[@jerbear197]

Hi. I thought this UPDATE would be similar to the INSERT but I guess I was wrong. I have 13 edit boxes on my form and I'd like to be able to update all 13. I may only change data in 3 or 4 of the 13 fields but trying to determine which ones where altered might be an issue so I'm just choosing to update all fields using this code. The SQL statement looks fine but it's the error being generated that has me baffled...."SQL Error: SQL Logic error or missing database" My UPDATE Code... procedure TfrmMain.btnUpDateClick(Sender: TObject); var NStr : string; begin UserQuery.Close; UserQuery.SQL.Clear; UserQuery.SQL.Add('UPDATE StaffData SET Class = "'+edtFirstName.Text+'", "'+edtMiddleName.Text+'", "'+edtLastName.Text+'", '); UserQuery.SQL.Add('"'+edtFullName.Text+'", "'+edtUserID.text+'", "'+edtLoginName.Text+'", "'+edtExtn.Text+'", '); UserQuery.SQL.Add('"'+edtStatus.Text+'", "'+cbDept.Text+'", "'+edtEMail.Text+'", "'+edtRDS.Text+'", '); UserQuery.SQL.Add('"'+edtMonitors.Text+'", "'+edtZoiperName.Text+'", "'+edtZoiperCode.Text+'", '); UserQuery.SQL.Add('"'+edtOfficePKey.Text+'", "'+mnoNotes.Text+'" WHERE RecNo = "'+edtRecNo.Text+'"'); UserQuery.ExecSQL; UserQuery.Close; UserQuery.SQL.Clear; UserQuery.SQL.Add('SELECT * FROM StaffData Where RecNo = '+edtRecNo.Text); UserQuery.Open; end; The SQL statement reads like this... UPDATE StaffData SET Class = "John", "James", "Johnston", "John James Johnston", "test", "JJohnston1", "2323", "Active", "CRE - Creditors", "[e-mail]", "MCD-RDSLB01.corp.mycompany.com", "1", "[e-mail]", "0003CtxY7198FubX4923IsuX", "12345-67890-01234-98765-19823", "additional info here" WHERE RecNo = "166" Anyone any ideas as to what I'm obviously doing wrong....

[@MrLongITandCAT]

When you use the SET you must specify each field = to each value separated by commas Example: SET Surname = 'Smith", FirstName = "John", Grade = 10, Class = "B" WHERE...

[@jerbear197]

Just realised the word/field CLASS should not be in there, not that it made any difference to the issue. Still the same problem.

[@jerbear197]

@@MrLongITandCAT Many thanks for the reply. I was just trying that but still producing the same error. UserQuery.SQL.Add('UPDATE StaffData SET FirstName = "'+edtFirstName.Text+'", '); UserQuery.SQL.Add('MiddleName = "'+edtMiddleName.Text+'", LastName = "'+edtLastName.Text+'", '); UserQuery.SQL.Add('FullName = "'+edtFullName.Text+'", UserID = "'+edtUserID.Text+'", '); UserQuery.SQL.Add('WHERE RecNo = '+edtRecNo.Text+';'); Result of above sql statement reads like so.. UPDATE StaffData SET FirstName = "John", MiddleName = "Pius", LastName = "Johnston", FullName = "John Pius Johnston", UserID = "4444", WHERE RecNo = 166; Still getting this "SQL Error: SQL Logic error or missing database" My INSERT procedure works fine. but this update has got me scratching my 68 year old head....lol

[@MrLongITandCAT]

@@jerbear197 Did you add the field names Your Example: UPDATE StaffData SET Class = "John", "James", "Johnston", "John James Johnston", "test", "JJohnston1", "2323", Should be UPDATE StaffData SET Class = "John", FirstName = "James", Surname = "Johnston", FulllName = "John James Johnston", FieldName1 = "test", FieldName2 = "JJohnston1", FieldName3 = "2323",

[@jerbear197]

@@MrLongITandCAT My Table Name is StaffData, and my field names are ( FirstName, MiddleName, LastName, FullName, and UserID) and it is record number 166 so I believe this statement to be correct UPDATE StaffData SET FirstName = "John", MiddleName = "Pius", LastName = "Johnston", FullName = "John Pius Johnston", UserID = "4444", WHERE RecNo = 166; In delphi applied as above... UserQuery.SQL.Add('UPDATE StaffData SET FirstName = "'+edtFirstName.Text+'", '); UserQuery.SQL.Add('MiddleName = "'+edtMiddleName.Text+'", LastName = "'+edtLastName.Text+'", '); UserQuery.SQL.Add('FullName = "'+edtFullName.Text+'", UserID = "'+edtUserID.Text+'", '); UserQuery.SQL.Add('WHERE RecNo = '+edtRecNo.Text+';'); I see your 2 hours ahead of us. 11am here in Ireland.

[@profwtelles]

Do you have another about parameters?

[@cyber_t_]

It's finally OUT :) thanks sir

[@carienpretorius1241]

Hi Mr. Long how do I get rid of the widememo message

[@KatsWorldd]

Great channel. Thanks for the help

[@careldebeer7228]

Hello sir, my program gives me a syntax error in update statement, what can I do to solve this?

[@jerbear197]

Not sure what's going on in our thread but it does not seem to be accepting any more comments. Just wanted to say that the issue has been resolved.. I removed the last 4 lines of code from the procedure and it now works fine. Why that is I have no idea. It might have something to do with the ZEOS SQLIte Components I'm using... the 4 lines removed are.. UserQuery.Close; UserQuery.SQL.Clear; UserQuery.SQL.Add('SELECT * FROM StaffData'); UserQuery.Open;

[@MrLongITandCAT]

That is very weird. Glad you got it working in the end. Looks like a really nice program you are developing there.

[@jerbear197]

Thank you and thanks again for the help. I think you mean attempting to develop. Getting there slowly but surely. Just have to figure out now how i can get the sqlite auto created rowid out to a variable so I know which row/rec I'm working on.

[@jerbear197]

And figured out the rowid as well. But Now I'm trying to use INSERT with WHERE Reading data from a stringlist and looking to insert it into the database if it does not exist with this query but can't get it to work. INSERT INTO StaffData(Extension,FullName,ZoiperCode) Values ('1424', 'Lauren Moore', 'xxqeS3161HccQ6631LmhX3770') WHERE NOT EXISTS (SELECT 'Extension' = '1424'); So the 1424 is the phone extension and is unique so if it does not already exist in the DB I want to add/insert it with the other two values. User Name and code. I'm trying to do it from within SQLite Expert Pro. as well as Delphi code but have had no luck.

[@jerbear197]

in case anyone else is reading these. I got round that last problem by running 2 separate querys. UserQuery.Close; UserQuery.SQL.Clear; UserQuery.Sql.Text := 'SELECT * FROM StaffData WHERE Extension = '1234'; UserQuery.Open; if UserQuery.FieldByName('Extension').AsString='' then begin UserQuery.Close; UserQuery.SQL.Clear; UserQuery.SQL.Add('INSERT INTO StaffData(Extension,FullName,ZoiperCode) '); UserQuery.SQL.Add('Values( '1234', 'Jerry Mallett', 'xyz3452gtr9y67s456') '); UserQuery.ExecSQL; end;

[@MrLongITandCAT]

@@jerbear197 There isn't really a WHERE clause in SQL when using an INSERT statement unless you are copying data from one table to another.

[@williepanic]

with dmShop do begin qryShop.SQL.Clear ; qryShop.SQL.Add('UPDATE Stock '); qryShop.SQL.Add('Set ProductStock = :ProductStock - 1 '); qryShop.SQL.Add('From Stock WHERE ProductName = :pname '); with qryShop.Parameters do begin ParamByName('pname').Value := 'Coke' ; end; qryShop.ExecSQL ; end; what is wrong here?

[@thomasjamesashton7813]

syntax, :ProductStock - 1 and :pname ').. should be ....=: ...

[@molymunshef1519]

Thanks solve my problm

[@youcefdebih2660]

thanks so much it's great

[@mikhailvanniekerk9829]

No hate but why don't you use Variables?

[@jerbear197]

what's wrong with just using the actual edit,text components....