Solved

nested loops inside a datareader - syntax help please

Posted on 2004-10-06
24
380 Views
Last Modified: 2012-05-05
Hi There,

I need to have an inner loop for my datareader, I have tried to reference the field by name but with no luck. Could someone help me out with the syntax please?

Sean


 Dim r As TableRow
        Dim c As TableCell
        Dim i As Integer
        Dim myconnection As New Odbc.OdbcConnection

        myconnection = CommonFunctions.ConnDB()
        myconnection.Open()


        Dim dtTable As New DataTable

        r = New TableRow
        c = New TableCell

        Dim myDataReader As Odbc.OdbcDataReader

        myDataReader = cmd.ExecuteReader()
        Do While (myDataReader.Read())

            r = New TableRow
            c = New TableCell
            c.Controls.Add(New _
             LiteralControl(myDataReader("colourdesc")))
            c.VerticalAlign = VerticalAlign.Top
            c.Style("background-color") = "lightblue"
            r.Cells.Add(c)

            Do While myDataReader.Item("qoh")


                c = New TableCell
                c.Controls.Add(New LiteralControl(myDataReader("qoh")))
                c.VerticalAlign = VerticalAlign.Top
                r.Cells.Add(c)
            Loop

            Table2.Rows.Add(r)
 
        Loop
        myDataReader.Close()
0
Comment
Question by:bullrout
  • 14
  • 10
24 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 12235522
Hi,

i would suggest you to use dataset - it will be much more easy

dim adapter as Odbc.OdbcDataAdapter()
adapter.SelectCommand = cmd
dim ds as DataSet

adapter.Fill ( ds )

the you can try

dim dr as DataRow
dim dc as DataColumn

for each ( dr in ds.Tables(0).Rows )
    for each ( dc in ds.Tables(0).Columns )
        if  dc.ColumnName = "qoh" Then
        end if
    Next
next

Regards,
B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12235777
Hi BM,

Can you give me a little help with this please? I am trying to return the results like below, I can get the first and the first column of the qoh but then it just duplicates the values.

Please help this is driving me nuts!

I am trying to get

colourdesc qoh |qoh |qoh |qoh |
-----------
colourdesc qoh | qoh | qoh | qoh |
-----------
colourdesc qoh | qoh | qoh | qoh |

I'm actually getting

colourdesc qoh
------------
colourdesc
------------
colourdesc
------------
colourdesc
------------



  Dim r As TableRow
        Dim c As TableCell

        Dim myconnection As New Odbc.OdbcConnection

        myconnection = CommonFunctions.ConnDB()
        myconnection.Open()

        Dim ds As New DataSet
        Dim strColourSql As String = "select shopsales.qoh,shopsales.size, shopsales.shop, shopsales.style, shopsales.colour, co.colourdesc from shopsales inner Join shops on shopsales.shop = shops.shop inner join colours co on shopsales.style  = co.style and shopsales.colour = co.colour where shopsales.shop =1 and shopsales.style = '631733A' group by co.colourdesc"

        r = New TableRow
        c = New TableCell

        Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strColourSql, myconnection)

        myColourDataAdapter.Fill(ds)

        Dim dr As DataRow
        Dim dc As DataColumn

        For Each dr In ds.Tables(0).Rows
            For Each dc In ds.Tables(0).Columns

                ' If dc.ColumnName = "colourdesc" Then

                r = New TableRow
                c = New TableCell
                c.Controls.Add(New _
                 LiteralControl(dr("colourdesc")))
                c.VerticalAlign = VerticalAlign.Top
                c.Style("background-color") = "lightblue"
                r.Cells.Add(c)

                If dc.ColumnName = "qoh" Then
                    c = New TableCell
                    c.Controls.Add(New LiteralControl(dr("qoh")))
                    c.VerticalAlign = VerticalAlign.Top
                    r.Cells.Add(c)

                End If
                ' End If
                Table2.Rows.Add(r)
            Next
        Next
0
 
LVL 5

Author Comment

by:bullrout
ID: 12236461
Hi BM,

I have got it displaying extactly the way I want, it's just the inner loop is displaying the value of the columns duplicated. Could you please have a look at this for me?

I'll give you extra points if I have to, I really need the help on this one.

  For Each dr In ds.Tables(0).Rows
                For Each dc In ds.Tables(0).Columns

                    If dc.ColumnName = "colourdesc" Then

                        r = New TableRow
                        c = New TableCell
                        c.Controls.Add(New _
                         LiteralControl(dr("colourdesc")))
                        c.VerticalAlign = VerticalAlign.Top
                        c.Style("background-color") = "lightblue"
                        r.Cells.Add(c)
                        For Each dc1 In ds.Tables(0).Columns
                            c = New TableCell
                            c.Controls.Add(New LiteralControl(dr("s7days")))
                            c.VerticalAlign = VerticalAlign.Top
                            r.Cells.Add(c)
                        Next
                    End If
                    Table1.Rows.Add(r)
                Next
            Next
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 28

Expert Comment

by:mmarinov
ID: 12236552
you have to add an additional if statement like this
  For Each dr In ds.Tables(0).Rows
                For Each dc In ds.Tables(0).Columns

                    If dc.ColumnName = "colourdesc" Then

                        r = New TableRow
                        c = New TableCell
                        c.Controls.Add(New _
                         LiteralControl(dr("colourdesc")))
                        c.VerticalAlign = VerticalAlign.Top
                        c.Style("background-color") = "lightblue"
                        r.Cells.Add(c)
                        For Each dc1 In ds.Tables(0).Columns
                             If Not dc.ColumnName = "colourdesc" Then
                                  c = New TableCell
                                  c.Controls.Add(New LiteralControl(dr("s7days")))
                                  c.VerticalAlign = VerticalAlign.Top
                                  r.Cells.Add(c)
                              End If
                        Next
                    End If
                    Table1.Rows.Add(r)
                Next
            Next
HTH
B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12236611
HI BM,

I tried the code and it only seems to populate the first column when it's looping, any ideas?

colourdesc
colourdesc
colourdesc
colourdesc
colourdesc
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12236678
can you please give me the column names and way you wanted to be shown
because the change that i've made is to not displaing the columns that you have already added

another way is try this code

For Each dr In ds.Tables(0).Rows
                For Each dc In ds.Tables(0).Columns

                    If dc.ColumnName = "colourdesc" Then

                        r = New TableRow
                        c = New TableCell
                        c.Controls.Add(New _
                         LiteralControl(dr("colourdesc")))
                        c.VerticalAlign = VerticalAlign.Top
                        c.Style("background-color") = "lightblue"
                        r.Cells.Add(c)
                        For Each dc1 In ds.Tables(0).Columns
                             If Not dc=dc1 Then
                                  c = New TableCell
                                  c.Controls.Add(New LiteralControl(dr("s7days")))
                                  c.VerticalAlign = VerticalAlign.Top
                                  r.Cells.Add(c)
                              End If
                        Next
                    End If
                    Table1.Rows.Add(r)
                Next
            Next

if your table contains only columns with names colourdesc than you have to determine which column to be displayed

in your first posting you've posted somthing about .Item("qoh") - do you mean that "qoh" is the name of the column, or it is the value of the current cell ?

B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12236751
Hi BM,

My query returns 6 columns as a result of an inner join, so I can get the description. I only need to display the colourdesc & qoh. The problem is that it's duplicating the rows for colourdesc because each colour has multiple sizes.

Sorry for the confusion, it's hard to articulate this in text.

my desired result

BLACK  | qoh | qoh | qoh | qoh
WHITE  | qoh | qoh | qoh | qoh

results of my query
+------------+------+------+---------+--------+------------+
| qoh        | size | shop | style   | colour | colourdesc |
+------------+------+------+---------+--------+------------+
| 0000000002 |    2 |    1 | 631733A |      1 | BLACK      |
| 0000000000 |    3 |    1 | 631733A |      1 | BLACK      |
| 0000000000 |    4 |    1 | 631733A |      1 | BLACK      |
| 0000000000 |    5 |    1 | 631733A |      1 | BLACK      |
| 0000000000 |    6 |    1 | 631733A |      1 | BLACK      |
| 0000000000 |    7 |    1 | 631733A |      1 | BLACK      |
| 0000000002 |    2 |    1 | 631733A |      2 | WHITE      |
| 0000000000 |    3 |    1 | 631733A |      2 | WHITE      |
| 0000000000 |    4 |    1 | 631733A |      2 | WHITE      |
| 0000000000 |    5 |    1 | 631733A |      2 | WHITE      |
| 0000000000 |    6 |    1 | 631733A |      2 | WHITE      |
| 0000000000 |    3 |    1 | 631733A |     29 | BEYONCE    |
| 0000000000 |    4 |    1 | 631733A |     29 | BEYONCE    |
| 0000000000 |    5 |    1 | 631733A |     29 | BEYONCE    |
| 0000000000 |    6 |    1 | 631733A |     29 | BEYONCE    |
| 0000000000 |    4 |    1 | 631733A |     32 | DREW       |
| 0000000000 |    5 |    1 | 631733A |     32 | DREW       |
| 0000000000 |    6 |    1 | 631733A |     32 | DREW       |
| 0000000000 |    2 |    1 | 631733A |     33 | CAMERON    |
| 0000000000 |    3 |    1 | 631733A |     33 | CAMERON    |
| 0000000000 |    4 |    1 | 631733A |     33 | CAMERON    |
| 0000000000 |    5 |    1 | 631733A |     33 | CAMERON    |
| 0000000000 |    6 |    1 | 631733A |     33 | CAMERON    |
| 0000000000 |    5 |    1 | 631733A |     43 | NAOMI      |
| 0000000000 |    3 |    1 | 631733A |     44 | LIV        |
| 0000000000 |    4 |    1 | 631733A |     44 | LIV        |
| 0000000000 |    5 |    1 | 631733A |     44 | LIV        |
| 0000000000 |    2 |    1 | 631733A |     45 | PARIS      |
| 0000000000 |    3 |    1 | 631733A |     45 | PARIS      |
| 0000000000 |    5 |    1 | 631733A |     45 | PARIS      |
| 0000000000 |    6 |    1 | 631733A |     45 | PARIS      |
| 0000000000 |    3 |    1 | 631733A |     46 | PINK       |
| 0000000000 |    4 |    1 | 631733A |     46 | PINK       |
| 0000000000 |    5 |    1 | 631733A |     46 | PINK       |
| 0000000000 |    6 |    1 | 631733A |     46 | PINK       |
| 0000000000 |    4 |    1 | 631733A |     50 | SALMA      |
| 0000000000 |    6 |    1 | 631733A |     51 | SOPHIE     |
| 0000000000 |    2 |    1 | 631733A |     52 | SARAH      |
| 0000000000 |    3 |    1 | 631733A |     52 | SARAH      |
| 0000000000 |    4 |    1 | 631733A |     52 | SARAH      |
| 0000000000 |    5 |    1 | 631733A |     52 | SARAH      |
| 0000000000 |    6 |    1 | 631733A |     52 | SARAH      |
| 0000000000 |    2 |    1 | 631733A |     54 | JESSICA    |
| 0000000000 |    5 |    1 | 631733A |     54 | JESSICA    |
| 0000000000 |    6 |    1 | 631733A |     54 | JESSICA    |
| 0000000000 |    2 |    1 | 631733A |     55 | MARY KATE  |
| 0000000000 |    2 |    1 | 631733A |     91 | APPLE      |
| 0000000000 |    3 |    1 | 631733A |     91 | APPLE      |
| 0000000000 |    4 |    1 | 631733A |     91 | APPLE      |
| 0000000000 |    5 |    1 | 631733A |     91 | APPLE      |
| 0000000000 |    6 |    1 | 631733A |     91 | APPLE      |
+------------+------+------+---------+--------+------------+
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12236919
ok, this is totally different
try this:

Dim oldColor as String = String.Empty
Dim newColor as String = String.Empty

For Each dr In ds.Tables(0).Rows
    If Not dr("colourdesc") = oldColor And Not dr("colourdesc") = String.Empty Then
        Table1.Rows.Add(r)
    End If

    If Not dr("colourdesc") = oldColor Then
        r = New TableRow
    End If

    newColor = dr("colourdesc")

    c = New TableCell
    if Not newColor=oldColor Then
        c.Controls.Add(New _
            LiteralControl(dr("colourdesc")))
        c.Style("background-color") = "lightblue"
    else
        c.Controls.Add(New LiteralControl(dr("qoh")))
    end if

    c.VerticalAlign = VerticalAlign.Top
    r.Cells.Add(c)
    oldColor = newColor
Next

Regards,
B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12237002
Hi BM,

I have the output, it gave me some rows with no values at all.

<table id="Table2" cellspacing="0" cellpadding="3" rules="all" bordercolor="Black" border="1" style="border-color:Black;border-width:1px;border-style:solid;font-family:Verdana;font-size:8pt;border-collapse:collapse;Z-INDEX: 200; LEFT: 20px; POSITION: absolute; TOP: 50px">
      <tr>

      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">BLACK</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">WHITE</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">BEYONCE</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">DREW</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">CAMERON</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">NAOMI</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">LIV</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">PARIS</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">PINK</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">SALMA</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">SOPHIE</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">SARAH</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">JESSICA</td><td valign="Top">0</td><td valign="Top">0</td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">MARY KATE</td>
      </tr>
</table>
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12237142
may be it is because i have change

dr("s7days")
to
dr("qoh")

because in your output there were no s7days column

Regards,
B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12237227
Hi BM,

I did try that and I got the results above, I know it's close.

   Dim r As TableRow
        Dim c As TableCell

        Dim myconnection As New Odbc.OdbcConnection

        myconnection = CommonFunctions.ConnDB()
        myconnection.Open()

        Dim ds As New DataSet
        Dim strColourSql As String = "select shopsales.qoh,shopsales.size, shopsales.shop, shopsales.style, shopsales.colour, co.colourdesc from shopsales inner Join shops on shopsales.shop = shops.shop inner join colours co on shopsales.style  = co.style and shopsales.colour = co.colour where shopsales.shop =1 and shopsales.style = '631733A'"

        r = New TableRow
        c = New TableCell

        Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strColourSql, myconnection)

        myColourDataAdapter.Fill(ds)

        Dim dr As DataRow
        Dim dc As DataColumn
        Dim dc1 As DataColumn

        Dim oldColor As String = String.Empty
        Dim newColor As String = String.Empty

        For Each dr In ds.Tables(0).Rows
            If Not dr("colourdesc") = oldColor And Not dr("colourdesc") = String.Empty Then
                Table2.Rows.Add(r)
            End If

            If Not dr("colourdesc") = oldColor Then
                r = New TableRow
            End If

            newColor = dr("colourdesc")

            c = New TableCell
            If Not newColor = oldColor Then
                c.Controls.Add(New _
                    LiteralControl(dr("colourdesc")))
                c.Style("background-color") = "lightblue"
            Else
                c.Controls.Add(New LiteralControl(dr("qoh")))
            End If

            c.VerticalAlign = VerticalAlign.Top
            r.Cells.Add(c)
            oldColor = newColor
        Next

0
 
LVL 5

Author Comment

by:bullrout
ID: 12237375
Hi BM,

I appreciate the help, is there another way this can be done? I'm desperate to get this working.
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12237577
try to change
  c.Controls.Add(New LiteralControl(dr("qoh")))

with
  c.Controls.Add(New LiteralControl(dr("qoh").ToString()))

Regards,
B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12237847
I did try that, its returning the values but it seems to be skiping some of the rows so instead of each colour having 5 qoh som of them have 2,3, none.
0
 
LVL 5

Author Comment

by:bullrout
ID: 12238044
Hey BM,

You still with me?
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12238416
yes, it is like that, because you don't have 5 values for every color

if you want to do it like this you will have to use a counter and before add the table row to the table you have to fill the rest of the columns with empty strings and you will receive 5 columns for every color

B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12242698
Hi BM,

I am not getting any values at all only zeros, even if the column has a value it is not shown at all.

0
 
LVL 5

Author Comment

by:bullrout
ID: 12245840
Hi BM,

Can I use the rowcount property for a dataset to get the extra column? There is only one coloum missing in the list.

Any ideas?
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12245851
i've seen an error in the code i've posted that is related with skiping the first value of qoh, but all other values are fileed with 0
so here is the corrected code

   Dim r As TableRow
        Dim c As TableCell

        Dim myconnection As New Odbc.OdbcConnection

        myconnection = CommonFunctions.ConnDB()
        myconnection.Open()

        Dim ds As New DataSet
        Dim strColourSql As String = "select shopsales.qoh,shopsales.size, shopsales.shop, shopsales.style, shopsales.colour, co.colourdesc from shopsales inner Join shops on shopsales.shop = shops.shop inner join colours co on shopsales.style  = co.style and shopsales.colour = co.colour where shopsales.shop =1 and shopsales.style = '631733A'"

        r = New TableRow
        c = New TableCell

        Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strColourSql, myconnection)

        myColourDataAdapter.Fill(ds)

        Dim dr As DataRow
        Dim dc As DataColumn
        Dim dc1 As DataColumn

        Dim oldColor As String = String.Empty
        Dim newColor As String = String.Empty
        Dim columns_count as Integer = 0

        For Each dr In ds.Tables(0).Rows
            If Not dr("colourdesc") = oldColor And Not dr("colourdesc") = String.Empty Then
                If columns_count < 5 Then
                   Dim count as Integer
                   For count = columns_count To 5
                      c = New TableCell
                      c.Controls.Add(New LiteralControl(""))
                      c.VerticalAlign = VerticalAlign.Top
                      r.Cells.Add(c)
                   Next
                End If
                Table2.Rows.Add(r)
            End If

            If Not dr("colourdesc") = oldColor Then
                r = New TableRow
            End If

            newColor = dr("colourdesc")

            c = New TableCell
            If Not newColor = oldColor Then
                c.Controls.Add(New _
                    LiteralControl(dr("colourdesc")))
                c.Style("background-color") = "lightblue"
                c.VerticalAlign = VerticalAlign.Top
                r.Cells.Add(c)
                c.Controls.Add(New LiteralControl(dr("qoh")))
            Else
                c.Controls.Add(New LiteralControl(dr("qoh")))
            End If

            c.VerticalAlign = VerticalAlign.Top
            r.Cells.Add(c)
            oldColor = newColor
        Next

I'm posting and the correction for that every color will have 5 columns

B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12245945
HI BM,

The coloums are doubled up with the first one sharing the data (see below). I 'm lost, I wanted to try to do this in one go but I'm wondering if it is too complex to get the structure needed, what do you think?

<table id="Table2" cellspacing="0" cellpadding="4" rules="all" bordercolor="Black" border="1" style="border-color:Black;border-width:1px;border-style:solid;font-family:Verdana;font-size:8pt;border-collapse:collapse;Z-INDEX: 108; LEFT: 16px; POSITION: absolute; TOP: 80px">
      <tr>
            <td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">BLACK3</td><td valign="Top">8</td><td valign="Top">11</td><td valign="Top">5</td><td valign="Top">3</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">WHITE0</td><td valign="Top">6</td><td valign="Top">7</td><td valign="Top">0</td><td valign="Top">2</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">BEYONCE1</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">BRITNEY0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">DREW0</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">CAMERON0</td><td valign="Top">2</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">GWYNETH0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">JENNIFER0</td><td valign="Top">2</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">1</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">JEWEL1</td><td valign="Top">1</td><td valign="Top">2</td><td valign="Top">2</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">JULIA0</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">NAOMI1</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top">3</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">LIV0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">PARIS1</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">PINK1</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">2</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">SALMA0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">SOPHIE0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">SARAH1</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">ASHLEY0</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">JESSICA0</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">MARY KATE0</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr><tr>
            <td valign="Top" style="background-color:lightblue;">APPLE0</td><td valign="Top">3</td><td valign="Top">0</td><td valign="Top">1</td><td valign="Top">0</td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td><td valign="Top"></td>
      </tr>
</table>
0
 
LVL 28

Accepted Solution

by:
mmarinov earned 500 total points
ID: 12245973
Dammm,

it is my fault bullrout, because i can not tested the code that i've posted :(
first about the merging the color name and first value is corrected within the code
about the adding extra columns - i didn't increase the counter

here is the corrected code

 Dim r As TableRow
        Dim c As TableCell

        Dim myconnection As New Odbc.OdbcConnection

        myconnection = CommonFunctions.ConnDB()
        myconnection.Open()

        Dim ds As New DataSet
        Dim strColourSql As String = "select shopsales.qoh,shopsales.size, shopsales.shop, shopsales.style, shopsales.colour, co.colourdesc from shopsales inner Join shops on shopsales.shop = shops.shop inner join colours co on shopsales.style  = co.style and shopsales.colour = co.colour where shopsales.shop =1 and shopsales.style = '631733A'"

        r = New TableRow
        c = New TableCell

        Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strColourSql, myconnection)

        myColourDataAdapter.Fill(ds)

        Dim dr As DataRow
        Dim dc As DataColumn
        Dim dc1 As DataColumn

        Dim oldColor As String = String.Empty
        Dim newColor As String = String.Empty
        Dim columns_count as Integer = 0

        For Each dr In ds.Tables(0).Rows
            If Not dr("colourdesc") = oldColor And Not dr("colourdesc") = String.Empty Then
                If columns_count < 5 Then
                   Dim count as Integer
                   For count = columns_count To 4
                      c = New TableCell
                      c.Controls.Add(New LiteralControl(""))
                      c.VerticalAlign = VerticalAlign.Top
                      r.Cells.Add(c)
                   Next
                End If
                Table2.Rows.Add(r)
            End If

            If Not dr("colourdesc") = oldColor Then
                r = New TableRow
            End If

            newColor = dr("colourdesc")

            c = New TableCell
            If Not newColor = oldColor Then
                c.Controls.Add(New _
                    LiteralControl(dr("colourdesc")))
                c.Style("background-color") = "lightblue"
                c.VerticalAlign = VerticalAlign.Top
                r.Cells.Add(c)
                c = New TableCell
                c.Controls.Add(New LiteralControl(dr("qoh")))
            Else
                c.Controls.Add(New LiteralControl(dr("qoh")))
            End If

            c.VerticalAlign = VerticalAlign.Top
            r.Cells.Add(c)
            oldColor = newColor
            columns_count = columns_count  + 1
        Next


Please excuse me for the uncorrect codes
B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12246063
That's fine, I appreciate you helping me on this, without your help I probably would have a meltdown! It seems to be working now, thank you so much!


Can I ask one question though, is there a way I can make it dynamically adjust to the columns returned? say for instance I have more than 5 can I set the coloum count to 10 for example and it will display all of them? or can I set it to the column count returned from the dataset?

0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12246107
i've put column_count=5 because there only five in the dataset that you've posted
it is no problem to do it dynamically, BUT you have to create a method like this:

Public Function GetCountOfColumns ( ByVal column_name as String, ByVal ds as DataSet ) as Integer
Dim dc as DataColumn
Dim column_count as Integer = 0
For Each dc in ds.Tables(0).Columns
 If dc.ColumnName = column_name Then
   column_count = column_count + 1
 End If
Next

GetCountOfColumns = column_count
End Function

to get the number of columns_count so you can do it ok
and call this function from the previous code to get the column_count
try it

B..M
0
 
LVL 5

Author Comment

by:bullrout
ID: 12246178
Kewl, I will give it a try.

Thanks BM, you have been really helpful.

Sean
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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