Solved

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

Posted on 2004-09-19
57
449 Views
Last Modified: 2010-05-18
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
Comment
Question by:_Esam
  • 35
  • 22
57 Comments
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Accepted Solution

by:
LindzK earned 500 total points
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Assisted Solution

by:LindzK
LindzK earned 500 total points
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
Essentially I need the TopLevelId for the TopLevel Name...

_ESam
0
 

Author Comment

by:_Esam
Comment Utility
Thanks..
I am to try it now and let you know..

_Esam
0
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Assisted Solution

by:LindzK
LindzK earned 500 total points
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
Ok...
Thanks...I will wait to hear from you shortly..

_Esam
0
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
Hi,
Not exactly as shown when I posted it...
the _  is connected with & in the upper line..

_Esam
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 
LVL 5

Assisted Solution

by:LindzK
LindzK earned 500 total points
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
Ok, I will add a space..

_Esam
0
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility


    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
 
LVL 5

Assisted Solution

by:LindzK
LindzK earned 500 total points
Comment Utility
if GC_id's parent is 0 - you should not go into the loop - add an extra if, to test this before continueing
0
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Assisted Solution

by:LindzK
LindzK earned 500 total points
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
I'm still here - what is the problem you have ? )
0
 

Author Comment

by:_Esam
Comment Utility
A new question for it...
It is fair to close this one.

_Esam
0
 

Author Comment

by:_Esam
Comment Utility
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
 
LVL 5

Expert Comment

by:LindzK
Comment Utility
Hi Esam,

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

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now