Getting error on PreparedSQLStatement.Bind

Databases
1

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

rs.Close

End If

Return Not Self.Error

2

Found a different way to do it WITHOUT using a PreparedSQLStatement.

SetNextCustTransNum As Boolean

Dim sql As String

sql = “UPDATE tblCounters SET Ct_CustTransNum = Ct_CustTransNum + 1”

Self.SQLExecute(sql)

Return Not Self.Error

3

[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

4

While what Norman said is correct, personally I would still opt for the single SQL statement, less overhead and much faster

5

SetNextCustTransNum As Boolean Self.SQLExecute "UPDATE tblCounters SET Ct_CustTransNum = Ct_CustTransNum + 1" Return Not Self.Error

6

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