How to test for EOF when using RowSet

How do you test for EOF on a RowSet as opposed to a RecordSet?
I tried using:
Var sql As RowSet
While Not sql.eof Then
// mycode…

But I get an error saying “Type “RowSet” has no member named “eof”
while Not sql.eof Then”

I looked at some of the documentation but I could only find eof using recordSet as opposed to rowSet.
if you miss something open the docu and search for this class name.
i guess end of file is the wrong name here and they choose this above

Rowsets are also iterable, so you can use

Var sql As RowSet
For Each Row As DatabaseRow In sql
// yourcode

What I am trying to do is to determine if a specific record is found in a database search so that if the record is not found I can display a MessageBox instead of just displaying a blank screen.

EOF is now AfterLastRow when using RowSet, so your while/wend would look like this

While not sql.afterlastrow
//your code
sql.MoveToNextRow //note this is movetonextrow with rowset instead of MoveNext with recordset 

Also, are you defining the rowset with a SELECT statement? Your code does not look like it is. Might be something like this

Var sql as string = “SELECT * FROM yourDBtable”
Var rs as RowSet = app.db.SelectSQL(sql)

//then your while/wend loop

Lastly, use a variable name for the RowSet as something like “rs” or “rsSearch”. This is just a preference of my own, and I think others choose naming variables like this too. “sql” as a variable, I reserve for my sql statement. Again, just preference

Thanks Ryan. I tried your code and it works in that, if it can’t find the file, it does nothing. That’s ok because at least it isn’t displaying a blank screen but I would like to have a MessageBox that says “Record not found!”. Where would that code go?

Here is what I currently have:

Var searchString As String = searchBox.Text

Dim dbFile As FolderItem
dbFile = GetFolderItem(“E:\Xojo Projects\PrehistoricLife\dbase\dinobase.sqlite”)
Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile
If db.Connect Then
Var sql As RowSet
sql = db.SelectSQL(“SELECT * FROM dino WHERE name LIKE '%” + searchString + “%’”)
While not sql.afterlastrow
recordNum.Text = sql.Column(“record”).StringValue
tf_name.Text = sql.Column(“Name”).StringValue
tf_meaning.Text = sql.Column(“meaning”).StringValue
tf_pronounce.Text = sql.Column(“pronounce”).StringValue
tf_period.Text = sql.Column(“period”).StringValue
tf_group.Text = sql.Column(“group”).StringValue
tf_size.Text = sql.Column(“size”).StringValue
tf_lived.Text = sql.Column(“lived”).StringValue
tf_diet.Text = sql.Column(“diet”).StringValue
tf_fossils.Text = sql.Column(“fossils”).StringValue
FactFile.Text = sql.Column(“factfile”).StringValue

ImageComparison.SelectedPanelIndex = 0
Var dinoName As String
dinoName = tf_name.text + ".jpg"
Dim dinoPic As FolderItem
dinoPic = GetFolderItem("E:\Xojo Projects\PrehistoricLife\dino_images\" + dinoName)

If dinoPic <> Nil Then
  Dim pic As Picture
  pic = Picture.Open(dinoPic)
  ImageDino.Image = pic
End If

Var sizeName As String
sizeName = tf_name.text + "_size.jpg"
Dim sizePic As FolderItem
sizePic = GetFolderItem("E:\Xojo Projects\PrehistoricLife\dinosize\" + sizeName)

If sizePic <> Nil Then
  Dim sizeCompPic As Picture
  sizeCompPic = Picture.Open(sizePic)
  ImageSize.Image = sizeCompPic
End If
searchBox.Text = ""
recno = Val(recordNum.Text)
If recno > 1 Then
  PreviousBtn.Enabled = True
  PreviousBtn.Enabled = False
End If
If recno < rowCount Then
  NextBtn.Enabled = True
  NextBtn.Enabled = False
End If

End If

At the point where you determine that there is a not-found record.

Or instead of while, just use If:

If sql.AfterLastRow Then
  Messagebox "not found"
End If
1 Like

Little comment on the code you posted too. You’re using direct paths to files for your database file and the images.

dinoPic = GetFolderItem("E:\Xojo Projects\PrehistoricLife\dino_images\" + dinoName)

This is fine if you’re only making the app for yourself, but if you ever wanted to distribute the app, either selling or just making a distributable for friends, you’re going to run into trouble since they will not have this exact path E:\Xojo Projects\PrehistoricLife\dino_images\

The better approach is to use SpecialFolder. A common practice with db files and other files is to utilize the ApplicationData folder, so your path might look something like this:

Dim dinoPic As FolderItem = SpecialFolder.ApplicationData.Child("PrehistoricLife").Child("dino_images").Child("dinoName")

1 Like

Thanks, that works just fine.

Thanks Ryan. I am aware of the fact that using direct paths will only work for myself but I was using that just for the purposes of trying out xojo. I actually made myself a note to change that if I liked xojo and decided to purchase a license. Thanks also for the example code. Code examples are a great help to a noob like me. :slightly_smiling_face:

We were all noobs at one point in time. I think you will find that the people in this forum are nothing short of extraordinary. I’ve received (and still do to this day!) countless help from many of the active users and Xojo engineers.

Code samples are a big help to see it all fit. Take some time to go through the example projects in Xojo, search the forum for answers, and never be afraid to ask for help, no matter how simple you think your question is!


Thanks for the encouragement Ryan. Years ago I programmed in dBase III and CA-Clipper. Currently I have been programming in Purebasic which is somewhat different from Xojo. With xojo, I have to get used to the idea of placing objects on the screen and then programming each of those objects as opposed to writing procedures line by line… I also have to get used to the language syntax so I’ll be reading the reference manual quite a bit. Forums are a great way to get help although I have found that, on a lot of forums, when people reply to your questions, they sometimes think that you know more about the language than you really do. They are trying to be helpful but are unaware of your limited knowledge of the language. I just started experimenting with xojo about 2 weeks ago so I have a ways to go yet but I find that the best way to learn a language, at least for me, is to write an app. In the case of xojo I am duplicating an app the I wrote in Purebasic.

Thanks again for the help and encouragement.

Cheers, and stay safe. :slightly_smiling_face: