Why am I unable to use obtain any results by using SQLite's 'LIKE' clause in an AIR Desktop App?

Question

I am trying to build a simple Library Management System for a class project. I am using SQLite to do that. The app looks like this:

At first I select and load everything from the table, which works fine as seen in the above image:

Here is the create table query:

 "CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, isbn TEXT, category TEXT, authors TEXT, publisher TEXT, edition INTEGER, copies INTEGER, pdf TEXT, cover TEXT)";

Here is the query that selects the whole table:

"SELECT id, title, isbn, authors, publisher, edition, copies, pdf, cover FROM books ORDER BY id";

To implement the search functionality, I tried using the 'LIKE' clause, like this:

"SELECT id, title, isbn, category, authors, publisher, edition, copies, pdf, cover FROM books WHERE title LIKE %'"+userInput+"'% ORDER BY id";

When I search with the above query, I get this error:

TypeError: Error #1009: Cannot access a property or method of a null object reference.
at (address-removed)/BooksContainer.as:124]

And here is line 124:

var numResults:uint = result.data.length;

I tried to change the code but after working with for about a week, I still get the same error. Yesterday, however I tried to approach it in a different as I was really frustrated and I was sure this approach would work. What I did is I selected the entire table like this:

public function searchBooks(userInput:String)
    {
        var stat:SQLStatement = new SQLStatement();
        stat.sqlConnection = _connection;
        stat.text = "SELECT id, title, isbn, category, authors, publisher, edition, copies, pdf, cover FROM books ORDER BY id";
        //stat.parameters['@search'] = "%" + userInput + "%";
        stat.execute();
        trace(stat.text);
        searchedString = userInput;
        stat.addEventListener(SQLEvent.RESULT, createSearchedContent, false, 0, true); 
    }


private function createSearchedContent(e:SQLEvent):void 
    {
        var result:SQLResult = e.target.getResult();
        _allBooks = result;
        trace(result.data);
        var numResults:uint = result.data.length;
        var currentY:Number = 0;
        var xIterator:Number = 0;

        for (var i:int = 0; i < numResults; i++)
        {
            var row:Object = result.data[i]; 
            var bookTitle:String = String(result.data[i].title);

            if (bookTitle.toLowerCase().indexOf(searchedString.toLowerCase()) > -1)
            {
                var book:Book = new Book(row.title, row.isbn, row.category, row.authors, row.publisher, row.edition, row.copies, row.pdf, row.cover);
                book.x = book.width * xIterator + 10;
                book.y = currentY;
                addChild(book);
                xIterator++;

                if (xIterator == 7)
                {
                    xIterator = 0;
                    currentY += book.height + 10;
                }

            }
        }
    }

Surprisingly enough, I got the same error. I didn't get it at all. Then I put this in the above loop:

var a:String = _main.search_txt.text.toLowerCase();
var b:String = String(row.title.toLowerCase());

trace(b.indexOf(a), a, b);

What I found out is that , in the trace I always get -1 for the searched term.

Here is the output window:

-1 data dark matter and the dinosaures
-1 data a history of religious ideas
-1 data steve jobs
-1 data digital logic design
-1 data thomas calculus
-1 data Data structures and algorithms in java
-1 data data structures in c++
-1 data data structures and algorithms in java
-1 data data structures and algorithms in java

So can any one help me out here?? I've only got a week to finish this project. Thanks in advance.

Answer 1

It looks that percent operators position issue.

Collect query

where something like '%foo%'

Your query

where something like %'foo'%

Put the percent operators inside single quotations.

"SELECT id, title, isbn, category, authors, publisher, edition, copies, pdf, cover FROM books WHERE title LIKE '%"+userInput+"%' ORDER BY id";

You also able to use like clause with parameters.
ActionScript and SQLite parameters on Select using Like