How do I create a parent/child relation between two tables on the same layout? I am working on a story creation/writing app where I have a Story title list box/table and a Character Listbox/table. I would like to only see the characters belonging to the story I click on and able to add characters to a selected story.
I have a Stories and a Characters Table. The Characters table has the foreign key StoriesID
This is the Add Character button code:
Dim sql As String
sql = "INSERT INTO Characters (Name, StoriesID) VALUES (‘New Characters’, " + Str(mStoriesID) + “);”
DB.SQLExecute(sql)
The way I would like it to work is: selecting a story title in the story list box, then click the Add Character button in the Character list box. So I think that somehow I should retrieve the Stories ID from the selected title before INSERTing a new row in the Character table?
I am trying to make it work by placing the storyID grabbing code in a CellClick event handler in the StoriesList box, something like this:
If Me.ListIndex >= 0 Then
Dim SelectedStory as string
SelectedStory = me.Row
End If
I know it’s not correct, but cannot get the syntax correct. Any suggestions?
I placed this in the StoriesList/cellClick event handler:
//retrieve selected StoryID
If Me.ListIndex >= 0 Then
mStoriesID = me.RowTag(me.ListIndex)
LoadStories
LoadCharacters
End If
It seems that one of the problems was that the Listboxes need to be updated, so calling the Methods for that seems to do the trick.
update: nope, still getting a weird error. When clicking a story title two times, the character names changes to the one from another story… It seems to be a matter of double clicking the title to get the correct characters…
[quote=442615:@Emile Schwarz]Or use two Listbox: one (left) for the movie title, and the second for the Cast.
When you click in one Row to select a Movie Title, you Populate the second with the Cast entries for that movie.[/quote]
Yes, I have 2 Listboxes, and that is how I would like it to work. Sometimes it does, sometimes it doesn’t. I noticed that double clicking a story title shows the correct cast although I do not have a DoubleClick event handler in the StoryListbox.
Also, deleting a story still keeps the cast I the Cast table/listbox, which should not be possible: not cast without their story.
I seem to be tired (?), but I do not really understand.
You have code in CellClick. A double click is two single clicks
What code do you use to delete something (in the Listbox ? In the TABLE ?)
More edits
At left Listbox click, you issue a RightListbox.DeleteAll line to clear the Listbox from prior data.[/quote]
Yes, but because the code is in a Cellclick (singular), shouldn’t it be trigger the code with a single mouse click? It only seems to do it with two times clicking.
I use this delete button code for the Story table:
If StoriesList.ListIndex >=0 Then
Dim id As Integer
id = StoriesList.RowTag(StoriesList.ListIndex).IntegerValue
Dim sql As String
sql = “DELETE FROM Stories WHERE ID = ?”
Dim ps As SQLitePreparedStatement
ps = DB.Prepare(sql)
Where would I place this code? In the Story table? Not sure how this would work, how can Xojo figure this out?
“At left Listbox click, you issue a RightListbox.DeleteAll line to clear the Listbox from prior data.”
Why dont you return (in a MsgBox for example) the eventual error message after ps.SQLExecute(id) ?
It will tell you what happens (or put a Break point there and whatch in the debugger if an error occured and what is the error)
[quote=442628:@Emile Schwarz]You want to delete data from the Data Base ?
What data ? You wrote ID=?.
Why dont you return (in a MsgBox for example) the eventual error message after ps.SQLExecute(id) ?
It will tell you what happens (or put a Break point there and whatch in the debugger if an error occured and what is the error)[/quote]
It is working correctly now and probably did all the time. Deleting a story does also delete its cast, but the character listbox isn’t updated immediately. It takes a double click on another story to do this.
I don’t claim to understand everything in the code, because I have put this together while watching and studying the Xojo examples/videos. But I am getting baby steps insight in how things work. I am only using it for 1 week so, my brain is still adapting from Filemaker…
So, you are adding multiple casts entries for multiple movies ?
Example:
The Bourne Identity is a multiple movie (like Rambo, Terminator, etc.), so you will have one actor with the same name in the movie. You will add that actors name/character name multiple times ?
[quote=442669:@Emile Schwarz]So, you are adding multiple casts entries for multiple movies ?
Example:
The Bourne Identity is a multiple movie (like Rambo, Terminator, etc.), so you will have one actor with the same name in the movie. You will add that actors name/character name multiple times ?
I hope my english above is correct enough.[/quote]
Yes,. The app can contain more than 1 movie and every movie can have more characters.
It seems to be a matter of double clicking the title to get the correct characters…
