I’m working on a loop. I think the Do…Loop method will be best, but I’m getting an error or two and I’m not sure what I’ve done wrong.
Here’s the loop code.
[code]
If C_S_DB.Connect Then
sql = "SELECT id FROM addressbook"
rs = C_S_DB.SQLSelect(sql)
Dim y As Integer
If rs.Field("id").IntegerValue = 1000 Then
Do y = 1000 + 1
Loop Until rs.Field("id").IntegerValue = y
supidfield.text = Str(y)
Elseif rs.Field("id").IntegerValue <> 1000 Then
supidfield.text = Str(1000)
End If
Else
MsgBox("Could not connect to the addressbook database")
End If
End If[/code]
The SQLite DB is called C_S_DB, the record set is rs, and sql is a property with type string. I’m trying to pull data (id) from a column in the table addressbook. The loop is being used to see if the value 1000 is in the DB. If 1000 is not in the id column, then “1000” will be inserted into a textfield called supidfield. However, when the database is searched and 1000 is found in the id column, it adds a 1, changing 1000 to 1001. The loop takes the new value (1001) and searches again to see if it is listed in the id column. If not, it inserts “1001” into a textfield called supidfield. This continues on and on, starting from 1000. It’s like a counter, starting from 1000.
With the code above I’m getting the error messages:
I’ve been using the Intro to Xojo Programming book, but I’m not sure how to fix the errors. Any suggestions?
you’re lacking fundamental database concepts. Your code doesn’t make any sense to me.
In order to show your ids commaseperated in supidfield.text use better this way:
while not rs.eof
supidfield.text = supidfield.text + cstr(rs.field("id").IntegerValue) + ", "
rs.MoveNext
wend
Always fix syntax errors first then re-analyze the project. Frequently syntax errors will cause the compiler to throw other erroneous errors further down the code. In this case the Do y… line is broken, so the Loop has no do and also must be in error.
while not rs.eof
supidfield.text = supidfield.text + cstr(rs.field("id").IntegerValue) + ", "
rs.MoveNext
wend[/quote]
Where exactly would this go? I’m guessing it will replace these lines?
supidfield.text = Str(y)[/code] and [code]supidfield.text = Str(1000)
[quote=196526:@Kayla Gordon]I adjusted the Do statement. Now the do statement looks like this:
Do
y = 1000 + 1
Loop Until rs.Field("id").IntegerValue = y
supidfield.text = Str(y)
This stops the error messages, and runs the program, but I still need to research MoveNext.[/quote]
This always sets y = 1001
and never increments it beyond that
If C_S_DB.Connect Then
sql = "SELECT max(id) FROM addressbook"
rs = C_S_DB.SQLSelect(sql)
dim y as integer
if rs = nil then
break // double check the sql query as this SHOULD work and not give a nil record set
// unless the table doesn't exist or something ???
else
// we got SOMETHING back but it could be empty
if rs.eof then
// EOF right away means its empty
y = 1000
else
y = rs.idxField(1).IntegerValue + 1
end if
end if
supidfield.text = Str(y)
Else
MsgBox("Could not connect to the addressbook database")
End If
Kayla please forgive me my question but what do you want to do with this loop and query? it seems to me that you just try to check any amount, right? So why don’t you not use COUNT(), SUM() or another math function in your SQL query?
[quote=196535:@Kayla Gordon] @Norman Palardy I’ll give your code a try.[/quote]
While the code might work understanding why it does, or doesn’t do, what you indicated you wanted to do is fundamentally more important
No problem @Tomas Jakobs. I’m making a program where the user can either insert an ID number or click a button (Create ID No.) and one is created for them.
I’m starting the generated IDs with 1000 and up. I’m searching the database to make sure the user hasn’t already entered any id (1000 and up) into the database. If an ID is available (not in the DB), the id will appear in the text field to the left of the button. All of this is followed by clicking the save button which will store the new id, along with other information, into the DB.
Kayla for these kind of tasks always keep in mind, what will happen to your user and query when there is already a huge amount of recordsets existing? Will you keep counting them up? Dave’s suggestion is pointing to the right direction.
here a function I am using to get the next ID value of a recodset, may be it helps a little bit…
[code]Private Function GetNextIndex() As integer
dim result as Integer = 0
dim rs as recordset
try
rs = myCore.SQL("SELECT Identifier FROM AnyTable ORDER BY Identifier DESC LIMIT 1")
if rs <> nil then result = rs.Field("Identifier").IntegerValue
catch err as NilObjectException
Finally
end try
return result
End Function
[/code]
The Class myCore is a set of often used methods and functions and looks like this. I am marshaling all my SQL queries through this single class so I can switch Connection/Database Type easily:
[code]Function SQL(CustomSQL as String) As RecordSet
if CustomSQL <> “” then
#pragma BackgroundTasks false
dim db as Database
dim rs as RecordSet
db = self.CreateDatabaseProvider
try
If db.Connect then
rs = db.SQLSelect(CustomSQL)
else
rs = nil
end if
catch err as NilObjectException
Finally
end try
return rs
#pragma BackgroundTasks true
end if
End Function[/code]
[code]Function CreateDatabaseProvider() As Database
#pragma BackgroundTasks false
dim db as Database
try
select case app.DatabaseEngine
case "ODBC"
dim dbODBC as new ODBCDatabase
dbODBC.DataSource = app.DBNAME
dbODBC.UserName = app.DBUSER
dbODBC.Password = app.DBPASSWORD
db = dbODBC
case "MySQL"
dim dbMySQL as new MySQLCommunityServer
dbMySQL.host = app.DBHOST
dbMySQL.port=app.DBPORT.val
dbMySQL.databaseName = app.DBNAME
dbMySQL.userName = app.DBUSER
dbMySQL.Password = app.DBPASSWORD
db = dbMySQL
case "SQLlite"
dim dbSQLLite as new SQLiteDatabase
dim FI as new FolderItem
dbSQLLite.MultiUser = true
FI = SpecialFolder.ApplicationData.Child("jakobssystems").Child(app.LOCAL_DBNAME)
if FI <> nil then
if FI.Exists = false then CreateLocalFile
dbSQLLite.DatabaseFile = FI
else
CreateLocalFile
dbSQLLite.DatabaseFile = FI
end if
db = dbSQLLite
end select
[quote=196547:@Scott Griffitts]If you’re looking for the first gap in a sequence so you don’t have holes in your id range you can do something like this:
select ifnull(min(a.id) + 1, 1000)
from addressbook a
left join addressbook b on b.id = a.id + 1
where b.id is null
and a.id > 999
Note: The use of ifnull() assumes sqlite but other dbs usually have similar methods.[/quote]
And if you’re just creating ID numbers. Let your db do it for you. Add a record and return the primary key. Makes life easier.
