Solved

Seperate categories from tablle into seperate gridview rows

Posted on 2012-04-02
14
331 Views
Last Modified: 2012-04-09
What I have is a many to many table tha has category and the datafields they assigned to the categories.  What I am trying to do is load them into a combined gridview row from the datatable.  Currently the db looks as so.

intCatId         Database field
28              strCpuManufac
28              ftCpuClock
28              strHardDrive
28              strOSArchitect
26              strSn
26              strComputerName
26              strCpuName
26              strCpuCaption
26              strCpuManufac
26              fltCpuCores
26              ftCpuClock
29              ftMemory
29              strMacHard
29              strIP
29              strOs

So when I bring them into the asp page I want to combine them into a gridview as one category id and all the fields as one string, so it would look as so.

CAtId                                                   Datafields
26                     strSn,strComputerName,strCpuName,strCpuCaption,strCpuManufac,
                         fltCpuCores,ftCpuClock
28                      strCpuManufac,ftCpuClocK,strHardDrive, strOSArchitect
29                      ftMemory,strMacHard,strIP,strOs


 Private Sub FillRegistry()
        Dim CatId As Integer
        Dim cat As String
        Dim Field As String

        sql = "Select r.intCategoryID, c.strCategory, r.strFieldname from drat_regRelation r INNER JOIN Drat_Category c on c.intCategoryId = r.intCategoryId"

        myDataTable = New DataTable
        myDataTable = getData(sql)

        For Each row As DataRow In myDataTable.Rows
            CatId = row(0)
            cat = row(1)
            Field += row(2) & ", "
        Next

        Field.Remove(Field.Length - 1, 1)

        Dim dt As New DataTable
        Dim ctId As DataColumn = dt.Columns.Add("intCategoryId")
        Dim Category As DataColumn = dt.Columns.Add("Category")
        Dim Fields As DataColumn = dt.Columns.Add("DataFields")
        Dim dtRow As DataRow

        dt.Rows.Add(CatId, cat, Field)

        myRadGrid.DataSource = dt
        myRadGrid.DataBind()

        pnlRegGrid.Visible = True
    End Sub
0
Comment
Question by:kdeutsch
  • 7
  • 6
14 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 37800046
what is not working for you?
the result gridview?
0
 

Author Comment

by:kdeutsch
ID: 37800723
hi,

I cannot seemt to seperate the categories and the datafields.  Somehow you should be able to loop through and get the datafields to categories from the table I am putting the query into.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37800795
u can improve the sql query to combine data fields into a single string instead of doing so in the code:

here's the full sub:
Private Sub FillRegistry()

      sql = "Select c.intCategoryId as CAtId, c.strSn+','+c.strComputerName+','+c.strCpuName+','+c.strCpuCaption+','+c.strCpuManufac+','+c.fltCpuCores+','+c.ftCpuClock as Datafields from Drat_Category c INNER JOIN drat_regRelation r on c.intCategoryId = r.intCategoryId"

        myDataTable = getData(sql)

        myRadGrid.DataSource = myDataTable 
        myRadGrid.DataBind()

        pnlRegGrid.Visible = True
    End Sub

Open in new window



if u need to combine more fields into the string then change the sql, for example if you wish to add strOs:
sql = "Select c.intCategoryId as CAtId, c.strSn+','+c.strComputerName+','+c.strCpuName+','+c.strCpuCaption+','+c.strCpuManufac+','+c.fltCpuCores+','+c.ftCpuClock+','+c.strOs as Datafields from Drat_Category c INNER JOIN drat_regRelation r on c.intCategoryId = r.intCategoryId"

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 37800842
HI,
I would have to rewrite the sql statment each time someone added a new category to the table, the table is able in my original post it has the intCatID and strFieldName.  Plus then I would have to soemhow combine them all together into one datatable.  I am just trying to pick one distinct category and then combine all the fields that go with that category into one field for the gridview on the screen.  I have many categories in ther with the datafields.  There has got to be soem way to change the following to rotate through the table and add the intcatId and the category and then roll through and all all the datafields to one columns and then switch to the next category and do the same.

  sql = "Select r.intCategoryID, c.strCategory, r.strFieldname from drat_regRelation r INNER JOIN Drat_Category c on c.intCategoryId = r.intCategoryId"

        myDataTable = New DataTable
        myDataTable = getData(sql)

        For Each row As DataRow In myDataTable.Rows
           'Some code if cat = cat then add to field else start new row.
            CatId = row(0)
            cat = row(1)
            Field += row(2) & ", "
        Next
0
 

Author Comment

by:kdeutsch
ID: 37800917
Hi,

Ok so this is what I am trying to accoplish but I get error heaven when I try to do this and no matter how I switch it around I get a ton of errors.

 sql = "Select r.intCategoryID, c.strCategory, r.strFieldname from drat_regRelation r INNER JOIN Drat_Category c on c.intCategoryId = r.intCategoryId order by r.intCategoryId"

        myDataTable = New DataTable
        myDataTable = getData(sql)

        Dim Id As Integer
        For Each row As DataRow In myDataTable.Rows
            Dim ctId As DataColumn = dt.Columns.Add("intCategoryId")
            Dim Category As DataColumn = dt.Columns.Add("Category")
            Dim Fields As DataColumn = dt.Columns.Add("DataFields")

            If CatId = ("intCategoryId") Then
                CatId = row(0)
                cat = row(1)
                Field += row(2) & ", "
            Else
                Field.Remove(Field.Length - 1, 1)
                dt.Rows.Add(CatId, cat, Field)
            End If
        Next

        myRadGrid.DataSource = dt
0
 

Author Comment

by:kdeutsch
ID: 37801013
Hi,
So I got this version to produce no errors however it produces a row for every row in the db and then starts our incriments the field by 1 everytime so it keeps building on the field name.

  Dim Id As Integer
        Dim ctId As DataColumn = dt.Columns.Add("intCategoryId")
        Dim Category As DataColumn = dt.Columns.Add("Category")
        Dim Fields As DataColumn = dt.Columns.Add("DataFields")

        For Each row As DataRow In myDataTable.Rows
            CatId = row(0)
            'Response.Write(CatId & "<br>")

            If CatId = ("intCategoryId") Then
                Response.Write(CatId & "<br>" & "intCategoryId")
                CatId = row(0)
                cat = row(1)
                Field += row(2) & ", "
            Else
                CatId = row(0)
                cat = row(1)
                Field += row(2) & ", "
            End If

            'Field.Remove(Field.Length - 1, 1)
            dt.Rows.Add(CatId, cat, Field)
        Next

        myRadGrid.DataSource = dt
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37801037
>>would have to rewrite the sql statment each time someone added a new category to the table
no you won't, cause you can query the table schema to get all the fields (columns) and then combine them into a single string, everything in a single sql.
in your current approach, you have to change and compile your code on each new data field.

but for now lets stick to your code, so lets start over.
please post all the fields of either Drat_Category table or drat_regRelation table that you wish to combine.

you posted a table above:
CAtId                                                   Datafields
26                     strSn,strComputerName,strCpuName,strCpuCaption,strCpuManufac,
                         fltCpuCores,ftCpuClock
28                      strCpuManufac,ftCpuClocK,strHardDrive, strOSArchitect
29                      ftMemory,strMacHard,strIP,strOs

i can see that different categories has different fields, how come?
which table they are located?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kdeutsch
ID: 37801227
Hi,
Drat-RegRelation is the combined database of drat_Category and the fieldnames of drat_registration.

what I am trying to do is give them the ability to pick a category and the fieldnames that they want to save agianst in drat_Registation dynamically and save into a sepeate db drat_Regrelation that I can then pull onto a aspx page that they can tehn save agianst that db without the hassle of create 10 different pages for every category for fields, they can pick which ones they want, save to relation table.  This stpe is creating the realtion and then showing back into a grid that they can then edit later on if they want o add fields. hence shwoing the grid like this.

CAtId                                                   Datafields
26                     strSn,strComputerName,strCpuName,strCpuCaption,strCpuManufac,
                         fltCpuCores,ftCpuClock
28                      strCpuManufac,ftCpuClocK,strHardDrive, strOSArchitect
29                      ftMemory,strMacHard,strIP,strOs

instead of this this.
intCatId         Database field
28              strCpuManufac
28              ftCpuClock
28              strHardDrive
28              strOSArchitect
26              strSn
26              strComputerName
26              strCpuName
26              strCpuCaption
26              strCpuManufac
26              fltCpuCores
26              ftCpuClock
29              ftMemory
29              strMacHard
29              strIP
29              strOs

It just all combined into one nice row.


This is my latest I get no errors but also nothing show for my category or my fieldnames
 Dim Id As Integer
        Dim ctId As DataColumn = dt.Columns.Add("intCategoryId")
        Dim Category As DataColumn = dt.Columns.Add("Category")
        Dim Fields As DataColumn = dt.Columns.Add("DataFields")

        For Each row As DataRow In myDataTable.Rows
            If CatId = row("intCategoryId") Then
                CatId = row(0)
                cat = row(1)
                Field += row(2) & ", "
                Response.Write(CatId & "<br>" & cat & "<br>" & Field & "<br>")
            Else
                dt.Rows.Add(CatId, cat, Field)
            End If

            'Field.Remove(Field.Length - 1, 1)

        Next
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37801928
so myDataTable contains 2 columns?  CAtId and Datafields?

is this the output of the sql?

CAtId                                                   Datafields
26                     strSn,strComputerName,strCpuName,strCpuCaption,strCpuManufac,
                         fltCpuCores,ftCpuClock
28                      strCpuManufac,ftCpuClocK,strHardDrive, strOSArchitect
29                      ftMemory,strMacHard,strIP,strOs
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37801998
0
 

Accepted Solution

by:
kdeutsch earned 0 total points
ID: 37802118
Hi,
Ok solved it, pain in the arse and i am doing and extra sql call, but this is the best I can do to produce what I want for the follwoing results.

CAtId                                                   Datafields
26                     strSn,strComputerName,strCpuName,strCpuCaption,strCpuManufac,
                         fltCpuCores,ftCpuClock
28                      strCpuManufac,ftCpuClocK,strHardDrive, strOSArchitect
29                      ftMemory,strMacHard,strIP,strOs


Solution
 Dim CatId As Integer
        Dim cat As String
        Dim Field As String
        Dim dt As New DataTable

        sql = "Select r.intCategoryID, c.strCategory from drat_regRelation r INNER JOIN Drat_Category c on c.intCategoryId = r.intCategoryId order by r.intCategoryId"

        myDataTable = New DataTable
        myDataTable = getData(sql)

        Dim Id As Integer
        Dim ctId As DataColumn = dt.Columns.Add("intCategoryId")
        Dim Category As DataColumn = dt.Columns.Add("Category")
        Dim Fields As DataColumn = dt.Columns.Add("DataFields")
        'CatId = 0
        For Each row As DataRow In myDataTable.Rows
            If (row(0) = CatId) Then
                CatId = row(0)
                Field = ""
            Else
                CatId = row(0)
                cat = row(1)

                sql = "Select strFieldName from drat_RegRelation where intCategoryId = " & row(0)

                myDataTable = New DataTable
                myDataTable = getData(sql)

                For Each item As DataRow In myDataTable.Rows
                    Field += item(0) & ","
                Next
                Field.Remove(Field.Length - 1, 1)
                dt.Rows.Add(CatId, cat, Field)
            End If
        Next

        myRadGrid.DataSource = dt
0
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 500 total points
ID: 37804824
sorry couldn't help u better, if u want check this post which describes your requirement exactly.
the solutions provided are pure sql and i personally learned alot from it.
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

cheers
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37806247
10x for the points.
0
 

Author Closing Comment

by:kdeutsch
ID: 37822704
Thanks for the , i bookmarked the site its a greate resource, eventually i might come back to it to change it to a sql solution so i do not have to hit the Db multiple times with my process.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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

21 Experts available now in Live!

Get 1:1 Help Now