How can I use a delimiter to filter data from field then display as bullet points

[The_Hobbyist]

I'm trying to learn some database coding after being away for a while. I never was great at it and now I'm really struggling...

I'm using Visual Studio 2019 and SQLite, programming in VB.

What I need to achieve: Within my database table I have a handful of fields with one of those fields containing a bunch of different pieces of data (keywords). This data is separated by a semi-colon. I need to pull the data from that field, break the data up at the semi-colons and display those individual pieces of data (keywords) as text in bullet point form within a Label on my Form.

Currently there is some other stuff going on as well, like populating a DataGridView from the same table using using a binding source and whatnot. I'm unsure how to filter through the data table (or if I even have to) in order to pluck the individual pieces of data from the field in question.

What I think I understand: Please correct me if I am wrong...

1. Structure a database connection (dbCon) by defining the SQLiteConnection path.
2. Structure the data set (Adapter1) by defining my desired query table and data field(s).
3. Develop an SQLiteDataAdapter (adCardsTable) which is acts as a bridge between the database and the dataset.
4. Define a Data Table (dtCardsTable) which works essentially like RAM memory, independent from the database itself.
5. Define a Binding Source (bsCardsTable) which is used to display the data into various objects, such as a DGV.

Imports System.Data.SQLite

Public Class frmMain

    Private ReadOnly dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "\CardsSample.db;" & "Version=3;New=False;Compressed=True;")

    Dim Adapter1 As String = ("SELECT ID, Deck, Name, KeyWords, KeyPhrases, Picture FROM Cards ORDER BY ID ASC")

    Private WithEvents adCardsTable As New SQLiteDataAdapter(Adapter1, dbCon)

    Private dtCardsTable As New DataTable
    Private bsCardsTable As New BindingSource

6. Use the SQLiteDataAdapter (adCardsTable) to retrieve the data and place it into a Data Table (dtCardsTable), which functions sort of like RAM memory.
7. Call to Set Up Bindings and then Call to Set Up The DGV

    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        adCardsTable.Fill(dtCardsTable)

        SetUpBindings()
        SetUpDataGridView()

    End Sub

8. Bind the data table (dtCardsTable) to a binding source (bsCardsTable)
9. Bind all of the Data Table (dtCardsTable) to a DataGridView (DGV_Database)
10. Bind the data from the field "Name" to a label (lblCardName)
11. Bind the data from the field "KeyWords" to a label (lblKeyWords)
12. Bind the data from the field "KeyPhrases" to a label (lblKeyPhrases)
13. Bind the data from the field "Picture" to a picture box (PictureBox)

    Private Sub SetUpBindings()

        bsCardsTable.DataSource = dtCardsTable

        Me.DGV_Database.DataSource = bsCardsTable
        Me.lblCardName.DataBindings.Add("Text", bsCardsTable, "Name")
        Me.lblKeyWordsDB.DataBindings.Add("Text", bsCardsTable, "KeyWords")
        Me.lblKeyPhrasesDB.DataBindings.Add("Text", bsCardsTable, "KeyPhrases")
        Me.PictureBox.DataBindings.Add("Image", bsCardsTable, "Picture", True)

    End Sub

14. Use the binding source (bsCardsTable) to set the data source for the Data Grid View (DGV_Database).
15. Hide some of the columns of the DGV, showing only the two that I feel are required.
16. Set the column width for those two columns and manipulate a few other DGV attributes.

    Private Sub SetUpDataGridView()

        With Me.DGV_Database
            .DataSource = bsCardsTable
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .Columns("ID").Visible = True                  
            .Columns("Deck").Visible = False                   
            .Columns("Name").Visible = True                  
            .Columns("KeyWords").Visible = False              
            .Columns("KeyPhrases").Visible = False                                    
            .Columns("Picture").Visible = False                                         
            .AllowUserToAddRows = False                                                
            .RowHeadersVisible = False                                                  
            .ColumnHeadersVisible = False                                               
            .Columns(0).Width = 30
            .Columns(2).Width = 146
        End With

    End Sub

So, that is what I have so far and it all functions flawlessly. I can highlight an item in the DGV and in turn it displays the appropriate data into the appropriate form objects. The corresponding picture is displayed into the picture box and likewise, the corresponding keywords are displayed in a label on the form. They are of course displayed as a paragraph of words that are separated by semi-colons. What I am looking to do is display these keywords as bullet points.

It is my assumption that I need to pass the data through an Array (is that correct?) and filter the data from the field "KeyWords" using a semi-colon as a delimiter? Then I'll have to take each individual word that has been filtered and somehow create a bullet point and place that text next to it before going on to create another bullet point IF there is another keyword in the field.


I have done my very best to explain my situation to the best of my limited capacity. Should anyone wish to look at my project for more clarity, I have made it downloadable here:
http://www.degueldre-knives.com/Misc-Files/

Thanks for any and all direction offered.

[Zvoni]

CREATE TABLE "cards" (
    "ID"    INTEGER,
    "Keywords"    TEXT,
    PRIMARY KEY("ID")
)

INSERT INTO "main"."cards" ("ID", "Keywords") VALUES ('1', 'Text1;text2;text3;text4');
INSERT INTO "main"."cards" ("ID", "Keywords") VALUES ('2', 'Othertext1;Othertext2;OtherText3');

WITH 
RECURSIVE split(id,last,rest) AS (
SELECT ID, '', Keywords FROM cards
UNION ALL
SELECT ID, 
CASE WHEN instr(rest,';')>0 THEN
  substr(rest,1,instr(rest,';')-1)
ELSE
  substr(rest,1) 
END,
CASE WHEN instr(rest,';')>0 THEN
  substr(rest,instr(rest,';')+1) 
ELSE
  ''
END
FROM split WHERE rest<>'')
SELECT ID, last As Keywords FROM split WHERE last<>''
ORDER BY ID, lower(Keywords)

Returns

id	Keywords
1	Text1
1	text2
1	text3
1	text4
2	Othertext1
2	Othertext2
2	OtherText3

EDIT: This smells of a "misdesign" of the Database or at least that table.
There's usually no reason to keep delimited text in a single field, if you need it separated.
Create a child-table "keywords" (or whatever name) with a Foreign Key pointing to the Primary Key of its Master-Table "Cards"

EDIT2: Alternative Version of the Query (using IIF)
Same Results

WITH 
RECURSIVE split(id,last,rest) AS (
SELECT ID, '', KeyWords FROM Cards
UNION ALL
SELECT ID, 
IIF(instr(rest,';')>0,substr(rest,1,instr(rest,';')-1),substr(rest,1)),
IIF(instr(rest,';')>0,substr(rest,instr(rest,';')+1) ,'')
FROM split WHERE rest<>'')
SELECT ID, last As KeyWords FROM split WHERE last<>''
ORDER BY ID, lower(KeyWords)

EDIT3: Variant 3, with only one place to define which Delimiter

WITH 
DT(Delimiter) AS (VALUES(';')),
split(id,last,rest) AS (
SELECT ID, '', Keywords FROM Cards 
UNION ALL
SELECT ID, 
IIF(instr(rest,Delimiter)>0,substr(rest,1,instr(rest,Delimiter)-1),substr(rest,1)),
IIF(instr(rest,Delimiter)>0,substr(rest,instr(rest,Delimiter)+1) ,'')
FROM split 
INNER JOIN DT ON 1=1
WHERE rest<>'')
SELECT ID, last As SplitText FROM split WHERE last<>''
ORDER BY ID, lower(SplitText)

[The_Hobbyist]

Thank you for this, Zvoni.

EDIT: This smells of a "misdesign" of the Database or at least that table.
There's usually no reason to keep delimited text in a single field, if you need it separated.
Create a child-table "keywords" (or whatever name) with a Foreign Key pointing to the Primary Key of its Master-Table "Cards"

Hmmm....... When I was last working with databases, I found that relational database structure and how to tie it all together and query it properly afterwards was challenging. Designing the roadmap and then utilizing it properly so to speak. With help and patience from this forum however, I seemed to manage. Now that it seems apparent that I was about to set out on the wrong foot here, I am going to go back and look at that past project in an effort to re-discover the proper way of doing this. Reading your statement above reminded me of how fundamental a proper database structure is to a smooth, efficiently run program. I believe you to be correct when you say "There's usually no reason to keep delimited text in a single field". For now, I am off to ponder my database structure.

Thank you! I will surely be back with questions but in the meantime, I appreciate you.

[The_Hobbyist]

After an exhausting night of database research and then database re-structuring, I believe that I have a cleaner set of tables to work with. I am no longer utilizing a delimiter to separate data. As suggested, I have created a child table which is then linked to the primary table via a Foreign Key setup. OK.... So, my table structures look like this:



Then through the following code I have been able connect to the database, query the tables, retrieve and view the desired data within some form objects (DataGridView, Label & PictureBox). I am having a different problem now but I'm unsure if I should start another thread on the forum or continue in this one? For now I will continue here.

Problem 1: I am seeing a complete list of records for the entire database inside the DataGridView however, I would like to avoid seeing duplicates of the same card (based on the Card ID field). I believe this is happening because each card has multiple Key Words (fields) associated with it in the database and the DGV is displaying a record for each of those Key Words. How do I filter this to only show each individual Card just once?

Problem 2: I don't know where to start when it comes to taking each of the Key Words associated with a certain Card and generating a bullet list and then displaying that list within a Label. The bullet list is to be populated solely by each key word associated with the card selected within the DGV.

Below the code, I will share a screen shot of my project in its current state.

Imports System.Data.SQLite

Public Class frmMain

    Private ReadOnly dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "\CardsSample.db;" & "Version=3;New=False;Compressed=True;")

    Dim Adapter1 As String = ("SELECT
                                Cards.ID, Cards.Deck, Cards.Name, Cards.KeyWordsID, Cards.Picture,
                                KeyWords.KeyWords_ID, KeyWords.Word
                                FROM KeyWords
                                INNER JOIN Cards
                                ON KeyWords.KeyWords_ID = Cards.KeyWordsID
                                ORDER by Cards.ID")

    Private WithEvents adCardsTable As New SQLiteDataAdapter(Adapter1, dbCon)

    Private dtCardsTable As New DataTable
    Private bsCardsTable As New BindingSource



    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        adCardsTable.Fill(dtCardsTable)

        SetUpBindings()
        SetUpDataGridView()

    End Sub



    Private Sub SetUpBindings()

        bsCardsTable.DataSource = dtCardsTable

        Me.DGV_Database.DataSource = bsCardsTable
        Me.lblCardName.DataBindings.Add("Text", bsCardsTable, "Name")
        Me.PictureBox.DataBindings.Add("Image", bsCardsTable, "Picture", True)
        Me.lblKeyWordsDB.DataBindings.Add("Text", bsCardsTable, "Word")

    End Sub



    Private Sub SetUpDataGridView()

        With Me.DGV_Database
            .DataSource = bsCardsTable
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect 
            .Columns("ID").Visible = True                               
            .Columns("Deck").Visible = False                           
            .Columns("Name").Visible = True                            
            .Columns("KeyWordsID").Visible = False                     
            .Columns("Picture").Visible = False                         
            .Columns("KeyWords_ID").Visible = False                    
            .Columns("Word").Visible = False                            
            .AllowUserToAddRows = False                                 
            .RowHeadersVisible = False                                 
            .ColumnHeadersVisible = False                               
            .Columns(0).Width = 30
            .Columns(2).Width = 146
        End With

    End Sub



    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click

        Me.Close()

    End Sub

End Class

The below image shows The Emperor card being selected from the DGV which in turn displays the correct image in the picturebox, the correct name in the Label at the top and even one of the correct KeyWords all associated with The Emperor card. I need to eliminate the duplicate cards from the DGV though, meaning show just 1 Emperor card while creating a bullet list for the 5 different key words associated with that card.


I'm looking forward to help and suggestions. Thank you.

[Zvoni]

Wrong way around. The foreign key-column belongs in table keywords, and points to id of cards

[The_Hobbyist]

I'm sorry, I don`t understand. I thought that I had my database structured that way? The way I understand it, I have the foreign key-column in table KeyWords.KeyWords_ID and it points to Cards.KeyWordsID. Does this not tie the Cards.KeyWords data field to the KeyWords.KeyWords_ID data field? Here is a better screenshot of the database structure:



My data table seems to retrieve all of the relevant data from across both tables. I then bind it to and display it in the DGV. With that said, I'm still unsure how to remove the duplicate card names from the DGV? Does this somehow tie into my foreign key mix up? I also wish to display all of the keywords in the label on the form. I'll attach a new screenshot which widens the DGV, better showing the data from both tables. The ID field in the DGV is from the Card table while I have the KeyWords_ID field hidden from view in the DGV.




My complete code for this entire project is as follows:

Imports System.Data.SQLite

Public Class frmMain

    Private ReadOnly dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "\CardsSample.db;" & "Version=3;New=False;Compressed=True;")

    Dim Adapter1 As String = ("SELECT
                                Cards.ID, Cards.Deck, Cards.Name, Cards.KeyWordsID, Cards.Picture,
                                KeyWords.KeyWords_ID, KeyWords.Word
                                FROM Cards
                                JOIN KeyWords
                                On Cards.KeyWordsID = KeyWords.KeyWords_ID
                                ORDER by Cards.ID")

    Private WithEvents adCardsTable As New SQLiteDataAdapter(Adapter1, dbCon)

    Private dtCardsTable As New DataTable
    Private bsCardsTable As New BindingSource



    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        adCardsTable.Fill(dtCardsTable)

        SetUpBindings()
        SetUpDataGridView()

    End Sub



    Private Sub SetUpBindings()

        bsCardsTable.DataSource = dtCardsTable

        Me.lblCardName.DataBindings.Add("Text", bsCardsTable, "Name")
        Me.PictureBox.DataBindings.Add("Image", bsCardsTable, "Picture", True)
        Me.lblKeyWordsDB.DataBindings.Add("Text", bsCardsTable, "Word")

    End Sub



    Private Sub SetUpDataGridView()

        With Me.DGV_Database
            .DataSource = bsCardsTable
            .DefaultCellStyle.Font = New Font("Tahoma", 9.75, FontStyle.Regular)
            .DefaultCellStyle.SelectionBackColor = Color.Pink
            .DefaultCellStyle.SelectionForeColor = Color.Red
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .Columns("ID").Visible = True
            .Columns("Deck").Visible = False
            .Columns("Name").Visible = True
            .Columns("KeyWordsID").Visible = False
            .Columns("Picture").Visible = False
            .Columns("KeyWords_ID").Visible = False
            .Columns("Word").Visible = True
            .AllowUserToAddRows = False
            .RowHeadersVisible = False
            '            .ColumnHeadersVisible = False          '<--- ADDS THE COLUMN HEADERS FOR FORUM POST CLARITY
            .Columns(0).Width = 25
            .Columns(2).Width = 115
            .Columns(6).Width = 145
        End With

    End Sub



    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click

        Me.Close()

    End Sub

End Class

[Zvoni]

You have in "Cards" a Column "KeywordsID" which is a Foreign Key to "Keywords_ID" in "KEywords".
Wrong. It has to be the other way around.

You need a column "CardsID" in "Keywords" being a Foreign Key to "ID" in "Cards"

You have a card called "Fool" (1 Card), which has multiple "Keywords" (n keywords) ("Childlike", "Innocent" etc.) --> "1:n" --> The "n" always points to the table which needs the Foreign Key, the "1" to which table that Foreign Key points to

And i seem to see some other issues (Deck? Picture?)

EDIT: Going by your Screenshot of your Form:
You need two Queries:
1) the first Query is for your ListBox on the Left representing the Cards.
SELECT ID, Name FROM Cards
2) The second Query, which gets executed INSIDE the OnClick, OnSelection, whatever Event there is if you click on a row of the Listbox
SELECT Word FROM Keywords WHERE CardID=IDOfClickedCardInListBox
And the result of that Query goes into your Label, or whatever control you have there for the Keywords.
I would probably use a selfexpanding/shrinking ListBox in flat and "unclickable" Style (without Scrollbars).
Basically a simulation of a multiline Label

[The_Hobbyist]

You need a column "CardsID" in "Keywords" being a Foreign Key to "ID" in "Cards"

I have changed a few things in the tables now. For example, I removed a field or two which I wasn't using at this time. They were reserved for later, once I got this code straightened out. For the sake of simplicity, I'll just include the relevant fields and have modified the tables to reflect that.

Regarding the reply above, am I not doing what I am instructed to do here? ...Or is it still backwards? Looking at the screenshot below, the way I understand it, I currently have a CardsID field within my KeyWords table and that CardsID field is set as the foreign key which in turn is pointing to the ID field within the Cards table.

[Zvoni]

Now it looks good.

As i wrote above: You need 2 Queries (resp. 3 Queries)
1) SELECT ID, Name FROM Cards ORDER BY Name
Take the result from that and fill up that Grid on your left side

After you click on an entry on left side grid,
INSIDE the Click-Event (Or Selection-Event, no idea what you use) you have to grab the ID from its column (or from whereever)
and execute the following 2 Queries
2) SELECT Picture FROM Cards WHERE ID=?
Set the Parameter for ID with that ID from the Column and grab the Picture
Resulting RecordCount should be 1 --> Check for NON-NULL-Result of Picture

3) SELECT Word FROM Keywords WHERE CardsID=?
Set the Parameter for CardsID with the ID you grabbed from the Column
Use that result to populate your Label (or whatever control you use) for your bulletpoints
RecordCount can be anything


It's the same for adding, changing, deleting Keywords:
You FIRST have to select a Card (to get its ID)
Adding a new Keyword
INSERT INTO Keywords (cardsID, Word) VALUES(?,?)
Set the 2 Parameters: First to the ID, second to the Textbox (or whatever)

Changing a single Keyword doesn't work in your setup. You need a separate ID-Column in Keywords

Deleting all Keywords (!!!!) --> You don't have a separate ID for each Keyword, so be careful with this
DELETE FROM Keywords WHERE CardsID=?
Set the Parameter for CardsID with the ID

[Zvoni]

btw: If you want to go back to your "original" concept (one Field with delimited Text)

It would still be 2/3 Queries

1) SELECT ID, Name FROM Cards ORDER BY Name
Take the result from that and fill up that Grid on your left side

After you click on an entry on left side grid,
INSIDE the Click-Event (Or Selection-Event, no idea what you use) you have to grab the ID from its column (or from whereever)
and execute the following 2 Queries
2) SELECT Picture FROM Cards WHERE ID=?
Set the Parameter for ID with that ID from the Column and grab the Picture
Resulting RecordCount should be 1 --> Check for NON-NULL-Result of Picture

3)

WITH 
    DT(Delimiter) AS (VALUES(';')),
    split(id,last,rest) AS (
        SELECT ID, '', Keywords FROM Cards 
        UNION ALL
        SELECT ID, 
        IIF(instr(rest,Delimiter)>0,substr(rest,1,instr(rest,Delimiter)-1),substr(rest,1)),
        IIF(instr(rest,Delimiter)>0,substr(rest,instr(rest,Delimiter)+1) ,'')
        FROM split INNER JOIN DT ON 1=1 WHERE rest<>'')

SELECT ID, last As KeyWord FROM split WHERE last<>'' AND ID=?  --HERE THE PARAMETER!!
ORDER BY lower(KeyWord)

Set the Parameter for ID with the ID you grabbed from the Column
Use that result to populate your Label (or whatever control you use) for your bulletpoints
RecordCount can be anything

[The_Hobbyist]

Thank you but no, I don't plan on going back and using a delimiter now. I can see that you were correct from the start and that my table was structured incorrectly. I feel its important to do this properly if I'm going to learn anything.

Going by your direction here:

1) SELECT ID, Name FROM Cards ORDER BY Name
Take the result from that and fill up that Grid on your left side

I am able to do this successfully and hopefully correctly using my code below. With that said, I have added the "Picture" field to the query because I am binding that data to the picture box. I hope this isn't a mistake to do so? I have also changed the order. I have set the ORDER by ID instead of by Name since in this case the card's ID is how I want the cards displayed in the DGV. I have done all of this by implementing the following code:

Imports System.Data.SQLite

Public Class frmMain

    Private ReadOnly dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "\CardsSample.db;" & "Version=3;New=False;Compressed=True;")

    Dim Adapter1 As String = ("SELECT ID, Name, Picture FROM Cards ORDER BY ID ASC")

    Private WithEvents adCards As New SQLiteDataAdapter(Adapter1, dbCon)

    Private dtCards As New DataTable                                                    
    Private bsCards As New BindingSource                                                



    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        adCards.Fill(dtCards)

        setupBindings()                                                                
        setupDGV()                                                                     

    End Sub



    Private Sub setupBindings()

        bsCards.DataSource = dtCards

        Me.lblCardName.DataBindings.Add("Text", bsCards, "Name")                       
        Me.PictureBox.DataBindings.Add("Image", bsCards, "Picture", True)              

    End Sub



    Private Sub setupDGV()

        With Me.DGV
            .DataSource = bsCards                                                       
            .DefaultCellStyle.Font = New Font("Tahoma", 9.75, FontStyle.Regular)       
            .DefaultCellStyle.SelectionBackColor = Color.Pink                           
            .DefaultCellStyle.SelectionForeColor = Color.Red                           
            .ReadOnly = True                                                            
            .AllowUserToResizeColumns = False                                          
            .AllowUserToResizeRows = False                                             
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect                   
            .Columns("ID").Visible = True                                               
            .Columns("Name").Visible = True                                             
            .Columns("Picture").Visible = False                                         
            .AllowUserToAddRows = False                                                
            .RowHeadersVisible = False                                                 
            .ColumnHeadersVisible = True                                               
            .Columns(0).Width = 28                                                      
            .Columns(1).Width = 147                                                    
        End With

    End Sub


End Class

Regarding your second instruction:

2) SELECT Picture FROM Cards WHERE ID=?
Set the Parameter for ID with that ID from the Column and grab the Picture
Resulting RecordCount should be 1 --> Check for NON-NULL-Result of Picture

I am unsure why I need to query the Cards table for the picture data that aligns with the chosen ID in the highlighted DGV row? Each record in the Cards table is unique, meaning that with each ID, a unique card name and picture go along with it. There will never be a record in the Cards table that shares an ID, name and/or picture with any other record throughout the table. Therefore, can I not simply query the Cards table once like I have done and pull the picture data out at that time and then simply bind it to the Picture box? If there is a reason that I should not do this, I don't know what it is and would like some clarification if possible please. I'm not trying to be difficult, I'm genuinely curious if what I am doing is incorrect? Again, my code to achieve what I just mentioned is as follows:

Dim Adapter1 As String = ("SELECT ID, Name, Picture FROM Cards ORDER BY ID ASC")

    Private Sub setupBindings()
        bsCards.DataSource = dtCards
        Me.lblCardName.DataBindings.Add("Text", bsCards, "Name")                        
        Me.PictureBox.DataBindings.Add("Image", bsCards, "Picture", True)               
    End Sub

Looking at your third instruction, I believe that I have a start in the right direction but please, correct me if I'm way out in left field. You said:

3) SELECT Word FROM Keywords WHERE CardsID=?
Set the Parameter for CardsID with the ID you grabbed from the Column
Use that result to populate your Label (or whatever control you use) for your bulletpoints
RecordCount can be anything

As a means to grab the "ID" value from the highlighted DGV row, I have implemented the following code and it returns favorable results (using a message box to return the value as a visual aid):

    Private Sub DGV_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGV.CellContentClick

        Dim X As String                                                                
        X = DGV.SelectedRows.Item(0).Cells(0).Value                                     

        MessageBox.Show(X)
    End Sub

At this point, I am struggling to move forward and out of time for today. It is my understanding that I will need to structure another query as you have noted (SELECT Word FROM Keywords WHERE CardsID=?) and I have captured the ID from the DGV selection but then I get lost. Also, I think its important that I wrap my head around my first set of questions that revolve around your second instruction above. Can I pull the Picture data from the original query right from the start or does it need to be a second query and why?

I am out of time this morning and have to head to my regular job. I will revisit this after work and see what I can come up with. Lastly, I have opted to scrap the idea of populating a label with bullet points and have since chosen to do as you recommended. I have implemented a list box as the object that will hold the Words from KeyWord table.

This is currently how the form looks with the listbox (framed for identification) in place of the old label.

[The_Hobbyist]

Well....So far I have come up with this:

    Private Sub DGV_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGV.CellClick

        Dim X As String
        X = DGV.SelectedRows.Item(0).Cells(0).Value

        Dim Adapter2 As String = ("SELECT Word FROM KeyWords WHERE CardID=" & X)

        Dim adKeyWord As New SQLiteDataAdapter(Adapter2, dbCon)

        Dim dtKeyWord As New DataTable
        Dim dsKeyWord As New DataSet

        adKeyWord.FillSchema(dsKeyWord, SchemaType.Source, "KeyWords")
        adKeyWord.Fill(dsKeyWord, "Word")
        dtKeyWord = dsKeyWord.Tables("Word")

        Me.ListBoxKeywords.ClearSelected()
        Me.ListBoxKeywords.DataSource = dtKeyWord
        Me.ListBoxKeywords.DisplayMember = "Word"

    End Sub

It seems to populate the listbox as I had hoped. I'm unsure whether this could be cleaned up or not? I used some example code I found online and adapted it to work with my parameters. I think....

Is this the correct way of, or at the very least, one of the correct ways of doing what I want to do? Or have I made a mess of it?

Shown below is the listbox displaying the data from the Words field from the KeyWords table in relation to the row selected in the DGV:

[wes4dbt]

I believe I helped you setup a gemstone app. I might be wrong but if so,

Then why not use a dataset and setup a relation between the two tables like we did in that app. Then keywords would be automatically displayed everytime you move from card to card in the dgv.

If not that then I'd use your bindingsource PositionChanged event and not dgv CellClick event.

[The_Hobbyist]

I believe I helped you setup a gemstone app. I might be wrong but if so,

Then why not use a dataset and setup a relation between the two tables like we did in that app. Then keywords would be automatically displayed everytime you move from card to card in the dgv.

If not that then I'd use your bindingsource PositionChanged event and not dgv CellClick event.

You did help me with that app! Unfortunately I am unable to find all of that source code in its entirety. I have found snippets of it that were stored on a flash drive but the entire project was lost to a bad hard drive some time ago.

[Zvoni]

Looks good in post 12.

regarding the picture, and why I don’t select it in the first query:
you actually only need the picture AFTER you select an entry in your grid.
there is no reason to hold a bunch of pictures in memory when you don’t need them.

and in any case: you always only display one picture at any given time

Yes, you said something about databinding, but i will not go down that rabbit hole.

rule of thumb dealing with databases: you only select what you really need at any given time

[wes4dbt]

You did help me with that app! Unfortunately I am unable to find all of that source code in its entirety. I have found snippets of it that were stored on a flash drive but the entire project was lost to a bad hard drive some time ago.

This is a an example of how the relation and bindings would be setup,

Public Class Form1
    Private data As New DataSet

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        SetUpDataSet()
        LoadTables()

        Me.BindingSource1.DataSource = data
        Me.BindingSource1.DataMember = "Cards"
        Me.DataGridView1.DataSource = BindingSource1

        Dim bs As New BindingSource
        bs.DataMember = "CardWords"
        bs.DataSource = Me.BindingSource1
        Me.ListBox1.DataSource = bs
        Me.ListBox1.DisplayMember = "Word"

    End Sub

    Private Sub SetUpDataSet()
        Dim CardsDataTable As New DataTable
        Dim WordsDataTable As New DataTable
        CardsDataTable.TableName = "Cards"
        CardsDataTable.Columns.Add("ID", GetType(Integer))
        CardsDataTable.Columns.Add("Name", GetType(String))

        WordsDataTable.TableName = "Words"
        WordsDataTable.Columns.Add("ID", GetType(Integer))
        WordsDataTable.Columns.Add("CardID", GetType(Integer))
        WordsDataTable.Columns.Add("Word", GetType(String))

        data.Tables.Add(CardsDataTable)
        data.Tables.Add(WordsDataTable)

        data.Relations.Add("CardWords", data.Tables("Cards").Columns("ID"), data.Tables("Words").Columns("CardID"), False)

    End Sub

    Private Sub LoadTables()
        data.Tables("Cards").Rows.Add(1, "Card1")
        data.Tables("Cards").Rows.Add(2, "Card2")
        data.Tables("Cards").Rows.Add(3, "Card3")

        data.Tables("Words").Rows.Add(1, 1, "Word1of1")
        data.Tables("Words").Rows.Add(2, 2, "Word1of2")
        data.Tables("Words").Rows.Add(3, 1, "Word2of1")
        data.Tables("Words").Rows.Add(4, 2, "Word2of2")


    End Sub
End Class

Of course you would get your data from your database.

I just dropped a datagridview, listbox and bindingsource on the form from the toolbox.

[The_Hobbyist]

.
regarding the picture, and why I don’t select it in the first query:
you actually only need the picture AFTER you select an entry in your grid.
there is no reason to hold a bunch of pictures in memory when you don’t need them.

This makes excellent sense to me. I think I'll go back and alter it so I do as you suggest and only call the picture out when needed. Smart!

Thank you for the help! It has been greatly appreciated.

[The_Hobbyist]

This is a an example of how the relation and bindings would be setup,

Public Class Form1
    Private data As New DataSet

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        SetUpDataSet()
        LoadTables()

        Me.BindingSource1.DataSource = data
        Me.BindingSource1.DataMember = "Cards"
        Me.DataGridView1.DataSource = BindingSource1

        Dim bs As New BindingSource
        bs.DataMember = "CardWords"
        bs.DataSource = Me.BindingSource1
        Me.ListBox1.DataSource = bs
        Me.ListBox1.DisplayMember = "Word"

    End Sub

    Private Sub SetUpDataSet()
        Dim CardsDataTable As New DataTable
        Dim WordsDataTable As New DataTable
        CardsDataTable.TableName = "Cards"
        CardsDataTable.Columns.Add("ID", GetType(Integer))
        CardsDataTable.Columns.Add("Name", GetType(String))

        WordsDataTable.TableName = "Words"
        WordsDataTable.Columns.Add("ID", GetType(Integer))
        WordsDataTable.Columns.Add("CardID", GetType(Integer))
        WordsDataTable.Columns.Add("Word", GetType(String))

        data.Tables.Add(CardsDataTable)
        data.Tables.Add(WordsDataTable)

        data.Relations.Add("CardWords", data.Tables("Cards").Columns("ID"), data.Tables("Words").Columns("CardID"), False)

    End Sub

    Private Sub LoadTables()
        data.Tables("Cards").Rows.Add(1, "Card1")
        data.Tables("Cards").Rows.Add(2, "Card2")
        data.Tables("Cards").Rows.Add(3, "Card3")

        data.Tables("Words").Rows.Add(1, 1, "Word1of1")
        data.Tables("Words").Rows.Add(2, 2, "Word1of2")
        data.Tables("Words").Rows.Add(3, 1, "Word2of1")
        data.Tables("Words").Rows.Add(4, 2, "Word2of2")


    End Sub
End Class

Of course you would get your data from your database.

I just dropped a datagridview, listbox and bindingsource on the form from the toolbox.

I'm going to dive into this after work tonight. Thank you! I will report back with my results and/or questions. As always, I appreciate the help. Thank you.

[wes4dbt]

I'm going to dive into this after work tonight. Thank you! I will report back with my results and/or questions. As always, I appreciate the help. Thank you.

If you are retrieving the photo and/or KeyWords as needed then use the Bindingsource PositionChanged event not the dgv CellClick event. A dgv cell can be clicked for various reasons, it doesn't mean that the dgv has moved to a different row. No need to be retrieving data from the database unless the dgv position has changed.

    Private Sub BindingSource1_PositionChanged(sender As Object, e As EventArgs) Handles BindingSource1.PositionChanged
        If Me.DataGridView1.CurrentRow IsNot Nothing Then
            Dim row As DataRow = DirectCast(Me.BindingSource1.Current, DataRowView).Row
            ' Retrieve the photo/keywords here, using row("ID").ToString to locate the correct record
            ' 
            'you will also need to load the photo in the FormLoad event
        End If
    End Sub