Solved

Seperate categories from tablle into seperate gridview rows

Posted on 2012-04-02
14
340 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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