Update Database Preparedstatement

Hi, I have been banging my head all day trying to figure this out, I’m trying to update my database contact table, could anybody tell me what i’m doing wrong in my update code


in my Web Session I do

Dim dbFile As FolderItem = GetFolderItem("").Parent.Child("Resourses\\database.sqlite")

DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
If Not db.Connect Then
  ' Display an error page and log the error
  ' You should not show specifics of the error to users
  MsgBox("DB.Error:" + " " + DB.ErrorMessage + " Data Base Name: " + dbFile.NativePath + dbFile.Name)
End If

DB.MultiUser = True

Than I have in my update method


Dim ps As SQLitePreparedStatement = Session.DB.Prepare("UPDATE Contacts SET firstname=?, lastname=?, Company=?, address=?, city=?, State=?, zip=?, Phone=?, phoneext=?, cellphone=?, email=?, Notes=? WHERE ID=?")

If Session.DB.Error Then
  MsgBox("Error:" + " " + Session.DB.ErrorMessage)
End If

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(6, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(7, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(8, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(9, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(10, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(11, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(12, SQLitePreparedStatement.SQLITE_INTEGER)

ps.Bind(0, firstname.Text)
ps.Bind(1, lastname.Text)
ps.Bind(2, company.Text)
ps.Bind(3, address.Text)
ps.Bind(4, city.Text)
ps.Bind(5, state.Text)
ps.Bind(6, zipcode.Text)
ps.Bind(7, Phone.Text)
ps.Bind(8, ext.Text)
ps.Bind(9, cellphone.Text)
ps.Bind(10, email.Text)
ps.Bind(11, contactnotes.Text)
ps.Bind(12, ID.Text)

If Session.DB.Error Then
  MsgBox("Error:" + " " + Session.DB.ErrorMessage)
End If



If Session.DB.Error Then
  MsgBox("Error:" + " " + Session.DB.ErrorMessage)
End If

then I call the Method in my update button

where did you EXECUTE the PS???
looks like you created it properly, but then never did anything with it

wow, I feel stupid, so simple, with ps.SQLExecute() and it works,

when I create a new contact I use Session.DB.SQLExecute(“COMMIT”) and it worked good, so I reused most of that code for the update, but never it never click in me that I needed to change the execute command,

Thanks for the hint.

works good now.

Btw, wrapping a one line SQL statement in a transaction is unnecessary. You can simply issue the update, without the transaction and save 2 round trips to the db. Single SQL statements are already atomic, no need to wrap them in a transaction.