I am on my iPad so can’t actually check it 
Ok. Let me know if there are errors. Ah you should be asleep. I’ll be online until about 10am your time.
Just tried it and got an error message saying COULD NOT PREPARE STATEMENT?
[code] // PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM Snippets WHERE Title LIKE %?%;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
// BIND THE SEARCH STRING AND EXECUTE
ps.Bind(0, TextField1.text)
Dim rs As RecordSet = ps.SQLSelect
// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Else
// CLOSE THE WINDOW
SearchWindow.close
// UPDATE THE LISTBOX
MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
End If[/code]
There goes theory (at least mine). Looking at it again the end result would be %‘Searchstring’% which is obviously wrong. I guess you’ll need to revert to the original. I very rarely use LIKE in SQL so D’Oh.
And you should still be asleep.
I will be up for a few hours yet - I did not get up until late today 
Off the top of my head You could include the % in the bind so it is placed inside of the single quotes.
[code] Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM Snippets WHERE Title LIKE ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
// BIND THE SEARCH STRING AND EXECUTE
ps.Bind(0, “%” + TextField1.text + “%”) //<-- % is included in the bind instead of in the statement above[/code]
Ok, then disregard that. That’s what I get for not reading the thread 
Jason, thanks, but that’s what I previously had
Do you mean Why I am using the Constants? IF so then its a habbit I got into when I was learning DB-Xojo stuff.
[quote]
E.g.
[code]Sub EggProdTable_SQLInsert(inDateCollected as String, inNumOfEggsCollected as integer, inWhoCollectedEggs as string, inNameOfHen as string, inNestingBoxID as string)
Dim ThisDateIs as New Date
Dim SQLInsertStatement As String = “insert into EggProdTable (DateCollected,RecordDate,NumOfEggsCollected,WhoCollectedEggs,NameOfHenLaid,NestingBoxID) values (?, ?, ?, ?, ?, ?)”
Dim prepInsert As SQLitePreparedStatement = SQLiteDB.Prepare(SQLInsertStatement)
// Set Bind Mappings for DB Table Columns
Const DateCollected = 0
Const RecordDate = 1
Const NumOfEggsCollected = 2
Const WhoCollectedEggs = 3
Const NameOfHenLaid = 4
Const NestingBoxID = 5
prepInsert.BindType(DateCollected, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(RecordDate, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(NumOfEggsCollected, SQLitePreparedStatement.SQLITE_INTEGER)
prepInsert.BindType(WhoCollectedEggs, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(NameOfHen, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(NestingBoxID, SQLitePreparedStatement.SQLITE_TEXT)
// Use Passed Values to Submit into our Table
prepInsert.Bind(DateCollected, inDateCollected)
prepInsert.Bind(RecordDate, ThisDateIs.SQLDateTime)
prepInsert.Bind(NumOfEggsCollected, inNumOfEggsCollected)
prepInsert.Bind(WhoCollectedEggs, inWhoCollectedEggs)
prepInsert.Bind(NameOfHenLaid, idNameOfHen)
prepInsert.Bind(NestingBoxID, inNestingBoxID)
// Execute Prepared SQL Statements
prepInsert.SQLExecute
End Sub[/code][/quote]
@Mike: no, Wayne means why you DON’T use the constants. In your code you define them but you do not use them. E.g.
[code]Const DateCollected = 0
prepInsert.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.Bind(0, inDateCollected)[/code]
Markus thanks! I overlooked that during testing … Bonehead move and thank you for pointing it out guys!!
One thing to be aware of when querying with Wildcards is when you use something like
SELECT column1 FROM table1 WHERE column1 LIKE '%SearchTerm'That % wildcard initiates a "leading wildcard search’ and the database engine cannot use an index to find the data, it has to trawl through every column1 and parse them 1 character at a time, left to right, to find the value.
With a table with few rows, you won’t notice any impact at all, but with a lot of data you’ll find this method is a known performance trouble area. Many a times I’ve been called in to fix a database problem and it’s down to something like this - it’ll work well in development and doesn’t become apparent till it’s been in production for some time.
If you find your table grows to find this problem, you’ll want to start looking at “Full Text Indexes” to cover this situation.
HI guys,
I changed the single quotes to double quotes and it now compiles and runs, but the search function does absolutely nothing
No error MsgBoxs, no search filtering?
It’s more about the amount of rows rather than the amount of columns.
What you display in the listbox is largely irrelevant, your sending a leading wildcard search request to the database and it’s how the database handles that request is the issue.
If your only ever going to have a few rows in your table that the query has to search through, you’ll be fine. Just be aware that every row added will cause the query to run a bit slower. Granted, your likely to need a good size table to feel or notice the pain. If in the future this part of the application has performance issues - this could be the problem.
Not saying “Don’t do it!” - it’s something I try to avoid in my queries if I can. Something to bear in mind
Success !
I removed the timer code which called a method, and have hardcoded a new method directly into this code. as follows:
[code] // PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM Snippets WHERE Title LIKE ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
// BIND THE SEARCH STRING AND EXECUTE
ps.Bind(0, “%” + TextField1.text + “%”)
Dim rs as Recordset = ps.SQLSelect
// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Else
// CLOSE THE WINDOW
SearchWindow.close
// CLEAR THE LISTBOX
MainWindow.Listbox1.deleteAllRows
// POPULATE THE LISTBOX
while not rs.eof
MainWindow.Listbox1.addRow rs.Field(“Title”).StringValue
rs.moveNext
wend
End If[/code]
THANK YOU ALL FOR YOUR HELP !
Thanks Patrick,
So if I remove the leading wildcard, and keep the trailing wildcard - would that be better??
Sorry for my ignorance
Only if you simply want to check if the string starts with the search term.
It’s perfectly reasonable, understandable and necessary to search with a leading wildcard. All I’m doing is pointing out a potential performance issue as your table scales - no more than that - just making you aware.
You, as a developer, need to make the call about your application. Whether a leading wildcard search will be fine for your needs or whether the table is big enough to benefit now from a Full Text Index. Either way, if you do the leading wildcard thing, keep an eye on it as your data grows to be sure it doesn’t bite you in the bum later.
(Oh, and don’t apologise!!! Nothing to apologise for!!!)
Patrick:
-
I appreciate you advising me of tips which I would never had considered.
-
Under no stretch of the imagination could I be considered a developer
-
Trailing only would be fine - it’s only for me to search through my list of snippets.
[quote=92377:@Richard Summers]
3) Trailing only would be fine - it’s only for me to search through my list of snippets.[/quote]
Great - if you only need to search for columns which start with your search term you don’t need the leading wildcard. No problems coming your way with that.
[quote=92377:@Richard Summers]
4) What did you mean by a full text index? - did you mean something like WHERE TITLE = “Joe”[/quote]
Nope - check out the Xojo blog on SQLite full text search Link here
Oh and check the SQLite docs for more info. Most DB platforms have a full text thing available in the technology.