Recursive query to Access database / OleDB / VB2005

[soldierB]

Hi Guys,

I find myself needing a little advice again,

Im trying to pull records from my database, I want to pass a string value in my query that will return a record for me (This I can manage)

But the problem I have is,

Each record in the database has a Primary Key (MainId) and also has another 2 fields that get pulled they are (MFatherID) & (MMotherID), these 2 fields match the values held in the (MainId (Primary Key)) colum.

So when I pull my first record I want to look at the values in the other 2 colums (MFatherID) & (MMotherID) look at their value and go back and do another query to pull the records they match from searching the (MainId (Primary Key)) colum.

Now I want this to be done a few times up to 70 records or more would be wanted, the actual amount of records pulled would hopefully be up to the user and I had hoped I could put in a listBox or something to allow the user the choice of how many records to return.

The records I would like to display in a couple of ways, initially if I can just return the records of the recursive query to a datagrid that would be fine, but I would also like to put the record into a report of somekind that will allow me to lay the data out in a way I want to show it.

Initially I started trying to build the querys in the database itsef but what I want seems to complex for access?? I did post on the Access boards first to get some support there but the lads on there thought I needed some recursive code to do this for me.

To see what my database is like the link to the access threads are here

http://www.tek-tips.com/viewthread.cfm?qid=1163782&page=2

Sorry to be posting a great long post I hope I dont put anyone off from helping me out here.

I have a TestProject at the moment with a form and some code that is doing a basic query and returning a single record or just displaying all the records in a grid, I will post this below in the hope im heading in the right direction and perhaps someone can assist me in the adaptation I need.

Thanks again
Sb

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    '---Form level variables
    Private strConnectionString As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source =C:\Documents and Settings\webstaff\My Documents\Visual Studio 2005\Projects\Recursive Test1\main.mdb;"


    Private objConnection As OleDbConnection
    Private objCommand As OleDbCommand
    Private objDataAdaptor As OleDbDataAdapter
    Private objDataSet As DataSet

    Private Sub PopulateGrid()
        '--- Initialise a new instance of the OleDBDataAdaptor class
        objDataAdaptor = New OleDbDataAdapter
        '---Initialise a new instance of the DataSet class
        objDataSet = New DataSet
        '--- Set the SelectCommand for the OleDbDataAdaptor
        objDataAdaptor.SelectCommand = objCommand

        Try
            '---Populate the DataSet
            objDataAdaptor.Fill(objDataSet, "Generations")

            '---Bind the dataset to the datagrid
            grdResults.DataSource = objDataSet
            grdResults.DataMember = "Generations"

            '---Set the AlternatingRowsDefaultCellStyle.BackColor property
            grdResults.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke

            '---Set the CellBorderStyle property
            grdResults.CellBorderStyle = DataGridViewCellBorderStyle.None

            '---Set the SelctionMode property
            grdResults.SelectionMode = DataGridViewSelectionMode.FullRowSelect

            '--- Set the AutoSizeColumnsMode property
            grdResults.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells


        Catch OleDbException As OleDbException
            MessageBox.Show(OleDbException.Message, "Find Generations")
        End Try

        '---CleanUp
        objCommand.Dispose()
        objCommand = Nothing
        objDataAdaptor.Dispose()
        objDataAdaptor = Nothing
        objDataSet.Dispose()
        objDataSet = Nothing
        objConnection.Dispose()
        objConnection = Nothing
    End Sub

    Private Sub btnParimeterQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParimeterQuery.Click
        '---Initialise a new instance of the OleDbConnection class
        objConnection = New OleDbConnection(strConnectionString)
        '---Initialise a new instance of the OleDbCommand class
        objCommand = New OleDbCommand
        '---Set the objCommand object properties
        objCommand.CommandText = "usp_SelectUser"
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.Connection = objConnection

        '--- Add the required parimeter for the query
        objCommand.Parameters.Add("@MName", OleDbType.VarChar, 50).Value = Replace(txtProjectID.Text, "'", "")

        '---Populate the DataGridView
        Call PopulateGrid()

    End Sub

   
    Private Sub btnSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
        '---Initialise a new instance of the OleDbConnection class
        objConnection = New OleDbConnection(strConnectionString)
        '---Initialise a new instance of the OleDbCommand class
        objCommand = New OleDbCommand
        '---Set the objCommand object properties
        objCommand.CommandText = "Select * From Main"
        objCommand.CommandType = CommandType.Text
        objCommand.Connection = objConnection

        '---Populate the DataGridView
        Call PopulateGrid()
    End Sub
End Class

 

[jebenson]

Okay, after reading your other post, I really don't think this should be all that difficult. Here goes...


Private Sub GetParents(ByVal KidsName As String, ByVal MomsID As Integer, ByVal DadsID As Integer, ByRef ResultsTable as DataTable)

Dim dr As DataRow
Dim ds As DataSet
Dim daMom As OleDbDataAdapter
Dim daDad As OleDbDataAdapter

daMom = New OleDbDataAdapter("Select * from Main where MainID=" & MomsID, objConnection)
ds = New DataSet
daMom.Fill(ds)

daDad = New OleDbDataAdapter("Select * from Main where MainID=" & DadsID, objConnection)
daDad.Fill(ds)

'Should only be one Mom row and one Dad row

dr = ResultsTable.NewRow()

With dr
.Item("PersonName") = KidsName
.Item("FatherName") = ds.Tables(1).Rows(0).Item("FirstName").ToString.Trim & " " ds.Tables(1).Rows(0).Item("LastName").ToString.Trim
.Item("MotherName") = ds.Tables(1).Rows(0).Item("FirstName").ToString.Trim & " " ds.Tables(1).Rows(0).Item("LastName").ToString.Trim
End With

ResultsTable.Rows.Add(dr)

daDad.Dispose()
daDad = Nothing

daMom.Dispose()
daMom = Nothing

ds.Dispose()
ds = Nothing

dr = Nothing

End Sub


Here's how you would call this:

'Set up table to hold results
Dim dt as DataTable

dt = New DataTable

Dim dcPersonName As DataColumn
Dim dcFatherName As DataColumn
Dim dcMotherName As DataColumn

dcPersonName = New DataColumn
dcPersonName.DataType = System.Type.GetType("System.String")
dcPersonName.ColumnName = "PersonName"

dcFatherName = New DataColumn
dcFatherName.DataType = System.Type.GetType("System.String")
dcFatherName.ColumnName = "FatherName"

dcMotherName = New DataColumn
dcMotherName.DataType = System.Type.GetType("System.String")
dcMotherName.ColumnName = "MotherName"

dt.Columns.Add(dcPersonName)
dt.Columns.Add(dcFatherName)
dt.Columns.Add(dcMotherName)

'Get kids info
Dim daKids As OleDbDataAdapter
Dim dsKids As DataSet

'Note: add any other criteria you want in the where clause.
'The IsNull(MFatherID,0)<>0 indicates that there is a value in the MFatherID field.
'The same holds true for MMotherID
daKids = New OleDbDataAdapter("Select * from Main where IsNull(MFatherID,0)<>0 and IsNull(MMotherID,0)<>0", objConnection)
dsKids = New DataSet

daKids.Fill(dsKids)

Dim r As Integer
Dim CurrentKidName As String
Dim DadId as Integer
Dim MomId as Integer

For r = 0 to dsKids.Tables(0).Rows.Count - 1
CurrentKidName = dsKids.Tables(0).Rows(r).Item("FirstName") & " " & dsKids.Tables(0).Rows(r).Item("LastName")

DadId = dsKids.Tables(0).Rows(r).Item("MFatherID")
MomId = dsKids.Tables(0).Rows(r).Item("MMotherID")

GetParents(CurrentKidName, MomId, DadId, dt)
Next

You might think about putting a "placeholder" value in the MMotherID and MFatherID fields (e.g., -1, -9, etc.). That way you don't have to use the IsNull() function in the SQL - you just test for the placeholder value.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[soldierB]

Hi Jebenson,

That looks awsome, Thanks for taking the time to reply to me,

I have been sitting reading through your code for a while now, and I have a rought idea of whats happening, Im just getting to grips with ADO.Net and datasets, datatables, etc,

I hope you can bear with me while I get my head round this,

Firstly you have a SUB 'GetParents' and its called like so : GetParents(CurrentKidName, MomId, DadId, dt),

Im following that.......phew!!

To Call it you set up a table (dt) along with the column names and value types to be held in there.

Dim dt as DataTable

dt = New DataTable

So I now have a table that holds the Persons Name ,Father Name,& Mother Name....

From the 'Get kids info' section onward im a little puzzled, although I can read the code Im not following whats happening...

Below, this pulls all the records from the database as the MFatherID & MMotherID are never empty. and then fills the dataset(dsKids) with all the records

daKids = New OleDbDataAdapter("Select * from Main where IsNull(MFatherID,0)<>0 and IsNull(MMotherID,0)<>0", objConnection)
dsKids = New DataSet

daKids.Fill(dsKids)

Now the last bit is whats realy got me just now, although as I say I can read it im not following 100%,

This seems to be the loop that will pull all the records for me ?? is that right?

Dim r As Integer
Dim CurrentKidName As String
Dim DadId as Integer
Dim MomId as Integer

For r = 0 to dsKids.Tables(0).Rows.Count - 1
    CurrentKidName = dsKids.Tables(0).Rows(r).Item("FirstName") & " " & dsKids.Tables(0).Rows(r).Item("LastName")

    DadId = dsKids.Tables(0).Rows(r).Item("MFatherID")
    MomId = dsKids.Tables(0).Rows(r).Item("MMotherID")

    GetParents(CurrentKidName, MomId, DadId, dt)
Next

What im not seeing is where to get the initial values'(CurrentKidName, MomId, DadId, dt)' to pass to this constructor?

GetParents(CurrentKidName, MomId, DadId, dt)

Thanks again for your time, I fully appreciate it.
Regards & Seasons greetings
Sb

 

[jebenson]

I was afraid that the IsNull(MFatherID,0)<>0 would be confusing. What I am trying to get at is some way to identify which record are children, and get only those records. From you post in the Access forum I got the impression that if a record is for a parent, then MFatherID and MMotherID would be empty. Since they are not empty, you need to change that part of the SQL query so that it excludes Parents (i.e., only get Children's records).

As I said in the last line of my post, if you put a placeholder value in those fields (say, -9), you can use that to exclude parents. This SQL:

"Select * from Main where MFatherID<>-9 and MMotherID<>-9"

would get only child records, if -9 indicated a parent.

Since I don't know what data are in those fields, I can't give you a more specific "here's what you do" answer.

However, once you get only child records the loop should iterate through the records, build the appropriate parameters and pass them to the GetParents sub.

A question: is this table intended to contain multiple generations? That is, children, their parents, their parents' parents, and so forth? Because if that is the case, I suggest you look into normalizing the table - basically, separating Parents and Children into 2 separate tables.

I'll keep following this thread, so if you have any more questions/problems just let me know.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[soldierB]

Hi Jebenson,

Thanks for replying again,
Initially I thought I could do all this in the database as a set of querys that pulled from each other but......

Mt database table is very simple, 1 Table (main)

The fields inside are as follows:

Field Name                Data Type

MainId                     AutoNumber
MTitle                     Text  <<  EG: Mr,Miss,Dr,Ms, ETC
MName                      Text  << EG:John Smith(Full name)
MSex                       Text  << EG: M or F
MDOB                       Text  << EG:10/04/2001
MFatherID                  Text  <<EG: 123, 12(From MainId)
MMotherID                  Text  <<EG: 123, 12(From MainId)

so my table may look like this in access if you looked, minus some fields of course:

MainId      MTitle        MName      MFatherID    MMotherID
1             Mrs       Jane Doe          9        9
2             Miss      John Doe          9        9
3             Miss      Baby1Doe          2        1
4             Mr        Baby2Doe          2        1
5             Mrs      Jill Smith        15       23
6             Mr      David Smith        55       77
7             Ms     Daughter Smith       6        5
8             Mr         Son Smith        6        5
9             Null       Unknown          9        9

I do want other fields in the datbase table main, like eye colour, hair colour, date of death etc, but this section I just want to be able to pull a record and build a family tree showing the person and all his generations.

like so:
It will be laid out in a family tree style as below on a report of some kind so it can be printed with the proper names and titles.

                       Title.Gfather

             Title.Father
                       Title.Gmother

Title.Focus Person
                       Title.Gfather2
            
             Title.Mother

                       Title.Gmother2

Im realy up for any suggestions, I will have to look into normalisation of the database??

I cannot change the table structure of main much, it realy has to say the way it is, as each record is added via another form the user adds the person and can pick the parents or add new parents on the fly if they are not in the database or if the parents are 'unknown' they can choose an 'unknown'(Record 9 in the example) the record is then added and the Parent Id's added too.

I have been working on something similar to your suggestion.

What I have done is I have a textbox,Button & datagrid controls on a form,
The user can type a name or part name into the textbox hit the button and get the results of the search displayed in the datagrid.

I have this working!! So now i have records in the datagrid I can select the record from the datagrid i want to view. (I can do this too.)

Now the tricky bit.....

When I select the record in the datagrid i want to get the values needed to get the parents, so I have done this:

Private Sub grdResults_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdResults.CellClick
        Try
            txtResultName.Text = grdResults.Item(2, grdResults.CurrentRow.Index).Value
            lblTitle.Text = grdResults.Item(1, grdResults.CurrentRow.Index).Value
            lblSex.Text = grdResults.Item(3, grdResults.CurrentRow.Index).Value
            lblDOB.Text = grdResults.Item(4, grdResults.CurrentRow.Index).Value
            lblMainID.Text = grdResults.Item(0, grdResults.CurrentRow.Index).Value
            lblMID.Text = grdResults.Item(5, grdResults.CurrentRow.Index).Value
            lblFID.Text = grdResults.Item(6, grdResults.CurrentRow.Index).Value

        Catch ex As Exception
            MessageBox.Show("Nothing To Select here....!! " & ex.Message, "Find Generations")
        End Try
    End Sub

So I now have the focus persons main details, title and Id's for their parents.

So to test with as I have no report set up at present and to see if I can get all the data properly I have set out a set of label & textbox controls below my datagrid on the same form.

like so: lbl=labelcontrol txt= textboxcontrol

                           lbl_GfatherID & txtGfather

             lbl_FatherID & txtFather

                           lbl_GmotherID & txtGmother

lblTitle.txtFocus Person

                       lbl_Gfather2ID & txtGfather2
            
             lbl_MotherID & txtMother

                       lbl_Gmother2ID & txtGmother2

So the datagrid is clicked and the values from the row are sent to my labels.

What im trying and it seems to be working!! Im not sure how efficient it is mind.

When the first labels get the values I went into the TexChanged event of the label controls and did this

I made a function 'GetParents(parentID as intiger)'

Private Sub lblFID_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lblFID.TextChanged
        GetParents(lbl_FatherID.Text)
        txtGfather.Text = ParentName
        lbl_GfatherID .Text = ParentFatherID
        lbl_GmotherID.Text = ParentMotherID

    End Sub

Ive done this on the first few label & textbox controls, and as the data goes into the labels the TextChanged event fires and the value gets passed to the function and the Variables in the function are set and sent back to the labels & text boxes.

As I say this seems to be working but will be quite a job to sit and code it all out.

I hope I havent totaly confused you, as I mentioned Im new to ADO.Net and VS so im up for any and all suggestions.

Thanks again

PS, sorry my posts seem to be getting longer....
Sb

 

[jebenson]

Well, given you most recent post you should disregard almost everything I posted earlier.

It looks to me as if you almost have it - I think you are on the right track with having each label be responsible for its own parents. Here's a tip that I think may help you out:

Creating controls in code, and assigning handlers to their events

Dim WithEvents lbl As Label 'in global declaration section


'In a sub or function
lbl = New Label
lbl.Top = 100 'calculate some value
lbl.Left = 100 'calculate some value

AddHandler lbl.TextChanged, AddressOf lbl_TextChanged
Me.Controls.Add(lbl)

lbl.Text = ParentID 'this will fire the TextChanged event

'end sub or function


Private Sub lbl_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)
GetParents(lbl_FatherID.Text)
txtGfather.Text = ParentName
lbl_GfatherID .Text = ParentFatherID
lbl_GmotherID.Text = ParentMotherID

End Sub

With this you can do an arbitrary number of generations. I leave it up to you to dtermine the placement of the labels, etc.

I hope I've helped at least a little bit. Good luck

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[soldierB]

Thanks Jebson,

I appreciated your thoughts.
That last little tip you have given me has just went way over my head........lol Whooooosh!!

Sorry Ive read through it a few times and just dont realy follow.(Newbies eh!!)

Thanks again
Merry Christmas....