Code for Updating SQLite database table

[montymay]

Hello fellow LiveCoders

I have a SQLite database table. I have written code that successfully updates existing records but I can't find code that will create new records. I searched this forum and found the following code--abridged for display here--that I amended to reflect my application, but it doesn't work. No new record is created.

Code: Select all

put fld "pcfnum" into tPCFnum
put fld "agency" into tDept
put fld "subject" into tSubject
put "INSERT INTO OpsDetails (pcfum,dept,subject) VALUES (" & merge ((" '[[tPCFnum]]' ,  '[[tDept]]', '[[tSubject]] ")) & ")" into tSQL
revExecuteSQL gDatabaseID, tSQL

I know I need to continue my studies of SQL, but in the meantime is the error in this code obvious? is there a better method? Thanks for any suggestions.

Monty May

[AxWald]

Hi,

set a breakpoint & examine tSQL. It should look like this:

Code: Select all

INSERT INTO myTable (Field_1, Field_2) VALUES 
(0, 'Something'), 
(2, 'WhatOther'), 
[...]
(723, 'Anything');

(Returns not mandatory, spaces at line end often required, single quotes for anything but integers)
Besides it you can try your tSQL manually in a db manager; for SQLite I use "SQLite Manager" (the Firefox plugin accessible via the Add-ons-pane). This saves a lot of time & nerves ;-)

Have fun!