• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Query funtion for two MS Access tables using ASP.NET(ADO.NET), one table has recursive nature

Hi,

I have two tables: G_Category and G_Term.

G_Category table:
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_Term table:
GT_ID GC_ID

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
While rst!GC_Parent<>0
   rst.Find "[GC_ID]=" & rst!GC_Parent
Wend
GetTopLevelID=rst!GC_ID
rst.Close
Set rst=Nothing
End Function

Please let me know how I do that.
I started new with ASP.NET VB.NET…

Thanks.
_Esam

0
_Esam
Asked:
_Esam
  • 35
  • 22
6 Solutions
 
LindzKCommented:
In .net you can use a dataset to store data from your database, and a dataadapter, to retrieve the data

below are a couple of funcitons for you - one to make the initial connect to the database
and the second, which can be called multiple times to get the top level id of a row from the database

I have commented the code, to try to help you understand what is happening.  If there is anything, you don't understand, then let me know



'First of all at the top of the class where you do this work, import sql client
Imports System.Data.SqlClient   'class so that we can run sql commands


Private DS as new dataset      ' dataset to hold the database tables


' Here is a function to get the tables from the database
' it places the tables inside a dataset, which is declared globally
' so that you can use them in other functions such as gettoplevelid
Sub GetTablesFromDB()
 
  ' create a connection to the database
  Dim SqlConn As New SqlConnection("My Connection string goes here")

  ' place all the connecting / querying code inside a try/catch
  ' so that if something fails, you can easily note the error
  ' and also close the connection safely in the 'finally
  ' PLEase NOTE
  ' You can send multiple selects to different tables if you need in the same
  ' select string, they would be stored within separate tables in the dataset
  ' ie dataset.tables(0)   dataset.tables(1)  for each query
  Try
            'send the query to the database
            SqlConn.Open()
            Dim mySqlCommand As New SqlCommand("select * from G_Category", SqlConn)
            Dim MySqlAdapter As New SqlDataAdapter
            MySqlAdapter.SelectCommand = mySqlCommand
            Dim retval As Integer = MySqlAdapter.Fill(DS)
  Catch ex As Exception
            'put code here, to handle a fail, or at least a debug line
            'so that you can see what went wrong
            DS = Nothing
  Finally
         ' whether there was an error or not - close the database connection
            SqlConn.Close()
  End Try


End Sub


' Here is a basic function, to open your connection to the database
Private Function GetTopLevelID(ByVal FindID As Long) As Long
        Dim DS As New DataSet
        'validate that there is something in the dataset
        If Not DS Is Nothing And Not DS.Tables.Count < 1 Then
           
            Dim finished As Boolean

            'perform select on the dataset, to get the first row needed
            Dim rows() As DataRow
            rows = DS.Tables(0).Select("GC_ID = " & FindID)
            If rows.Length > 0 Then
                'loop through the dataset, until the parent id = 0
                While Not finished
                    'perform a select on the dataset, to obtain the top level
                    'id
                    rows = DS.Tables(0).Select("GC_ID = " & rows(0).Item("GC_Parent"))
                    If rows(0).Item("GC_Parent") = 0 Then
                        finished = True
                    End If
                End While
            End If

            Return rows(0).Item("GC_ID")
        End If
    End Function
0
 
LindzKCommented:
Oh   - one last thing - as I use a while to loop through until it gets to 0,

after each select that places an array of rows, into 'rows' you may want to test if rows.length > 0 and if it is - set finished to true, so that you don't get stuck in an infinite loop - sorry - seems, i rushed out the solution a little too fast
0
 
_EsamAuthor Commented:
I'm sorry.
I think both functions are missing something or I am not understanding right?
It is said that the GT_ID (id from G_Term table) is related to any sublevel of the G_Category But I need to find the Top level GC_ID for this relationship (this sublevel relationship).
I don't see any relationship with GT_ID in the function.
If I am not clear please let me know.
Or how GT_ID is related with TopLevelGC_ID in the function?

_Esam

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
_EsamAuthor Commented:
Hi,
I's sorry.
Please ignore the last post.
Of course I am getting the GC_ID for this GT_ID when I do the query on the G_Category and G_Term tables initially.
But please do provide the while loop sysnax.
_Esam
0
 
_EsamAuthor Commented:
Hi,
Could you please clarify what you meant by being stuck in infinity loop?
Could you please let me know how to fix it?

Thx.
_Esam
0
 
LindzKCommented:
Hi, the procedure GetTopLevelID I posted, mimics your original loop - the main differences in most of the code is the way you get the data from the database, and the fact that you use tables in a dataset, to access it, rather than a record set - here is the second function again, with the stuff added, to stop you going into an internal loop -  only reason you may have done with my first example, is because I do while not finished - but i only set finished if it found the gc_id - now, if there had been a problem iwth the data in the database, so therefore it didn't find the next row, in the hierachy - finished would never be true - so that's the bit I tweaked.

' Here is a basic function, to open your connection to the database
Private Function GetTopLevelID(ByVal FindID As Long) As Long
        Dim DS As New DataSet
        'validate that there is something in the dataset
        If Not DS Is Nothing And Not DS.Tables.Count < 1 Then
           
            Dim finished As Boolean
            dim notfound as boolean = false

            'perform select on the dataset, to get the first row needed
            Dim rows() As DataRow
            rows = DS.Tables(0).Select("GC_ID = " & FindID)
            If rows.Length > 0 Then
                'loop through the dataset, until the parent id = 0
                While Not finished
                    'perform a select on the dataset, to obtain the top level
                    'id
                    rows = DS.Tables(0).Select("GC_ID = " & rows(0).Item("GC_Parent"))
                    if not rows.length < 1 then
                      If rows(0).Item("GC_Parent") = 0 Then
                        finished = True
                      End If
                    else
                        finished = true
                         notfound = false
                    endif
                   
                End While
            End If
            if not notfound then
            Return rows(0).Item("GC_ID")
            else
             return nothing
            endif
        End If
    End Function
0
 
LindzKCommented:
Datasets, are easy to work with , as you can think of them just like a database -
your dataset, containis tables - relating to what ever you placed into your original database query

Each table, contains rows, identical to those in the database structure - but only the rows that you asked for in your original query

each row contains columns

you can run select statements on the dataset, just like you can in a database  -

You even get 'rollback' features, similar to that in transactional sql - where you can accept changes in the dataset, or reject them, before sendign anything back to the database itself.
0
 
_EsamAuthor Commented:
Hi,
Thank you so much for your excellent illustration.
I will accept this answer although I haven't tried it it...
Because I will have to ask you another question before I can try this solution..
Please look at the posting in a few minutes..

Thx.
_Esam
0
 
LindzKCommented:
Don't accept, until you can try it :)  if it doesn't work for you - it will be a waste of points!  I'd rather be happy that you went away with a working solution, and i'm happy to wait until we know it works.
0
 
_EsamAuthor Commented:
Ok then..:)
I need to know how can I get the GT_ID from this query and pass it to your function:

Dim da As New OleDbDataAdapter("SELECT GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance FROM G_Term " & _
                "WHERE GT_Term Like '%" & sv & "%'", cn)

Should I declare a variable like:

Dim TopLevelID As Long
now how do I assign the GT_ID to the TopLevelID so that I can perform the function?

Thx.
_Esam

0
 
LindzKCommented:
Using like - you could end up with more than one row back from your database  - to get the GT_ID from the results however - is soemthing like this...

 ' create a connection to the database
  Dim SqlConn As New SqlConnection("My Connection string goes here")
Dim DS as new Dataset
Try
            'send the query to the database
            SqlConn.Open()
            Dim mySqlCommand As New SqlCommand("SELECT GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance FROM G_Term " & _
                "WHERE GT_Term Like '%" & sv & "%'", cn, SqlConn)
            Dim MySqlAdapter As New SqlDataAdapter
            MySqlAdapter.SelectCommand = mySqlCommand
            Dim retval As Integer = MySqlAdapter.Fill(DS)
  Catch ex As Exception
            'put code here, to handle a fail, or at least a debug line
            'so that you can see what went wrong
            DS = Nothing
  Finally
        ' whether there was an error or not - close the database connection
            SqlConn.Close()
  End Try

if not DS is nothing and DS.tables.count > 0 then
  dim TopLevelID As Long
  ' lets assume that you want the TopLevelID from the first row returned
  TopLevelID = DS.Tables(0).Rows(0).Item("GT_ID")
end if


You reference any cell you need, using the field name

You could do something like

dim trow as datarow
trow = DS.Tables(0).Rows(0)
TopLevelID  = trow.Item("GT_ID")

If you are expecting more than one row returned from your query, you can loop through each row as follows :-

Dim trow as datarow
for each trow in DS.Tables(0).Rows
  'get each id out
  TopLevelID = trow.Item("GT_ID")
  ' do what you need to with the id, before moving on to the next one
next

If you need to refine your results, if more than one row you can do a select on the dataset table, just as you would a database, it returns an array of rows, even if the arrya only has one item -  
Dim rows() as datarow
rows = DS.Tables(0).select("new select statement here")


When you do your initial select into the database, you can also select the data from your second table at the same time, if you want - they both go in the dataset, as different tables

Dim mySqlCommand As New SqlCommand("SELECT GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance FROM G_Term " & _
                "WHERE GT_Term Like '%" & sv & "%'  SELECT * FROM G_Term", cn, SqlConn)

Then to access the rows, from your second select you DS.Tables(1).rows

Hope this helps, and hope I haven't made it sound too complicated, it's very simple and in my oppionion far easier to work with the data once you have it, than in old resultsets.

0
 
_EsamAuthor Commented:
Essentially I need the TopLevelId for the TopLevel Name...

_ESam
0
 
_EsamAuthor Commented:
Thanks..
I am to try it now and let you know..

_Esam
0
 
LindzKCommented:
If you use 'like' it will find records that are like the text you place in - so if for example you said

like '%Green%'

you may get several rows back -

Green Grass
Green Tea
Emerald Green

if so, you will have to either assume it's the first row - or use one of the for / each loops i suggested, or refine with a second select.

If you dont use like but =

then it will only return one row ever

then you can just

dim trow as datarow
trow = DS.Tables(0).Rows(0)
TopLevelID  = trow.Item("GT_ID")

but baring in mind, if you use = rather than like, you have to pass in the exact string
0
 
_EsamAuthor Commented:
Hi,
I have a little trouble working around with functions. :)
I guess I am still a little bit of structured type :)
Anyway, having trouble passing the args and values...

Assuming I have come thus far:

       If sv <> "" Then
                Dim cn As New OleDbConnection
                Dim s As String = Server.MapPath("ADVOICE.mdb")
                cn.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source
                = "   + s
                cn.Open()
                Dim da As New OleDbDataAdapter("SELECT GT_ID, GT_Term,
                GT_Definition,   GT_Example, GT_VUI_Relevance FROM G_Term " & _
                "WHERE GT_Term Like '%" & sv & "%'", cn)
                Dim ds As New DataSet
                da.Fill(ds, "G_Term")
                TermDetails.DataSource = ds
                TermDetails.DataMember = "G_Term"
                TermDetails.DataBind()
                If Not ds Is Nothing And ds.Tables.Count > 0 Then
                    Dim TopLevelID As Long
                        TopLevelID = ds.Tables(0).Rows(0).Item("GT_ID")
                End If

                cn.Close()
                If Not ds Is Nothing And ds.Tables.Count > 0 Then
                    Dim TopLevelID As Long
                        TopLevelID = ds.Tables(0).Rows(0).Item("GT_ID")
                End If
         End If

I understood the codes, but can't seem to connect them together...

I need to ask why do we even need to declare TopLevelID as Long?
It is a number in the database, perhaps a String/Integer?


I'm being stuck here.
How do I pass this id to the function?
_Esam
0
 
LindzKCommented:
it doesn't have to be a long - I declared it as a long, as i don't know how big the numbers get in your database.

To pass an id into the function that does the loop to get the top level you just pass in the variable,

you could do something like  ..

'once you know you have a value inside TopLevelID

Dim FinalID As Long
FinalId = GetTopLevelID(TopLevelID)


so you would place that around the same place that you have this code
If Not ds Is Nothing And ds.Tables.Count > 0 Then
                    Dim TopLevelID As Long
                        TopLevelID = ds.Tables(0).Rows(0).Item("GT_ID")

                    ' new line to call other function
                   Dim FinalID As Long
                  FinalId = GetTopLevelID(TopLevelID)

                End If


BTW - you should if you can, place the database connection inside a try/catch, as I illustrated before, that way - if there is a problem at any point in your code, you won't end up with lots of unclose connections, as the connection.close goes inside the 'finally' area, so will always execute.

Am I helping? or missing the point now heh - please let me know ,if i'm not explaining this right.
0
 
_EsamAuthor Commented:
Hi LindzK,
 Honestly speaking, you are giving me great examples.
 It's just me who is so new at this problem.
 I am trying my best to follow so that I can solve the problem.
 Here is what I have so far: (Not totally following your standard advisory methods but as I solve the problem first, I can definately get comfortable with fine desing methods. :)

   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Dim sv As String
        sv = Request.QueryString("search")
   If Not Page.IsPostBack Then
           If sv <> "" Then
                Dim cn As New OleDbConnection
                Dim s As String = Server.MapPath("ADVOICE.mdb")
                cn.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source = " + s
                cn.Open()
                Dim da As New OleDbDataAdapter("SELECT GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance FROM G_Term " & _
                "WHERE GT_Term Like '%" & sv & "%'", cn)
                Dim ds As New DataSet
                da.Fill(ds, "G_Term")
                TermDetails.DataSource = ds
                TermDetails.DataMember = "G_Term"
                TermDetails.DataBind()
               

                cn.Close()

                 If Not ds Is Nothing And ds.Tables.Count > 0 Then
                    Dim TopLevelID As Long
                    ' lets assume that you want the TopLevelID from the first row returned
                    TopLevelID = ds.Tables(0).Rows(0).Item("GT_ID")
                    Dim FinalID As Long
                    FinalID = GetTopLevelID(TopLevelID)
                    CategoryHyperLink.Text = FinalID  'I know this is wrong
                         'Eventually I would like to get the name of the TopLevelCategoryName
                         'from the TopLevelID that I got and bind it to a hyperlink that will
                          redirect the user to the intented first page.
                 End If
             End If
        End If
 End Sub

   

Private Function GetTopLevelID(ByVal TopLevelID As Long) As Long

        Dim cnt As New OleDbConnection
        Dim st As String = Server.MapPath("ADVOICE.mdb")
        cnt.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source = " + st
        cnt.Open()
        Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category" & _
        "WHERE GC_ID=" & TopLevelID, cnt)
        Dim dst As New DataSet
        dat.Fill(dst, "G_Category")
        'validate that there is something in the dataset
        If Not dst Is Nothing And Not dst.Tables.Count < 1 Then

            Dim finished As Boolean
            Dim notfound As Boolean = False

            'perform select on the dataset, to get the first row needed
            Dim rows() As DataRow
            rows = dst.Tables(0).Select("GC_ID = " & TopLevelID)
            If rows.Length > 0 Then
                'loop through the dataset, until the parent id = 0
                While Not finished
                    'perform a select on the dataset, to obtain the top level
                    'id
                    rows = dst.Tables(0).Select("GC_ID = " & rows(0).Item("GC_Parent"))
                    If Not rows.Length < 1 Then
                        If rows(0).Item("GC_Parent") = 0 Then
                            finished = True
                        End If
                    Else
                        finished = True
                        notfound = False
                    End If

                End While
            End If
            If Not notfound Then
                Return rows(0).Item("GC_ID")
            Else
                Return Nothing
            End If
        End If
    End Function

So far, I get an error saying that the FROM clause is not correct inside the GetTopLevelID function.
Please also let me know how should I declare the variables FinalID, TopLevelID since the underlying GC_ID in MS Access in declared as Number. I am not totally comfortable with type conversion yet.

Please let me know.
THank you so much.
_Esam
0
 
_EsamAuthor Commented:
Hi,
 There were couple of erros in the previous code:
1)   Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category" & _
        "WHERE GC_ID=" & TopLevelID, cnt)
Here I also need to include the GC_ID (foreing key to the G_Category table for this G_Term)

2)TopLevelID = ds.Tables(0).Rows(0).Item("GT_ID")
I should have .Iterm("GC_ID") here so that I can get the top leve category from this related GC_ID.

Let me know If I am not clear.
Thx.
_Esam
0
 
_EsamAuthor Commented:
Hi,
 The first error should have been as: (sorry, copy, paste problem)
1)   Dim mySqlCommand As New SqlCommand("SELECT GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance FROM G_Term " & _
                "WHERE GT_Term Like '%" & sv & "%'", cn, SqlConn)

Here I also need to include the GC_ID (foreing key to the G_Category table for this G_Term)

2)TopLevelID = ds.Tables(0).Rows(0).Item("GT_ID")
I should have .Iterm("GC_ID") here so that I can get the top leve category from this related GC_ID.

Let me know If I am not clear.
Thx.
_Esam
0
 
LindzKCommented:
Hi Esam,
    i've only just turned on my comp this morning - give me a few mins, and i'll have a read through what you have now :)

0
 
_EsamAuthor Commented:
Ok...
Thanks...I will wait to hear from you shortly..

_Esam
0
 
LindzKCommented:
Hello again -

• The problem with the FROM clause

If the code you posted is exactly what you have in your project, then you have no space between the table name and the WHERE.

It would print out as a string as SELECT GC_ID, GC_Cetegory, GC_Parent FROM G_CategoryWHERE GC_ID=

you need to add a space such as

Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category " & _
        "WHERE GC_ID=" & TopLevelID, cnt)

Also, one other problem you may encounter is that TopLevelID is a long, and you are passing it into the select string - it expects a string, so you should put the CStr function around TopLevelID  so your final select string should look as follows :-

Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category " & _
        "WHERE GC_ID=" & Cstr(TopLevelID), cnt)


• Now, how to declare TopLevelID - a long is a very large number ( int64 ), i'm not sure how large an access number will go - you could declare it as an integer ( which is a little smaller ) if you want - but it doesn't really matter - int32 int64 long double, will all convert into each other just fine ( as long as the number isn't too large - ie, if you place a really large number into an int32 or integer, it won't have enough memory to fit ).

• Can you explain a little more what you mean by this ?  i'm still half asleep and didn't fully understand it hehe "Here I also need to include the GC_ID (foreing key to the G_Category table for this G_Term)"

• If you need the GC_ID instead, you just do TopLevelID = ds.Tables(0).Rows(0).Item("GC_ID"),   if you need both of them, you could do
Dim TopLevelID1 as Long = ds.Tables(0).Rows(0).Item("GT_ID")
Dim TopLevelID2 as Long = ds.Tables(0).Rows(0).Item("GC_ID")


As for the needing to include GC_ID,  here is the list of tables you originally posted
G_Category table:
GC_ID GC_Parent GC_Category

G_Term table:
GT_ID GC_ID

GC_ID comes from G_Term - do you need to join the two tables, to get this results ?
I'm not entirely sure I understand what you are trying to do here - could you repost your table structures, and how they are linked, and try to write again what it is you need for that bit ?

Thanks.

0
 
_EsamAuthor Commented:
Hi LindzK,

My two tables are: G_Category and G_Term.

G_Category table:
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_Term table:
GT_ID GT_Term, GT_Definition, GT_Example, GC_ID

You know that My G_Category table has a recursive nature (top level categories have their GC_Parent set to 0, but also there are subcategories that are defined in terms of the GC_Parent id which are actually the GC_ID for a particular category/subcategory).
What I am doing is passing a search query string to the G_Term table to get the Term related info.
The specific Term is also linked (joined?)(with GC_ID > foreign key referencing the G_Category table) with a certain level of category/subcategory of the G_Category table.

So, I was trying to catch this specifice level's category/subcategory id (GC_ID) from this G_Term table, and pass it to the function so that I can find the TopLevelID for this particular GC_ID that I happen to find from the G_Term table.

If you understood right, then I believe I must be making another mistake!
I think I also need to join the G_Term table with the G_Category table to find the GC_ID for the Term at a specifice level, and then perhaps pass it to the function.
I believe you are right saying that I need to join the two tables?

Let me know if it is clear now..:)

Thanks.
_Esam
0
 
_EsamAuthor Commented:
Hi,
My two tables are: G_Category and G_Term.

G_Category table:
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_Term table:
GT_ID GT_Term, GT_Definition, GT_Example, GC_ID

GC_ID is a foreign key that references GC_ID in G_Category.
This GC_ID can be at any arbitrayy level of the category/subcategory in the G_Category table.
Subcategories have arbitrary level, but not to exceed 7 levles.
If I had fixed sublevles, then I could have found the top level with complex join query, I guess!

Hope I am clear.
Let me know.
_Esam
0
 
LindzKCommented:
Can you not

Dim da As New OleDbDataAdapter("SELECT GC_ID, GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance FROM G_Term " & _
                "WHERE GT_Term Like '%" & sv & "%'", cn)

and then get out the GC_ID in the same way that you get the  GT_ID ?

Dim gc_id as long = DS.Tables(0).Rows(0).Item("GC_ID")





0
 
_EsamAuthor Commented:
I think I can since I am actually not getting any other info from the G_Category table?
relating this GC_ID...
But I am little curious ..
Since this GC_ID is actually a foreign key to the G_Category table.
But for a particular term in the G_Term table, this GC_ID should have a value.

_Esam
0
 
_EsamAuthor Commented:
This is exactly how I have it:

Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category"& _
        "WHERE GC_ID=" & CStr(TopLevelID), cnt)

The function prototype is:
Private Function GetTopLevelID(ByVal TopLevelID As Long) As Long

Please note that I directly passed the function argument in the where clause!
Should I declare another local variable within the function body and then pass it in the
where clause?

_Esam
0
 
_EsamAuthor Commented:
Hi,
Not exactly as shown when I posted it...
the _  is connected with & in the upper line..

_Esam
0
 
_EsamAuthor Commented:
Hi,
I tested to see if I can get the GC_ID eventhough it is a foreign key.
It does return the GC_ID.
I must be doing something wrong then..
Somehow the function is not getting the value of this GC_ID

_Esam
0
 
LindzKCommented:
Have a read of this - to see if i'm understanding what you are trying to do properly.  I have added some comments at the bottom, to try to explain where I think it's going wrong

This is what i think is happening now

1)The GC_ID is the thing that links the Category table to the Term table

2)On page load  - you preform a selcet on the G_Term table
based off a search string on the term

3)Then you take the GT_ID from your query

4)and call the gettoplevelid function using the gt_id

5)Now for the GetTopLevelId function

6)currently you :

7)perform an initial select on the G_Category table where the GC_ID =
the value that you passed in to it.

8)and loop through getting the GC_ID until its parent id = 0                  

I think this is how it works at the moment - am I correct ?                  

If so - I have a few comments to make it work correctly - otherwise, let                  
me know which bit I missunderstood                  

•  At point 3 - you should take the GC_ID and GT_ID, as this                  
is the id you need                  

• You therefore need to ammend your function so that it takes in two parameters                  
                  
Private Function GetTopLevelID(ByVal GC_ID As Long, ByVal GT_ID As Long) As Long                  
                  
• At point 7 - you should perform a select on the whole table                  
this is because if you say where id = soandso here - you have no other                  
rows to loop through

• Just before your loop, you do a select on the dataset
where the GC_ID = passed in GC_ID, and the GT_ID = passed in
GT_ID

• Get the initial GC_ID from the rows returned

• use the GC_ID you have now gained, as the toplevelid, you
use in your loop
0
 
_EsamAuthor Commented:
Hi,
1)The GC_ID is the thing that links the Category table to the Term table

Yes.

2)On page load  - you preform a selcet on the G_Term table
based off a search string on the term

Yes

3)Then you take the GT_ID from your query

No, I was mistaken earlier, I take GC_ID from the query since I want to loop through the
G_Category table to find the top level GC_ID for this selected GC_ID

4)and call the gettoplevelid function using the gt_id

I call the GetTopLevelID function with the gc_ID



5)Now for the GetTopLevelId function

6)currently you :

7)perform an initial select on the G_Category table where the GC_ID =
the value that you passed in to it.

Yes, the value I passed onto the function with the argument parameter.
I directly used the parameter that I passed in the function as GC_ID= 'the parameter of the function i.e. TopLevelID

8)and loop through getting the GC_ID until its parent id = 0

Yes.

I still have the same error message..
Syntax error is WHERE clause..  "WHERE GC_ID=" & TopLevelID

Thanks.
_Esam
0
 
LindzKCommented:
if what i said was correct, then try the following ammendments
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim sv As String
sv = Request.QueryString("search")
If Not Page.IsPostBack Then
If sv <> "" Then
Dim cn As New OleDbConnection
Dim s As String = Server.MapPath("ADVOICE.mdb")
cn.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source = " + s
cn.Open()
Dim da As New OleDbDataAdapter("SELECT GC_ID, GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance FROM G_Term " & _
"WHERE GT_Term Like '%" & sv & "%'", cn)
Dim ds As New DataSet
da.Fill(ds, "G_Term")
TermDetails.DataSource = ds
TermDetails.DataMember = "G_Term"
TermDetails.DataBind()


cn.Close()

If Not ds Is Nothing And ds.Tables.Count > 0 Then
Dim TopLevelID As Long
' lets assume that you want the TopLevelID from the first row returned
'TopLevelID = ds.Tables(0).Rows(0).Item("GT_ID")
'*********** AMMENDED CODE **********
Dim GC_ID as Long = ds.Tables(0).Rows(0).Item("GC_ID")
Dim GT_ID as Long = ds.Tables(0).Rows(0).Item("GT_ID")
Dim FinalID As Long
FinalID = GetTopLevelID(GC_ID, GT_ID)

'*******************************************

'Dim FinalID As Long
'FinalID = GetTopLevelID(TopLevelID)
CategoryHyperLink.Text = FinalID 'I know this is wrong
'Eventually I would like to get the name of the TopLevelCategoryName
'from the TopLevelID that I got and bind it to a hyperlink that will
redirect the user to the intented first page.
End If
End If
End If
End Sub


'************* AMMENDED CODE ***********************
Private Function GetTopLevelID(ByVal GC_ID As Long, GT_ID As Long) As Long

Dim cnt As New OleDbConnection
Dim st As String = Server.MapPath("ADVOICE.mdb")
cnt.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source = " + st
cnt.Open()
'Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category" & _
'"WHERE GC_ID=" & TopLevelID, cnt)
Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category", cnt)

Dim dst As New DataSet
dat.Fill(dst, "G_Category")
'validate that there is something in the dataset
If Not dst Is Nothing And Not dst.Tables.Count < 1 Then

Dim finished As Boolean
Dim notfound As Boolean = False

'perform select on the dataset, to get the first row needed
Dim rows() As DataRow
rows = dst.Tables(0).Select("GC_ID = " & CStr(GC_ID) & " and GT_ID = " & Cstr(GT_ID))
If rows.Length > 0 Then
'loop through the dataset, until the parent id = 0
While Not finished
'perform a select on the dataset, to obtain the top level
'id
rows = dst.Tables(0).Select("GC_ID = " & rows(0).Item("GC_Parent"))
If Not rows.Length < 1 Then
If rows(0).Item("GC_Parent") = 0 Then
finished = True
End If
Else
finished = True
notfound = False
End If

End While
End If
If Not notfound Then
Return rows(0).Item("GC_ID")
Else
Return Nothing
End If
End If
End Function

0
 
LindzKCommented:
The error is on this line ?

Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category"& _
        "WHERE GC_ID=" & CStr(TopLevelID), cnt)


You are missing a space again beween G_Category and WHERE

after the category, just add a space, before the end of the quotes
0
 
_EsamAuthor Commented:
Hi,
Since I only needed to pass the GC_ID to the function,
I made the adjustments to your ammended funtion, just passing one paremeter..
Now the error is:

GC_ID not declared 'this shows here:

rows = dst.Tables(0).Select("GC_ID = " & CStr(GC_ID))

_Esam
0
 
_EsamAuthor Commented:
Ok, I will add a space..

_Esam
0
 
_EsamAuthor Commented:
Hi,
Now I have a new error:

Exception Details: System.IndexOutOfRangeException: Index was outside the bounds of the array.

Source Error:


Line 145:            End If
Line 146:            If Not notfound Then
Line 147:                Return rows(0).Item("GC_ID")   'this is the error line
Line 148:            Else
Line 149:                Return Nothing
 

_Esam
0
 
LindzKCommented:
GC_ID should be which ever the name of the parameter you recieve in your function , also if you removed GT_ID from the function, remove the "and GT_ID etc etc, from the end of your select

it means it didn't find any - you need to check that rows.length > 0
0
 
_EsamAuthor Commented:
Hi,
I declared the idvalue as:

If Not ds Is Nothing And ds.Tables.Count > 0 Then
                    Dim gc_ID As Long
                    gc_ID = ds.Tables(0).Rows(0).Item("GC_ID")
                    Dim FinalID As Long
                    FinalID = GetTopLevelID(gc_ID)
End If

Although I declared the variable, I didn't explicitely used it to a control to display it.
Should this be a problem?
I think not.

The other function conditions are as:

If Not dst Is Nothing And Not dst.Tables.Count < 1 Then

            Dim finished As Boolean
            Dim notfound As Boolean = False

            'perform select on the dataset, to get the first row needed
            Dim rows() As DataRow
            rows = dst.Tables(0).Select("GC_ID=" & CStr(TopLevelID))
            If rows.Length > 0 Then
                'loop through the dataset, until the parent id = 0
                While Not finished
                    'perform a select on the dataset, to obtain the top level
                    'id
                    rows = dst.Tables(0).Select("GC_ID=" & rows(0).Item("GC_Parent"))
                    If Not rows.Length < 1 Then
                        If rows(0).Item("GC_Parent") = 0 Then
                            finished = True
                        End If
                    Else
                        finished = True
                        notfound = False
                    End If

                End While
            End If
            If Not notfound Then
                Return rows(0).Item("GC_ID")  'this is the erroe line.
            Else
                Return Nothing
            End If
End If


Array index is out of bounds error again!

 If Not notfound Then
                Return rows(0).Item("GC_ID")  'this is the erroe line.
            Else
                Return Nothing
            End If

The term search I quried, should have returned a value.!

_ESam
0
 
_EsamAuthor Commented:
HI,
Please also note, that it is also possible that a Term's GC_ID is directly linked to the
toplevel id (where the GC_Parent is 0).

i.e. GC_ID id's GC_Parent can be 0, while not needing any search.
Should this be a problem for the logic used?

_Esam
0
 
LindzKCommented:
The error you are getting here

rows = dst.Tables(0).Select("GC_ID = " & CStr(GC_ID))

this comes from here ? -

Private Function GetTopLevelID(ByVal GC_ID As Long, GT_ID As Long) As Long

Dim cnt As New OleDbConnection
Dim st As String = Server.MapPath("ADVOICE.mdb")
cnt.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source = " + st
cnt.Open()
'Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category" & _
'"WHERE GC_ID=" & TopLevelID, cnt)
Dim dat As New OleDbDataAdapter("SELECT GC_ID, GC_Category, GC_Parent FROM G_Category", cnt)

Dim dst As New DataSet
dat.Fill(dst, "G_Category")
'validate that there is something in the dataset
If Not dst Is Nothing And Not dst.Tables.Count < 1 Then

Dim finished As Boolean
Dim notfound As Boolean = False

'perform select on the dataset, to get the first row needed
Dim rows() As DataRow
rows = dst.Tables(0).Select("GC_ID = " & CStr(GC_ID) & " and GT_ID = " & Cstr(GT_ID))
If rows.Length > 0 Then
'loop through the dataset, until the parent id = 0
While Not finished
'perform a select on the dataset, to obtain the top level
'id
rows = dst.Tables(0).Select("GC_ID = " & rows(0).Item("GC_Parent"))
If Not rows.Length < 1 Then
If rows(0).Item("GC_Parent") = 0 Then
finished = True
End If
Else
finished = True
notfound = False
End If

what I meant was
Function GetTopLevelID(ByVal GC_ID As Long, GT_ID As Long)

if your function still takes in a parameter called TopLevelID, rather than GC_ID

then you must reference TopLevelID, rather than GC_ID at the point you get the error -


rows = dst.Tables(0).Select("GC_ID = " & CStr(TopLevelID))



0
 
LindzKCommented:


    If Not rows.Length < 1 Then
                        If rows(0).Item("GC_Parent") = 0 Then
                            finished = True
                        End If
                    Else
                        finished = True
                        'notfound = False   'this should be true
                        notfound = true
                    End If

                End While
            End If
            If Not notfound Then
                Return rows(0).Item("GC_ID")  'this is the erroe line.
            Else
                Return Nothing
            End If

but it does seem that your select is returning nothing,
all I can suggest at this point, is puttnig a break line in, just before you loop - and stepping through the code, checking the values of everything, on each line - that way the point it is going wrong, will hopefully jump out at you
0
 
LindzKCommented:
if GC_id's parent is 0 - you should not go into the loop - add an extra if, to test this before continueing
0
 
_EsamAuthor Commented:
Hi,
I think the problem is because,
a given GC_ID passed to the function can also have a GC_Parent that is 0 since the
GC_ID in the G_Term table can be related to any level including the top level.
I think, the logic probably needs to be changes somehow..I don't know...???

_Esam
0
 
LindzKCommented:
here you go - the extra logic, to allow an inital parent ID of 0

rows = dst.Tables(0).Select("GC_ID=" & CStr(TopLevelID))
            If rows.Length > 0  Then
                if not rows.Item("GC_Parent") = 0 then
                'loop through the dataset, until the parent id = 0
                While Not finished
                    'perform a select on the dataset, to obtain the top level
                    'id
                    rows = dst.Tables(0).Select("GC_ID=" & rows(0).Item("GC_Parent"))
                    If Not rows.Length < 1 Then
                        If rows(0).Item("GC_Parent") = 0 Then
                            finished = True
                        End If
                    Else
                        finished = True
                        notfound = False
                    End If

                End While
               else
                  finished = true
               end if
            End If
0
 
_EsamAuthor Commented:
Hi LindzK,
Thank you so much for your patience,
I did include extra if before the loop,
to test if the GC_Parent was 0, then set finished = true
and it worked.

Now I will try to do something with it...

so far...as I consider...
We have found a solution for this question...:):):)
To my discretion, i will reward you the full possible points..

Thanks.
_Esam

0
 
_EsamAuthor Commented:
I actually tried a little different way...it worked..
Like this:

Dim rows() As DataRow
            rows = dst.Tables(0).Select("GC_ID=" & CStr(TopLevelID))
            If rows(0).Item("GC_Parent") = 0 Then
                finished = True
            Else
                If rows.Length > 0 Then
                ....the usual codes I had before...


thx.
_Esam
0
 
_EsamAuthor Commented:
I haven't exclusively tested to find out what the value was...
The program executed fine...
How can I quickly test to see if it did return the right ID>..
I have set up a variable as gc_ID..
I had been binding all my data to datalists so far..
How can I bind this say to a label? to see the value returned?
I will try something by the time you have a chance to get to this posting...

_Esam
0
 
_EsamAuthor Commented:
I tested the value returned...
And it worked..result is verified to be the toplevel GC_ID...

Thanks a million...
I thank you again for your patience...

_Esam
0
 
LindzKCommented:
Quickest way, is to just debug through it, and add a watch to GC_ID and GC_Parent - so you can see
which values it's putting them in.

You can bind values to a textbox - if that helps you for visual purposes.

or you can  write teh results out to your page -

response.write("GC_ID = "&CSTr(GC_ID) where ever you need

or

response.wrtie("<Script>window.alert("debugging function - GC_ID = " & CStr(GC_ID) & ")</script>")
0
 
_EsamAuthor Commented:
Hi,
there is something strange going on here...
If i search for a term that it at some subcategory level..
I get the correct category id..
But if I search for a term that is of a top level category..I get the category id to be 0.
Strange..
I will try to figure it out...

_Esam
0
 
_EsamAuthor Commented:
Ok..
It's working...now..I simply returned the GC_ID if the GC_Parent is 0.
Now it's returning the GC_ID for any level..

_Esam
0
 
_EsamAuthor Commented:
Hi,
Let me know if you would be around for a while..
I will ask another question...
Since you are already familiar with my problem..
I have my solution for this question now..
THanks.
_Esam
0
 
_EsamAuthor Commented:
I will ask a new question anyway without preferring that you answer it..
I know you have strong ethical values in terms of EE rules.
I respect it..

Thanks.
_Esam
0
 
LindzKCommented:
I'm still here - what is the problem you have ? )
0
 
_EsamAuthor Commented:
A new question for it...
It is fair to close this one.

_Esam
0
 
_EsamAuthor Commented:
Hi LindzK,
 Could you please look at my other posting since you are already familiar with my problem..

  http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21140622.html
 The other expert dropped couple of lines and left...
 I would appreciate your help..

THanks.
_Esam
0
 
LindzKCommented:
Hi Esam,

ok, i'll have a look - bit busy atm - will look in a few hours :)

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 35
  • 22
Tackle projects and never again get stuck behind a technical roadblock.
Join Now