Syntax check please :)

Databases
42

Thanks Patrick - I was just reading that when you posted me the link :slight_smile:

Just realised though -
If I remove only the leading %, only the trailing %, or both - the search fails silently?

[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[/code]

This also produces and empty listbox:

ps.Bind(0, TextField1.text)
43

Fails or nothing in the table matches the search?

To be clear

%SearchTerm - searches for any column where the text STARTS with the SearchTerm (leading wildcard search)

SearchTerm% - searches for any column where the text ENDS with the SearchTerm. Trailing wildcard search and DB can use an index

%SearchTerm% - searches for any column where the text CONTAINS the SearchTerm (another leading wildcard search)

SearchTerm - searches for any column where the text IS the SearchTerm, no wildcards

44

Ahhhh - I simply need to check if my search string is contained anywhere in the Title column.
I get an emptied listbox.

45

Well, then you’ll need the %SearchTerm% variant and you’ll need to be aware of the fact it initiates a leading wildcard search and monitor for performance problems if the table gets large.

Job done :slight_smile:

46

Thanks for clarifying that - I appreciate it.

Shame there is not a more efficient way of searching a database for a string which avoids the overheads.
I will have to look into FTS.

47

It’s perfectly understandable when you get under the hood of the database engine - it makes sense. So, that’s why we have Full Text Search available for the bigger datasets.

48

Just when I thought I had a basic understanding of prepared statements - this threw me.
After my listbox is only displaying rows containing the search string, I need to re-populate the database with ALL the entries again.

I am trying to use a prepared statement in a button, but I have no idea how to formulate the code:
What do I bind to the question mark???

[code] // PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“select SRef, Title, Code from Snippets order by Title Asc ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

// BIND
ps.Bind(0,)
Dim rs as Recordset = ps.SQLSelect[/code]
49

You should use a SQLSelect here as there’s no data to bind.

50

Solved - thank you for the tip :slight_smile:

51

[quote=92392:@Patrick Delaney]Fails or nothing in the table matches the search?

To be clear

%SearchTerm - searches for any column where the text STARTS with the SearchTerm (leading wildcard search)

SearchTerm% - searches for any column where the text ENDS with the SearchTerm. Trailing wildcard search and DB can use an index
[/quote]
I think you have these two reversed
%SearchTerm will find it where the text in the DB ENDS with SearchTerm (% matches anything preceding search term)
SearchTerm% will find it where the text in the DB STARTS with SearchTerm (matches search term followed by anything)

52

Oops!!! Busy day yesterday!