_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.Navigate Url = "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
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.Navigate
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
One more question, cant you get the GC_ID (Top Level) by using an INNER JOIN in the SQL query?
Best, Nauman.
Best, Nauman.
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
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
ASKER
Let me know if you need to see the whole code to see where I am running short..
_Esam
_Esam
ASKER
Here is the code:
Dim sv As String
sv = Request.QueryString("searc h")
If Not Page.IsPostBack Then
If sv <> "" Then
Dim cn As New OleDbConnection
Dim s As String = Server.MapPath("ADVOICE.md b")
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.md b")
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(CommandB ehavior.Cl oseConnect ion)
dr.Read()
Me.CatHyperLink.Text = CStr(dr("GC_Category"))
Me.CatHyperLink.NavigateUr l = "VUI_G_Categories.aspx"
cnn.Close()
dr.Close()
cmd.Dispose()
cnn.Dispose()
Next
End If
End If
End If
THanks.
_Esam
Dim sv As String
sv = Request.QueryString("searc
If Not Page.IsPostBack Then
If sv <> "" Then
Dim cn As New OleDbConnection
Dim s As String = Server.MapPath("ADVOICE.md
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.md
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(CommandB
dr.Read()
Me.CatHyperLink.Text = CStr(dr("GC_Category"))
Me.CatHyperLink.NavigateUr
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.C ount > 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.
if ds.Tables["G_Term"].Rows.C
'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.
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
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
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.md b")
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_I D=" & 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_I D=" & 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
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.md
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_I
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_I
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.Navigate Url = "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 ?
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.Navigate
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
If Not ds Is Nothing AndAlso ds.Tables.Count > 0 AndAlso DS.Tables(0).Rows.count > 0 Then
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
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..
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
HTH, Nauman.