bullrout
asked on
nested loops inside a datareader - syntax help please
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(myDataReade
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
r.Cells.Add(c)
Do While myDataReader.Item("qoh")
c = New TableCell
c.Controls.Add(New LiteralControl(myDataReade
c.VerticalAlign = VerticalAlign.Top
r.Cells.Add(c)
Loop
Table2.Rows.Add(r)
Loop
myDataReader.Close()
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(myDataReade
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
r.Cells.Add(c)
Do While myDataReader.Item("qoh")
c = New TableCell
c.Controls.Add(New LiteralControl(myDataReade
c.VerticalAlign = VerticalAlign.Top
r.Cells.Add(c)
Loop
Table2.Rows.Add(r)
Loop
myDataReader.Close()
ASKER
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.si
r = New TableRow
c = New TableCell
Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strCo
myColourDataAdapter.Fill(d
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
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.si
r = New TableRow
c = New TableCell
Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strCo
myColourDataAdapter.Fill(d
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
ASKER
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
ASKER
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
I tried the code and it only seems to populate the first column when it's looping, any ideas?
colourdesc
colourdesc
colourdesc
colourdesc
colourdesc
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
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("colourd
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color"
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
ASKER
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 |
+------------+------+-----
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 |
+------------+------+-----
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("colourd
c.Style("background-color"
else
c.Controls.Add(New LiteralControl(dr("qoh")))
end if
c.VerticalAlign = VerticalAlign.Top
r.Cells.Add(c)
oldColor = newColor
Next
Regards,
B..M
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("colourd
c.Style("background-color"
else
c.Controls.Add(New LiteralControl(dr("qoh")))
end if
c.VerticalAlign = VerticalAlign.Top
r.Cells.Add(c)
oldColor = newColor
Next
Regards,
B..M
ASKER
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;
<tr>
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr>
</table>
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;
<tr>
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr>
</table>
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
dr("s7days")
to
dr("qoh")
because in your output there were no s7days column
Regards,
B..M
ASKER
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.si
r = New TableRow
c = New TableCell
Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strCo
myColourDataAdapter.Fill(d
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("colourd
c.Style("background-color"
Else
c.Controls.Add(New LiteralControl(dr("qoh")))
End If
c.VerticalAlign = VerticalAlign.Top
r.Cells.Add(c)
oldColor = newColor
Next
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.si
r = New TableRow
c = New TableCell
Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strCo
myColourDataAdapter.Fill(d
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("colourd
c.Style("background-color"
Else
c.Controls.Add(New LiteralControl(dr("qoh")))
End If
c.VerticalAlign = VerticalAlign.Top
r.Cells.Add(c)
oldColor = newColor
Next
ASKER
Hi BM,
I appreciate the help, is there another way this can be done? I'm desperate to get this working.
I appreciate the help, is there another way this can be done? I'm desperate to get this working.
try to change
c.Controls.Add(New LiteralControl(dr("qoh")))
with
c.Controls.Add(New LiteralControl(dr("qoh").T
Regards,
B..M
c.Controls.Add(New LiteralControl(dr("qoh")))
with
c.Controls.Add(New LiteralControl(dr("qoh").T
Regards,
B..M
ASKER
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.
ASKER
Hey BM,
You still with me?
You still with me?
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
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
ASKER
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.
I am not getting any values at all only zeros, even if the column has a value it is not shown at all.
ASKER
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?
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?
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.si
r = New TableRow
c = New TableCell
Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strCo
myColourDataAdapter.Fill(d
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("colourd
c.Style("background-color"
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
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.si
r = New TableRow
c = New TableCell
Dim myColourDataAdapter As New Odbc.OdbcDataAdapter(strCo
myColourDataAdapter.Fill(d
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("colourd
c.Style("background-color"
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
ASKER
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;
<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:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr>
</table>
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;
<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:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr><tr>
<td valign="Top" style="background-color:li
</tr>
</table>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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
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
ASKER
Kewl, I will give it a try.
Thanks BM, you have been really helpful.
Sean
Thanks BM, you have been really helpful.
Sean
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