Hello everyone. I hope you’re Monday is going well!
I have two questions.
Q1. I was wondering if it was possible for the search bar to display results in a kind of drop down box as you type. For instance, like Google (see Figure 1). My goal is to have the search box on page 1 and the list (that is searched) on page 2. When the result is seen, the user can click the correct search result from the drop down box (page 1) and go directly to the name searched for in the listbox (page 2). Is this possible?
Figure 1: Google result with drop down box
Q2. I was doing the SQL database example in the Introduction to Programming with Xojo pdf and I designed a SQLite database. I’m very excited about having learned about databases, and thanks for your help! 
However, when I ran the program I noticed with every search submission empty rows were continuously being added into the database list. The searchfield Textchange Event only calls the created Method, Populate. The Populate code can be seen below. I looked at the Populate code to trying to figure out why additional lines were being added. I initially thought it was the + “%’)”, but I was wrong. By removing the + “%’)”, the search bar didn’t work at all. Can anybody spot the culprit? Thanks in advance!
Figure 2: Empty rows generated with each search submission.
Dim sql As String
Dim rs As RecordSet
sql = "SELECT name, email "
sql = sql + "FROM addressbook "
If SearchField.Text <> "" Then
sql = sql + "WHERE LOWER(name) LIKE LOWER('%" + SQLify(SearchField.Text) + "%')"
sql = sql + "OR LOWER(email) LIKE LOWER('%" + SQLify(SearchField.Text) + "%')"
End If
sql = sql + "ORDER BY name"
rs = MyDatabase.SQLSelect(sql)
If rs <> Nil Then
AddressBox.DeleteAllRows
While Not rs.EOF
AddressBox.AddRow(rs.Field("name").StringValue)
AddressBox.Cell(AddressBox.LastIndex,1) = rs.Field("email").StringValue
RS.MoveNext
Wend
End If
Maybe you have some empty entries in your database. First check your count of entries with:
SELECT COUNT(name) AS namecount FROM addressbook;
Add a rs to a Listbox goes simple with:
While Not rs.EOF
AddressBox.AddRow(rs.Field(“name”).StringValue, rs.Field(“email”).StringValue)
RS.MoveNext
Wend
Try these 3 suggestions (one at a time - not all together):
1) Change line 9 of your code to this (so there is a space before the word ORDER):
sql = sql + " ORDER BY name"
2) Try moving the code out of the TextChange event, and put it inside the Search button’s activate event.
3) Check the database file to ensure it has no empty entries (which for some strange reason keep getting added to the top).
Line 7 of your code also needs a space before the word “OR”.
Replace line 7 with this:
sql = sql + " OR LOWER(email) LIKE LOWER('%" + SQLify(SearchField.Text) + "%')"
@Richard Summers @Horst Jehle
Thanks Richard and Horst! I found the problem. If you look at the screen shot (Database Fun), you’ll notice that the search box is selected and also the Add button is selected too. So as I was typing in a name and pressing Enter, the Add function was being executed, thus adding rows. Now if I can try to make it so when I select the search box the Add button isn’t selected, but rather the Search button… Looking at that next.
Hang on, I might be wrong. Maybe the blue outline is messing with me. I’ll change the button out and try again.
Ensure the myAddButton is not set to default. Then, in the SearchField’s GotFocus event, put this code:
myAddButton.Enabled = False
mySearchButton.Enabled = True
Then in the SearchField’s LostFocus event, put this code:
myAddButton.Enabled = True
Hope this helps.
Okay, it wasn’t the Add button after all, it was the blue highlight confusing me.
I’m going to play around with it some more. Regarding the AddressBox.DeleteAllRows, I’m guess I add that to the Populate file? I added it at the very top, but it didn’t clear the cells. I also added it to the AddressBox control, as a Cell Action, but nothing there either. I’m going to play around with it some more.
Thanks for your help @Richard Summers. If I ever meet you, I’m buying you a beer!
Not necessary - it makes a change for me to be able to help someone else!
I posted the code to enable / disable the buttons above (not sure if you saw it)?
Post the link to an example database file (without real peoples details in it), and I will try a quick test.
Yes, I saw your button posts. I like the GotFocus and LostFocus events. I’m going to research them more.
From Richard Summers:
In order to make it MUCH easier for you to create database files in future, use this code:
// CHECK TO SEE IF THE NOTES DATABASE FILE EXISTS - IF NOT - CREATE IT
Dim fi As FolderItem = SpecialFolder.ApplicationData.Child(“myNoteApp Data”)
If Not fi.Exists Then fi.CreateAsFolder
fi = fi.Child(“NotesDatabase.db”)
Dim NotesDB As new SQLiteDatabase
NotesDB.DatabaseFile = fi
if not fi.Exists then
If NotesDB.CreateDatabaseFile Then
NotesDB.SQLExecute(“CREATE TABLE myTableName(NRef INTEGER PRIMARY KEY, NoteName TEXT, NoteContent TEXT)”)
// ERROR EXECUTING THE DATABASE SQL
If NotesDB.Error Then
MsgBox("Error: " + Str(NotesDB.ErrorCode) + " - " + NotesDB.ErrorMessage)
End If
// DATABASE COULD NOT BE CREATED - DISPLAY ERROR MSGBOX, AND THEN QUIT
else
MsgBox("Notes database could not be created - this app will now need to close!")
Quit()
end if
End if
You could put this code in the App.Open event, which would then check to see if the database already exists. If it doesn’t exist (the app is being run for the very first time), it will create a database file called NotesDatabase.db in a folder called myNoteApp Data.
Simply rename myNoteApp Data, NotesDatabase.db, and all instances of NotesDB appropriately for your app.
You will also obviously need to modify the line of code below appropriately for your particular app, so that it creates the desired table name, columns etc:
(“CREATE TABLE myTableName(NRef INTEGER PRIMARY KEY, NoteName TEXT, NoteContent TEXT)”
My code above should make it much easier and quicker.