# SQLite edit-update

**URL:** <https://forum.xojo.com/t/sqlite-edit-update/15293>\
**Category:** Databases\
**Created:** [March 27, 2014, 7:07pm UTC](https://forum.xojo.com/t/sqlite-edit-update/15293 "2014-03-27T19:07:58Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jim\_Smith](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/edb3f5/32.png) [@Jim\_Smith](https://forum.xojo.com/u/Jim_Smith)\
**Post date:** [March 27, 2014, 7:07pm UTC](https://forum.xojo.com/t/sqlite-edit-update/15293/1 "2014-03-27T19:07:58Z")

</div>

I have the following code that read a table, which has some field added, which need to be changed on the first run.

[code] dim sql as String = “Select \* FROM service”  
// run the query  
dim rs as RecordSet = gDB.SQLSelect(sql)  
if gDB.error then — whatever

while rs \<\> nil and rs.eof \<\> true  
‘’ \*\*\*\*\*\*\* fix cost \*\*\*\*\* This should only update records once

```
if db = 0 then
  if val(rs.field("ST_cost").StringValue) <> 0 then
 
   
    rs.edit
    if gDB.error then --- whatever

    rs.field("ST_cost_parts").StringValue = rs.field("ST_cost").StringValue
    
    rs.Update//check for error
    if gDB.error then --- whatever
    
    // Commit changes to the database
    gDB.Commit//check for error
    if gDB.Error then
      MsgBox "Error Commit service list: "+gdb.ErrorMessage [/code]

```

I get SQLite error" cannot commit - no transaction is active"

this ran ok on RealSQL

If I add `"Select rowid, * FROM service" `

result are the same, error.

if I add `gDB.sqlExecute "begin transaction"//check for error`  
just before the rs.edit I do NOT get the error.

NOTE NOTE: In all cases the update WORKED! The database was updated with the change.

My question: Do I need the “commit” statement. My understanding was that for the edit/update needed it to actually do the update.

---

<div class="post-metadata">

**Author:** ![Eugene\_Dakin](https://forum.xojo.com/user_avatar/forum.xojo.com/eugene_dakin/32/178_2.png) [@Eugene\_Dakin](https://forum.xojo.com/u/Eugene_Dakin)\
**Post date:** [March 28, 2014, 3:14am UTC](https://forum.xojo.com/t/sqlite-edit-update/15293/2 "2014-03-28T03:14:06Z")

</div>

Hello James,

After working with SQLite for a while, the commit statement is needed when using transactions. Tranactions look something like:

`db.SQLExecute("BEGIN TRANSACTION") 'SQL code past this point can be reversed (rolled back)
db.SQLExecute("do some SQL code here")
If db.Error then
    db.Rollback 'if there is an error, rollback or reverse the changes
Else
    db.Commit 'no errors so make the SQL code permanent
End If`

If the code is not a transaction then it would look something like:

`db.SQLExecute("do some SQL code here")
'Note: no commit is required`

A transaction allows the SQL code request to be reversed.

[A Link to Helpful Xojo Docs](http://documentation.xojo.com/index.php/SQLiteDatabase)

If there is an update to a transaction, I am sure I will be corrected 😉

---

<div class="post-metadata">

**Author:** ![Jim\_Smith](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/edb3f5/32.png) [@Jim\_Smith](https://forum.xojo.com/u/Jim_Smith)\
**Post date:** [March 28, 2014, 1:31pm UTC](https://forum.xojo.com/t/sqlite-edit-update/15293/3 "2014-03-28T13:31:45Z")

</div>

Hi Eugene  
Thanks. That just what I needed to know.

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 5:08pm UTC](https://forum.xojo.com/t/sqlite-edit-update/15293/4 "2020-10-29T17:08:43Z")

</div>