Solved

nested loops inside a datareader - syntax help please

Posted on 2004-10-06
24
378 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
 
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
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

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 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

16 Experts available now in Live!

Get 1:1 Help Now