Sub GetUniqueRandomRecord
Dim sqlstring As String, sqlCursor As Cursor
Dim RowCount, RandomRow as Integer
Dim uid, rv as string
' Assuming SQL Table named DataTable
' Columns of UniqueID Integer, Used Integer, Wanted String
sqlstring = "Select * from DataTable where Used < 1 order by UniqueID"
sqldata.Initialize(File.DirDefaultExternal, "DataDasename.db", False)
sqlCursor = sqldata.ExecQuery(sqlstring)
' Get row count
RowCount = sqlCursor.RowCount
' Get random record...
' (I'm assuming that if RowCount = 1 then Rnd(0,0) will give 0 -
' if not then an 'if/else' could be uesd for last record!)
RandomRow = Rnd(0,RowCount-1)
'
' Get Values from random record...
sqlCursor.Position = RandomRow
uid = sqlCursor.GetString("UniqueID")
rv = sqlCursor.GetString("wanted")
' Set used flag to ensure record not used again
If RowCount = 1 then
'Reset entire table for reuse!
sqlstring = "Update DataTable set Used = 0"
else
'Set one record as used
sqlstring = "Update DataTable set used = 1 where UniqueID ='" & uid & "'"
end if
sqldata.ExecNonQuery(sqlstring)
sqldata.Close
Return rv
End Sub
