[RESOLVED] DataGridView Parent / Child

[Fxguy1]

I'm learning how to use databases in VB and I was able to create a data set with two tables and a relationship between the two tables manually. In the Console I can display the parent / child data fine:




Now I'd like to display this same data in a DataGridView, but I cannot for the life of me figure out how to do it. I can get the column names and the data from the parent table, but not the child table:




Here is my code for the Forms App:

  Private Sub btnTable_Click(sender As Object, e As EventArgs) Handles btnTable.Click
        'Declare DataColumn Variables 
        Dim MedID As DataColumn
        Dim Generic As DataColumn
        Dim ClassID As DataColumn
        Dim AFHS_Class As DataColumn
        Dim AFHS_Class_Description As DataColumn

        'Declare other Variables
        Dim column As DataColumn
        Dim row As DataRow
        Dim Classcolumn As DataColumn
        Dim Classrow As DataRow
        Dim line As String
        Const vbTab As String = "     "

        'Create New Data Set
        Dim ChartData As DataSet = New DataSet("ChartData")

        'Add Tables to Data Set
        Dim MedsTable As DataTable = ChartData.Tables.Add("MedsTable")
        Dim ClassTable As DataTable = ChartData.Tables.Add("ClassTable")


        'Add Columns to Data Tables Method 2
        'Add to MedsTable
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "MedID"
        column.Unique = True
        MedsTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "Generic"
        MedsTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "ClassID"
        MedsTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "Class"
        MedsTable.Columns.Add(column)

        'Add to Class Table
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "ClassID"
        column.Unique = True
        ClassTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "AFHS_Class"
        ClassTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "AFHS_Class_Description"
        ClassTable.Columns.Add(column)


        'Set Data Table Primary Keys
        MedsTable.PrimaryKey = New DataColumn() {MedID}
        ClassTable.PrimaryKey = New DataColumn() {ClassID}

        'Add data to rows in Data Table Method 1
        MedsTable.Rows.Add(New Object() {0, "Atenolol", 10})
        MedsTable.Rows.Add(New Object() {1, "Lisinopril", 11})
        ClassTable.Rows.Add(New Object() {10, "04:00:08", "Beta-Blockers"})
        ClassTable.Rows.Add(New Object() {11, "08:23:24", "ACE-Inhibitors"})
        ClassTable.Rows.Add(New Object() {12, "12:00:08", "Calcium Channel Blocker"})
        ClassTable.Rows.Add(New Object() {13, "24:00:04", "Antitussives"})


        'Add data to rows in Data Table Method 2
        Dim newMedsRow As DataRow = MedsTable.NewRow()
        newMedsRow("MedID") = 3
        newMedsRow("Generic") = "Metoprolol"
        newMedsRow("ClassID") = 12
        MedsTable.Rows.Add(newMedsRow)

        newMedsRow = MedsTable.NewRow()
        newMedsRow("MedID") = 4
        newMedsRow("Generic") = "Enalapril"
        newMedsRow("ClassID") = 13
        MedsTable.Rows.Add(newMedsRow)


        'Add Data Relations
        Dim ClassLinksRelation As DataRelation = ChartData.Relations.Add("ClassRel", MedsTable.Columns("ClassID"), ClassTable.Columns("ClassID"))


        grdResults.DataSource = ChartData.Tables("MedsTable")


    End Sub

I've searched and read and searched and read and I just cannot seem to figure out how to do this.

Do I need to create a third "Results" table and fill it using a select from the other two tables?

[wes4dbt]

I don't see where your retrieving data from database tables, your just manually entering data, so there's no need for a Relation. Do you want to display the Parent and Child data in the same Datagridview? Why not the in separate grids? If you want to use just one grid then you would retrieve the data from the database using a "Join" statement. "Select tbl1.someField, tbl2.somefield From tbl1 Inner Join tbl2 On tbl1.someField = tbl2.someField"

[Fxguy1]

I don't see where your retrieving data from database tables, your just manually entering data, so there's no need for a Relation. Do you want to display the Parent and Child data in the same Datagridview? Why not the in separate grids? If you want to use just one grid then you would retrieve the data from the database using a "Join" statement. "Select tbl1.someField, tbl2.somefield From tbl1 Inner Join tbl2 On tbl1.someField = tbl2.someField"

Ok, I'm still trying to wrap my head around it all. I understand VB and I understand Access, but putting the two together has been a bit tricky for me.

What I'm trying to do is basically learn how to import a multi table database into a VB DataSet (Which is basically an in memory version of the DB) and then work with the DataSet to do my Selects and Joins and displaying of results.

Is that possible or am I going about it the wrong way?

[wes4dbt]

Really, you should be doing your Selects and Joins when you retrieve the data from the database but you can definitely do filtering and setup relationship within your dataset. Here is an excellent Parent/Child example without a database, it's by jmc. There is ton of information available on how to connect to an Access database and how to retrieve data. Give it a try and then post questions if you have problems. Always post the code that your having problems with along with a full explanation.

[wes4dbt]

Here is a very basic example of displaying data from an Access database,

Imports System.Data.OleDb
Public Class Form1
    Private con As New OleDbConnection(My.Settings.waterConnectionString)
    Private da As New OleDbDataAdapter("Select ID, Field1, chkBox From AutoNum1", con)
    Private dt As New DataTable
    Private cmdBldr As New OleDbCommandBuilder(da)



    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        da.Update(dt)
    End Sub
End Class

[Fxguy1]

Give it a try and then post questions if you have problems. Always post the code that your having problems with along with a full explanation.

Ok. So I have this application that I am working on that I am having trouble with. It is based on this database:



The application is supposed to read in a text file of medications along with a text file of indications. Then it is supposed to show:
1) the list of matched medications with indictations
2) a list of any Medications that dont have a matching indication from the text file
3) a list of any Indications that dont have a matching indication from the text file

I'm doing that via a paramaterized select query with this code here:

 'Build SQL statement to Select Generic, Class, and Indication from Generics, AHFSClass, Indication tables with Selected Criteria from form
        Dim strSQLSelect As String = "SELECT Generics.Generic,AHFSClass.Class,Indication.IndicationShort 
                                FROM (((Generics INNER JOIN ClassLink ON Generics.GenericID = ClassLink.GenericID) 
                                INNER JOIN AHFSClass
                                ON AHFSCLass.ClassID=ClassLink.ClassID)
                                INNER JOIN Treatments
                                ON Treatments.ClassID = AHFSClass.ClassID)
                                INNER JOIN Indication
                                ON Treatments.IndicationID = Indication.IndicationID"


        Dim strSQLWhere As String = "WHERE Generics.Generic IN ("
        Dim strSQLSelectProblems As String = "And Indication.IndicationShort NOT IN ('" & cmbIndication1.Text & "' , '" & cmbIndication2.Text & "')"


        'Create IO Reader to Readin File and Set Delimiter to ";"
        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Users\Fxguy1\Desktop\Meds.txt")

            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.SetDelimiters(";")
        End Using

        'Read in All Lines of File
        Dim lines() As String = IO.File.ReadAllLines("C:\Users\Fxguy1\Desktop\Meds.txt")

        'Generate Where Statement with required number of parameters
        For x = 0 To lines.GetUpperBound(0)
            Dim csv() As String = lines(x).Split(";"c)
            For i As Integer = 0 To csv.GetUpperBound(0)
                strSQLWhere &= " ?,"
                txtDisplay.Text = txtDisplay.Text & csv(i) & Environment.NewLine
            Next
            strSQLWhere = strSQLWhere.Substring(0, strSQLWhere.Length - 2) 'Removes the comma and space on the end
            strSQLWhere = strSQLWhere & ")"
        Next


        'Set Final SQL String for Query
        Dim strSQLFinal As String = strSQLSelect & " " & strSQLWhere


        'Attempt to Run Query
        Try
            'establish command object and data adapter
            ResultsCommand = New OleDbCommand(strSQLFinal, ChartCheckConnection)
            With ResultsCommand.Parameters
                'Fill Parameters 
                For x = 0 To lines.GetUpperBound(0)
                    Dim csv() As String = lines(x).Split(";"c)
                    For y As Integer = 0 To csv.GetUpperBound(0)
                        .AddWithValue("@Parameter'" & (y), csv(y))
                    Next
                Next

            End With

            ResultsAdapter.SelectCommand = ResultsCommand
            ResultsAdapter.Fill(ResultsTable)

I've run into a problem --

When I had 3 indications and 3 medications in the text file, the matching results returned fine, however when i added a 4th indication and medication, those are not showing up (even though I've checked in Access and they are found in the database with all the proper links.)

Its gotten complicated and complex enough that I've exceeded my limited knowledge of VB and Databases. I cant figure out why it works for 3 but when i added a fourth it doesnt list the 4th item in the text file.

I've created a text box to display the list of meds imported from the IO Reader and it read in correctly. I checked the Select String and it's there too, so I cant for the life of me figure out what is going wrong. I feel like it's something to do with the Access DB but that looks fine as well.

Stumped and frustrated.....

[wes4dbt]

Sorry, looks like my link to the Parent/Child example is missing from my previous post, here http://www.vbforums.com/showthread.p...ms)&highlight=

Don't have time to check out your code now but a text file is not a database, so what does this have to do with MS Access.

[Fxguy1]

Sorry, looks like my link to the Parent/Child example is missing from my previous post, here http://www.vbforums.com/showthread.p...ms)&highlight=

Don't have time to check out your code now but a text file is not a database, so what does this have to do with MS Access.

The text file is simply inputting the variables for the SELECT Query. The application is meant to be able to take any number of medications and any number of indications and return the matched and not matched from the database. The database is a complete list of all existing medications and indications.

Does that make sense?

I'll check out the link later today when I have some time.

Thanks!

[wes4dbt]

I've run into a problem --

When I had 3 indications and 3 medications in the text file, the matching results returned fine, however when i added a 4th indication and medication, those are not showing up (even though I've checked in Access and they are found in the database with all the proper links.)

Its gotten complicated and complex enough that I've exceeded my limited knowledge of VB and Databases. I cant figure out why it works for 3 but when i added a fourth it doesnt list the 4th item in the text file.

I've created a text box to display the list of meds imported from the IO Reader and it read in correctly. I checked the Select String and it's there too, so I cant for the life of me figure out what is going wrong. I feel like it's something to do with the Access DB but that looks fine as well.

Yeah this isn't what you originally asked for and I see you've posted this question before. There is some good information in the answers, you might want to study them again. Especially the information on Inner Joins vs Left Joins or Outer joins.

Remember, with an Inner join you will need the necessary data in ALL 5 tables, not just the three your interested in.

For testing purposes I would

            Debug.Print( ResultsCommand )
            ResultsAdapter.SelectCommand = ResultsCommand
            Dim int as Integer
            int = ResultsAdapter.Fill(ResultsTable)
            Debug.Print( int.ToString

The Debug.Print result will be in the Output Window or maybe the Immediate Window depending how you have your VS setup. "int" will tell you how many records the Fill method returned. You could then copy and paste the ResultsCommand string into the SQL Query window in Access. Then run the query to see the result. If you still can't find the problem, post the Debug.Print results back here so we can see.

[Fxguy1]

I think I may have figured out the issue / problem. I think it may have something to do with dealing with a local file for the database. I designed forms for each of the tables to allow me to update the data, which seems to work fine until I try to add something and it runs like I didnt update anything.

I try to update the connection string to a different copy of the DB and then I get a ConnectionString not initialized error.

[wes4dbt]

You can lose your updates, it all depends on how you have setup the projects properties. Here's some things to check. Did you add the Database to the project? If so, check the "copy to output directory" property. It looks like you've created a Typed dataset, so the Connection String is probably in Project->Setting. Go there and post back the connection string. Can't help without seeing it. If your still getting an error, post the line that's causing the error and what the error says.

EDIT - This should help explain what's going on with your database https://msdn.microsoft.com/en-us/lib...89(VS.80).aspx

[Fxguy1]

The Debug.Print result will be in the Output Window or maybe the Immediate Window depending how you have your VS setup. "int" will tell you how many records the Fill method returned. You could then copy and paste the ResultsCommand string into the SQL Query window in Access. Then run the query to see the result. If you still can't find the problem, post the Debug.Print results back here so we can see.

I have posted this before, the reason I posted this thread was because I was attempting to debug on my own the only way I could figure how.

So thank you for the debug code, it helped a little bit. So the Fill method returned 6 records (but in reality it was only 3 because there were 2 copies of each returned). As for the ResultsCommand string it was - System.Data.OleDb.OleDbCommand.

So here I'm passing the Select command to the OldDbCommand to select the data I'm looking for from the database:

ResultsCommand = New OleDbCommand(strSQLFinal, ChartCheckConnection)

I added a textbox control and outputted my strSQLFinal variable and this is the result:

Select Generics.Generic, AHFSClass.Class, Indication.IndicationShort 
                                FROM (((Generics INNER JOIN ClassLink ON Generics.GenericID = ClassLink.GenericID) 
                                INNER JOIN AHFSClass
                                ON AHFSCLass.ClassID=ClassLink.ClassID)
                                INNER JOIN Treatments
                                ON Treatments.ClassID = AHFSClass.ClassID)
                                INNER JOIN Indication
                                ON Treatments.IndicationID = Indication.IndicationID WHERE Indication.IndicationShort In ( ?, ?, ?, ?, ?, ?)

the '?'s are the parameters (which I understand to be a safer way of passing variables to the Select Command). I also output the result of the text file read in to make sure it was reading in all meds from the file and it is:

atenololLoratadineOndansetronCortisoneLisinopril

Next I used the Debug.Print to print out the parameters to the debug window to see exactly which parameters where being added /passed to the select.

This is the result :

Heart failure
Angina pectoris
Itching
Nausea
Cough

Which is exactly what is in the indications text file.


So the INNER JOIN is supposed to return the results from both tables where the value is exactly the same. Based on my relations:

So an INNER JOIN on the Treatments, AFHS Class, and ClassLink tables should all return fine since the classID is found across all three of those.


The connection string is : ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Fxguy1\Desktop\VBDB\ChartReview.accdb")

BTW - This is all the result of I was able to get this to work fine in Access itself, but I wanted to create a standalone application. I then started working through this book:
VB and Databases

If there is a better text / resource out there please let me know. I'm really not trying to be a pain or ask anyone to do it for me, I'm just struggling as I am self taught and have been learning by examples. This is the most complicated / complex thing I've ever done.

[Fxguy1]

OK. I know exactly what the problem is... To many instances of the database I'm trying to work with. Is there a way to search ALL the code across my project for the data source? I think I have one copy specified in some places and another in other places.

Now might be a good time for a lesson on using local files in a project.......


EDIT --- This was EXACTLY the problem. My Select and JOINS were fine, it was simply pulling in an old copy of the database. Everything works fine now.

I need to create a CLEAN copy of my project and all files and delete all the old copies......

[wes4dbt]

As for the ResultsCommand string it was - System.Data.OleDb.OleDbCommand.

Yeah, that my fault, I meant ResultCommand.CommandText. That would have shown the completed Select command.
Also you could have put a Break Point at this line,

ResultsCommand = New OleDbCommand(strSQLFinal, ChartCheckConnection)

Then you could hover the cursor over strSQLFinal and it's value would have been displayed. I don't know if have learned how to use the Debugger but it's a valuable tool. To set a break point all you have to do is Click at the far left side of the line of code you want to break at.

I usually only use one connection string for a database. I create it in Project->Settings. Then anytime I need it,

Dim con As New OleDbConnection(My.Settings.someConnectionString)

You could also use this method

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ChartReview.accdb"

The program will always look for the database in the same folder as the "exe". So when your working on the program it will be either in the "Bin\Debug" or "Bin\Release" folder, depending if your running VB in Debug or Release mode.

[Fxguy1]

Thanks for the help, especially with the Debug. I'm not all that familiar with the Debug tools which is why I was trying to debug in a very roundabout way (trying to display the results of each step instead of looking at the debug text.

I have 1 out of 3 working correctly (and the most difficult one at that). Now I need to figure out on the other two why it's returning duplicates and how to filter it. I'll wait until I have played with it a bit with the Debug and see what I can do and I'll be back if I cant figure it out.

Thanks again!