search sqlite table

[Roboblue]

I am trying to search three fields (say, Title, Name, and Address) in a sqlite db table with 12 fields. The search is for a text string. I need to find if the fields have the string, and then show in a list box the Title for each recordid that has the string.
Any help is appreciated.
Tia

[Worm]

Use the where clause in your SQL statement. Use the "%" sign as a wildcard.

The following SQL statement would return all records that had a Title that contained the the substring "worm" or a Name that contained the substring "worm" or a Address that contained the substring "worm"

tblRS = SQLite.QueryToTable(db, "Select * from YourTable where (Title LIKE '%worm%') or (Name LIKE '%worm%') or (Address LIKE '%worm%')")

[Roboblue]

Worm, almost there

This works mostly, and it has helped me understand more about the query. But, when I display the Title in a list box, the recordID for the title is incorrect. Also, when using the wildcard %, how do I replace static text with a variable from an input box?
Thanx,

[Worm]

tblRS = SQLite.QueryToTable(db, "Select * from YourTable where (Title LIKE '%" .. Input.GetText("Input1") .. "%') or (Name LIKE '%" .. Input.GetText("Input1") .. "%') or (Address LIKE '" .. Input.GetText("Input1") .. "')")

As for having the wrong RecordID, I'd have to see your code to know what was wrong. You need to add the RecordID as the Listbox's ItemData and tehn get the Selected Item's ItemDate to determine the RecordID. Don't think you can use the Index of the listbox, because that won't work.

[Roboblue]

Worm Thanx for quick reply

I haven't experimented with the last post, yet. Will do so in a few.
Here is the apz to see what is happening in the list box when you press "GO".
The search input box is not working, so just press GO and it will search for the text "Blade". Then select the result in the list box and see the details in the info input box on the bottom. All of this spawned from the sglite project that came with the sqlite plug in.
There are only three records in the database with the apz, the real database has over 1700, and climbing.
If you do go through the whole project, that is not me driving the corvette

[Roboblue]

Worm, input works

That took about 20 seconds. Not only did the example work, but a light came on.
I have been doing things a little different in other projects in that I have been using several lines of code to get the same result as this one line. I have yet to really get the process of putting more than one function in one line of code. This has helped clear some confusion.
Thanx again.

[Worm]

Glad you got that working.

I made some modifications in the time I had left on my lunch here.

The app looks very nice, and has a nice work flow. Kudos!

Here's my 2 cents on using database's and listboxes.

I tend to stear clear of using the table that holds the results of a query as the basis for the underlying data. My reasoning is that items can be deleted from the database table, and still be in the LUA table (or vice versa). You were storing the row number of the LUA table in your listbox as the ItemData. This will work most of the time, problem comes when you start removing/deleting from the database and LUA table, your data changes row numbers change, but your listbox still points to the previous position.

I modified your app to store the RecordID as the ItemData. On selection, I query the database with a WHERE clause using the RecordID to retrieve a single selected record. Since the RecordID is unique, you are certain to be working with the correct record.

Again, this is my preference, and a suggestion. You certainly could continue using the tables the way you were, but I caution you to be sure you sync the table up after every delete, and repopulate the listboxes too.

hmmmm, can't attach APZ

[Roboblue]

Thanx Worm

About the LUA table thing. I did add a refresh to the list box that "reloads" data straight from the database. And, whenever the page changes and come back to the main, the on preload reloads the same. I added a progress box to the refresh cause it takes a few seconds to reload the whole db with close to 2000 records.
Thanx for the Kudos, it is far from being done, and when it is, I will be putting it to use for a lot of db projects/themes.
Looking forward to the apz with the changes you made. I am sure it will be an education.

[Worm]

I uploaded it to my website, click the link in the above post.

[Roboblue]

Thanx, Again!

Strangely enough, I understand how this works
One last thing (riiiight), I am having a problem getting the Trailer consistantly to run from the Trailer button on the main page, and from the View All Trailers page. It doesn't always work, but I'm I'm not getting errors. I have tried setting work folder path and adding full path to the file when passing it to the WMP page. Could you please take a quick look at it and see if I am omitting something? I have been over it so much I think I am just too close to the project to see the error.
Thanx

[Worm]

Never mind I didn't see the code on the previous page

[TJ_Tigger]

I noticed that you are obtaining the path to the vid separately. On the main page you obtain the video from the database and you append the filename to a particular string where the file is located. However on the trailer page you populate the listbox using a File.Find to locate all the files and populate the list box with Filename and then the full path. Then when you select a trailer to view, you use the same code to append the full path to the other code. So what I did was changed the vid = line to this

vid = sTrailer_Show

Lastly, I noticed that some of the code you have has the "L" capitalized in AutoPLay in the file path. I don't believe Windows cares about case, but LUA does care about case, I don't know if that would cause problems.

Tigg

[Roboblue]

thanx Worm and TJ

I recoded the trailer show so they are are the same, and it looks like I am getting consistant hits from either section, now.
I have also added a "useful links" popup for IMDB and CC Covers, using the popup plug in for the first time. I like it. When I finish with this, do you think it will be worth sharing with the community?

[Worm]

IMO Anything shared with the community can only benefit us all. Especially when it's source code. Looking at the way others have accomplished things has taught me more than any instructor or book ever has.

In this case, from the number of posts I've seen pertaining to SQLite, a sample app that does adding/deleting/editing of records, plus searching, seems to me would be a hot item.

[Corey]

I agree with Worm. I think it would be very popular. :yes