Link to home
Start Free TrialLog in
Avatar of _Esam
_Esam

asked on

How to return function value and bind it to multiple search results (VB.NET)

Hi,
Here is the synopsis:
Dim da As New OleDbDataAdapter("SELECT GT_ID, GT_Term, GT_Definition, GT_Example, GT_VUI_Relevance, GC_ID 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()

I am binding multiple search results to the TermDetains datalist.
For each search result, I also extracted the GC_ID and passed it to a function to get the
Top level GC_ID from another table named G_Category that has a recursive nature.
I was able to get the Top Level GC_ID but when I tried to bind to a tentative hyperlink control, it only binds to the last search result.
I need to know how can I bind this Top Level GC_ID to all the search results ?
I now have just one declared hyperlink control...
The code is used is:

If Not ds Is Nothing And ds.Tables.Count > 0 Then
                    Dim trow As DataRow
                    For Each trow In ds.Tables(0).Rows
                        Dim gc_ID As Long
                        gc_ID = trow.Item("GC_ID")
                        Dim FinalID As Long
                        FinalID = GetTopLevelID(gc_ID)
                        CategoryHyperLink.Text = CStr(FinalID)
                        CategoryHyperLink.NavigateUrl = "VUI_G_Categories.aspx"
                    Next
End If

This only binds the last search result's GC_ID (Top Level) to the hyperlink..
What do I need to do, if I want to have this GC_ID to be bound to all the search results..?
Do I somehow dynamically create more hyperlinks for multiple search results?

Please let me know..
Thx.
_Esam
Avatar of nauman_ahmed
nauman_ahmed
Flag of United States of America image

Make sure that you are not using a globally declared dataset variable here. Because if it is overridden in the function, it will always display the last result.

HTH, Nauman.
One more question, cant you get the GC_ID (Top Level) by using an INNER JOIN in the SQL query?

Best, Nauman.
Avatar of _Esam
_Esam

ASKER

I am not sure if I follow you.
No, I coudn't get the top level GC_ID due to some design constraints of the table.
I had to do it in a funtion.
I don't think I declared global dataset.

_Esam
Avatar of _Esam

ASKER

Let me know if you need to see the whole code to see where I am running short..
_Esam
Avatar of _Esam

ASKER

Here is the code:

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, GC_ID 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   'this could be wrong???

                  Dim trow As DataRow
                      For Each trow In ds.Tables(0).Rows
                        Dim gc_ID As Long
                        'get each id out
                        gc_ID = trow.Item("GC_ID")

                        Dim FinalID As Long
                        FinalID = GetTopLevelID(gc_ID)
                        Dim cnn As New OleDbConnection
                        Dim sn As String = Server.MapPath("ADVOICE.mdb")
                        cnn.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source = " + sn
                        Dim cmd As New OleDb.OleDbCommand
                        Dim dr As OleDb.OleDbDataReader

                        cmd.CommandType = CommandType.Text
                        cmd.CommandText = _
                        "SELECT GC_ID, GC_Category FROM G_CATEGORY WHERE GC_ID=" & CStr(FinalID)
                        cmd.Connection = cnn
                        cnn.Open()
                        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                        dr.Read()

                        Me.CatHyperLink.Text = CStr(dr("GC_Category"))
                        Me.CatHyperLink.NavigateUrl = "VUI_G_Categories.aspx"
                        cnn.Close()
                        dr.Close()
                        cmd.Dispose()
                        cnn.Dispose()
            Next
      End If
  End If
End If

THanks.
_Esam
Usually when you check the dataset if it contains any rows then you should check like this:

if ds.Tables["G_Term"].Rows.Count > 0 then
   'More code
end if

I am still little bit confused about your code. Are you trying to search the child table and retrieve the GC_ID from the parent table?

-Nauman.
Avatar of _Esam

ASKER

Yes, since my database is like this:

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.

This is the reason I had to use a function, unless you can provide me with a join query :)

Anyway, what is the reason for not returning multiple hyperlinks??

_Esam
 
Avatar of _Esam

ASKER

I need to know why am I not gettin multiple function value returned for the multiple search results...
I get some error message if I try to use ds.Tables. etc as you prescribed..probably due to the function....

Do yoy need to see the funtion too:

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 ", 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(TopLevelID))
            If rows(0).Item("GC_Parent") = 0 Then
                finished = True
                Return rows(0).Item("GC_ID")
            Else
                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 If
    End Function
Hi Esam,

Can I just confirm what you are doing / and what you are trying to do ?

you bind the results from your G_Term query to a datalist

result1_From_G_Term
result2_From_G_Term
retsult3_From_G_Term

You have a hyperlink - CategoryHyperlink

You loop through your results -  and add the top level ID to the link

CategoryHyperLink.Text = CStr(FinalID)
                        CategoryHyperLink.NavigateUrl = "VUI_G_Categories.aspx"

Therefore - you are only ending up with the last toplevelid ? Because you only have one hyperlink, so each time you go through the loop - you are overwriting the hyperlink details?  is that what's happening at the moment ?

And you want to have a hyperlink, for each level - to co-inside with the restuls from your g_term query ?
ASKER CERTIFIED SOLUTION
Avatar of LindzK
LindzK
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
oh btw -

the line of code to check you have rows in your table ( and indeed tables in your dataset, to be doubly safe ), should look as follows :

If Not ds Is Nothing AndAlso ds.Tables.Count > 0 AndAlso DS.Tables(0).Rows.count Then  

I used and also - because it won't test the second thing, until it knows the first one is true - that way, if the dataset was nothing, it wont throw an error for doing the count on the tables.
edit !

If Not ds Is Nothing AndAlso ds.Tables.Count > 0 AndAlso DS.Tables(0).Rows.count > 0 Then  
Avatar of _Esam

ASKER

Hi LindzK,
I apologize for misspelling "datalist" , it should have been a Repeater.
I actually finally realized why I am not getting multiple results for TopLevelID..
It was actually as you pointed out.
I haven't worked with datagrid yet..
I used datalists, and repeaters..since their layout suits for some of my problems.
My understanding on Datagrids are that they are pretty much like table format..
Anyway, I was planning to add a hyperlink control to my repeater and bind the toplevelid
to it..
this should work...

I actually have two matters to consider for this term.aspx page..
One is to show the details from a search ..and the other is to show the details from..
browsing categories/subcategories..and then selecting a term...thus showing the details for the terms..
I was working on the terms from categories/subcategories perspective..
I was easier to add the toplevel id ..since I could pass that from a session variable...while browsing categories/subcategories and then selecting a term..

I was little lost due to recursive nature of the category table...
Thus your function helped me enourmously...

I will try to add a hyperlink to the repeater..from as much knowledge as I have , and let you know where I stand..
THanks much for your attention..

_Esam