I am getting an error on PreparedSQLStatement.Bind. Is Bind required here?
SetNextCustTransNum As Boolean
Dim stmt As SQLitePreparedStatement = Self.Prepare(“SELECT Ct_CustTransNum FROM tblCounters”)
stmt.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
stmt.Bind(0, Ct_CustTransNum)
Dim rs As RecordSet = stmt.SQLSelect
If rs <> Nil Then
rs.Edit
rs.Field(“Ct_CustTransNum”).IntegerValue = rs.Field(“Ct_CustTransNum”).IntegerValue + 1
rs.Update
[quote=292325:@John Keidel]I am getting an error on PreparedSQLStatement.Bind. Is Bind required here?
Dim stmt As SQLitePreparedStatement = Self.Prepare(“SELECT Ct_CustTransNum FROM tblCounters”)
[/quote]
There are no columns to bind to in this statement
If you’re not going to use and autoincrement key then you need to do this VERY differently to make sure you do not run into issues if you have more than one connection to the DB at one time
Even a single user app with many threads that each have unique db connections can run into concurrency issues
The general pattern to avoid issues where two connections may get duplicate values is
start a transaction
update the db FIRST
read the new value from the table
commit
any ACID compliant DB will insure you never get overlapping or duplicate values using this pattern
why ?
the update will be paused waiting for any other updates to commit before it can / will proceed thereby insuring that no 2 get the same value