Solved

Seperate categories from tablle into seperate gridview rows

Posted on 2012-04-02
14
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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
 

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
 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

696 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