Convert REALSQLdatabase ("UPDATE …") ?

I have to updae (add two Fields in the RecordSet) a REALSQLdatabase and I have troubles with Add, Modify (UPDATE) and New …

Specifically:

[code]Dim CaritasDB As REALSQLdatabase
Dim OneRecord As String

// aound 60 If …/… Else …/… End If Lines to build the OneRecord string

CaritasDB.SQLExecute (OneRecord)[/code]

OneRecord is build line by line using a series (one for each field) of If Then Else End If blocks.

I’ve copied OneRecord contents int the clipboard and this gives:

UPDATE Caritas PNom='Laurence', Nom='Abeille', Wife='Chinclish', Sexe='False', Date_Birth='1984-03-02', Pays='Députée du Val-de-Marne', SentBy='CMS Les Charmettes', Date_In='2014-07-23', Date_Out='2016-05-10', Statut='Have a Job', WHERE ID=3

The returned error is Error 1 near PNom.
Xojo Docs says nothing aboud this UPDATE (excepted Database.SQLSelect)… not says Read Studio (I spent one hour or so to find both a REAL Studio 2012r2.1 and the appropriate license number (and you forgot how hard it was to set up Real Studio at these times - and Xojo too if you want to build…).

I was able to check the original code and it was that code that I used with REALSQLdatabase and it worked. Replacing REALSQLdatabase with SQLIteDatabase is not enough, apparently.

I nearly forgot: I checked in the sqlite.com language reference and found the UPDATE page, nut I only get the graphic syntax artwork: no code example available (for help).

What did I miss ?

TIA

UPDATE Caritas SET PNom

You are missing the keyword “SET” after the tablename.

update syntax:
UPDATE SET =, = WHERE

You wrote update

=,=, where

so you are missing the SET after the table name and you have an extra comma after the field=value list before the WHERE keyword

Thank you for your answers.

Wayne: you are right ! I lost the leading SET when I removed the first field (I forgot to mention that):

// a. Build the SQLExecute command string

[code] OneRecord = "UPDATE Caritas "

// Is CAF_ID empty or not ?
If TF_CAF_ID.Text = "" or TF_CAF_ID.Text = " " Then
  'OneRecord = OneRecord + "SET CAF_ID=NULL, " // THIS FIELD CANNOT BE NULL ! [BUT THE CODE IS CORRECT)
  OneRecord = OneRecord + "SET CAF_ID='', "
Else
  OneRecord = OneRecord + "SET CAF_ID='" + ReplaceAll(TF_CAF_ID.Text," ","") + "', "
End If[/code]

Antonio:
The “new first field” is:

// Is Prénom empty or not ? If TF_PNom.Text = "" or TF_PNom.Text = " " Then 'OneRecord = OneRecord + "PNom=NULL, " OneRecord = OneRecord + "PNom='', " Else OneRecord = OneRecord + "PNom='" + TF_PNom.Text + "', " End If

So you are right: no SET ! and I do not saw that ! Too many similar lines !

>you have an extra comma after the field=value list before the WHERE keyword
I do not saw that extra comma, but I recall something (an extra space before a comma, two spaces before WHERE…).

I investigate ! ARRRRGHHHH this is the older project !

In the new project: I understand ! I misplaced (in my unchanged sources *) the last comma !

  • I worked with a duplicate project that I throw away (trashed) once I stopped to work on the project (I was unable to make it working ! The Clipboard sql data string comes from that project !).

Wayne, Antonio: you saved my day

For Thanks = 0 To infinity Say "Thank you guys" Next

It works ! I’ve made the changes in the last to date project and… it works !
(on UPDATE only, but I certainly do the same erroe on the other Methods !).

CONCLUSION:

a. Beware when you delete an If block (SET),

b. Beware when you Copy / Paste code (the extra comma).

Having friends to help is nice (thanks friends), but avoiding errors is better ;-:slight_smile:

End of story (for today, waiting for the tests results):

the application seems to work fine now.

Thank you all !

Hi Emile,

Seeing as you are taking data entered through text fields and using that to update your database your should really be using prepared statements. Your application could be subject to SQL injection attacks.

Wayne

Hi Wayne,

thank you for your advice.

FWIW: I display data in … a Listbox; I display a Navigation window (card ?) to make changes / modify / add new entries thus TextFields (and a PopupMenu/EditField to select the country name: users enters country names using different spellings and at data collation time, I can see more than one entry for the same country…).

In my original design (end of 2012, so I used Real Studio), I wanted to add a window that allows to put “sql orders“ from a then EditField. Some people in the old forum adviced to not do that because of SQL injection… and so I scrapped the idea (*).

Now I have time to add refinements (time to learn how to code some refinements). I will investigate your advice (how to implement it). I saved the PDF for Prepared Statement (developer.xojo.com).

I wanted to add the ability to get the number of persons per countries for example (how many man / woman, etc.). A case comes at the end of 2015, and I send the data after I get them manually, using a different application. The users are mostly computer newbies.

PS: they will probably stay with Windows XP… forever or so (’till they have to change the computer that runs my application). So, building it with 2015r1 is not a problem in this case.