Update Database Preparedstatement

Databases
1

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

Thanks

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

Session.DB.SQLExecute("BEGIN TRANSACTION")


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


Session.DB.SQLExecute("COMMIT")

'Session.DB.Commit()

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

then I call the Method in my update button
ContactUpdate

2

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

3

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.

4

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.