Query funtion for two MS Access tables using ASP.NET(ADO.NET), one table has recursive nature
Posted on 2004-09-19
I have two tables: G_Category and G_Term.
GC_ID GC_Parent GC_Category
1 0 CatA
2 0 CatB
3 0 CatC
4 1 SubCatA1
5 1 SubCatA2
6 1 SubCatA3
7 4 SubSubCatA1
8 7 SubSubSubCateA1
G_Category in itself has some main Categories and SubCategories (thus recursive in nature). Main Categories have their GC_Parent set to 0 (zero).
SubCategories are defined in terms of the GC_Parent ids (they are the GC_ID for the Categories/SubCategories.
For a given GT_TD (from G_Term), I need to find the top level GC_ID (where GC_Parent = 0) considering that each major Category has some different levels of subcategories (there are maximum of 7 levels of subcategories) and the GT_ID can be at any arbitrary subcategory level.
You can see that the G_Category has recursive nature.
There are subcategories in the G_Category table.
Subcategories are defined in terms of the parant ids (GC_Parent)
Top level categories (wherer GC_Parent = 0) are the the main categories.
And there are subcategories for this main categories as defined in terms of GC_Parent.
Thus #4 is a subcategory of #1
#5 is a subcategory of #1
#7 is a subcategory of #4
For each main category (GC_parent =0) , there are arbitrary levels of subcategories.
I needed to know if GT_ID is related at any sublevel (say #7 here), how do I figure out what the main category is (where parent id (GC_Parent) is 0)?
I was provided with an ADO VB version of the problem with a function.
I need help converting this to an ASP.NET ADO.NET version.
Function GetTopLevelID(FindID As Long) As Long
Dim rst As ADODB.Recordset
rst.Open "G_Category", CurrentProject.Connection, adOpenStatic
rst.Find "[GC_ID]=" & FindID
rst.Find "[GC_ID]=" & rst!GC_Parent
Please let me know how I do that.
I started new with ASP.NET VB.NET…