[RESOLVED] DataView and ToTable()

[jdogg]

Hey all I was just wondering if anyone is familar with getting the distinct value from a DB using this method? I have the code written but it is not giving me any distinct values.

Private Sub BuildListView()
        Dim dsCategories As DataSet
        dsCategories = GetCategories(GetLanguage)
        Dim dv As New DataView(dsCategories.Tables("Categories"))
        dv.Sort = "CATEGORY_NAME"
        dv.RowFilter = "ISNULL(AD_HELP_CONTENT_ID,0)<>0"
        Dim newTable As DataTable = dv.ToTable(True, "CATEGORY_NAME", "AD_HELP_CATEGORY_ID")

        'There are no child nodes for this tree
        tvList.Nodes.Clear()

        'Load the base nodes
        Dim node As TreeNode
        Dim rowView As DataRow
        For Each rowView In newTable.Rows
            node = New TreeNode(CStr(rowView("CATEGORY_NAME")), CStr(rowView("AD_HELP_CATEGORY_ID")))
            node.SelectAction = TreeNodeSelectAction.SelectExpand
            tvList.Nodes.Add(node)
        Next
    End Sub

this is what I have so far, it does work and it populates the tree view, but it has data that is repeated over again. I would like to only show one, each of the nodes leads to different pages, but this is for an online help, so while it has different sections, all the content pretty much mean the same, First Name is First Name no matter where you go.

[riteshjain1982]

Edit:
sorry i just overlook the dataview's .Totable method...... just on other note;why dont you use DISTINCT clause to fetch the unique record from DB it self

[jdogg]

I could do that, but any changes i make to the DB needs to be approved by the DBA, could take a couple of days, plus at the moment I am the only doing any work cause they are currently testing the application, my part is a stand alone add-on, so no one is going to be doing anything till the testing is over, I don't really feel like waiting for them to get to me for this, plus it is not part of the requirements, just a little bonus I am doing. so they will not be in much of a hurry to get much changed...

[gep13]

Hello jdogg,

Did you get an answer to your question?

Unfortunately, I am not able to help you, but what I would say is similar to what ritesh was saying. Any operations that you want to take on, should be done in the correct place. In this case, that is to alter the recordset as close to the source as possible. i.e. modify the SQL Query that you are executing. Either that, or alter the DataSet that you get back from the Stored Procedure. Editing the data at the point when you are binding it to the User Interface is too late, because at that point you are very closely coupling the UI to the Data, and that is not a good thing.

Gary

[jdogg]

I did get it to work Gep, but I agree with both of you that it should be done in the DB, which is what we are going to eventually do, at the moment we were just playing around with different options to see what would work best. While i got the ToTable method to work, we have decided to not use it, mostly for the reasons you stated, and also have been having some other issues with it. Right now we are using some of the stored procedures we have, and for the final project we are either gonna create a new procedure or just modify the one we are using now, depends on how the client wants it look like and work, plus like i may have stated, this is just a side bonus to to final application, so if they don't like it, we are not going to use it, which was why I was trying to use ToTable() just to give them an idea of how it would work. But since my boss has decided on a different direction it is a moot point, but just for the sake of answering the question that I had asked: when I removed

CStr(rowView("AD_HELP_CATEGORY_ID")

from the code it worked, actually it was working with the code in, but in the DB, each record as a name and an id, so it was give me the pair, so since each pair was different it was still giving me the distacnt records, I just didn't see it at the time. So I am going to mark this thread as resolved.

[gep13]

Sounds like you are on the right track anyway

Thanks for posting back, and closing out your thread! Hope you get it all working the way you want going forward.

Gary