asked on

dynamically populating a datatable instance from inside another function? - Syntax question

Hi There,

I have a function for which I am hardcoding some values for column names & headings, I would like to make this more dynamic by making a call to the db before I create the datatable and then populate the column names with the results of the function so the names of the columns are dynamically populate from the query.

At the moment I create a datatable with hardcoded values (see below) and then I have a column returned from the db called "size" and depending on the value of size the actual output is placed in the appropriate column. The problem I have is that there is 70 different size descriptions and if they are not found in the column list then the whole program falls over.

I have tried to write a function at the bottom of this one that querys the database and then I try to make the column names from the result of the query and return it to the other function, but I am having trouble with the correct logic.

Could someone help me out with my code please.

Sean - thanks in advance

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myconnection As New Odbc.OdbcConnection
Try
myconnection = CommonFunctions.ConnDB()
myconnection.Open()
Dim ds As New DataSet
Dim size1 As String
'correct statement

Dim strColourSql As String = "select SUM(stockroom.wip) as wip,stockroom.size, stockroom.style, stockroom.colour, stockroom.wipdue, co.colourdesc from stockroom inner join colours co on stockroom.style = co.style and stockroom.colour = co.colour where stockroom.style ='6310564' group by co.colourdesc,stockroom.size order by colourdesc limit 10"

'lblError.Text = strColourSql

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

myColourDataAdapter.Fill(ds)

dtreturn()

Dim dt As DataTable = New DataTable("try")
dt.Columns.Add("Colour")
dt.Columns.Add("3XS")
dt.Columns.Add("XXS")
dt.Columns.Add("XS")
dt.Columns.Add("S")
dt.Columns.Add("M")
dt.Columns.Add("L")
dt.Columns.Add("XL")
dt.Columns.Add("XXL")
dt.Columns.Add("Count")
dt.Columns.Add("wipdue")

Dim oldColor As String = String.Empty
Dim newColor As String = String.Empty
Dim _RowTotal As Integer
Dim _GrandTotal As Integer
Dim WipDueDate As DateTime

Dim _dr As DataRow

If ds.Tables(0).Rows.Count <> 0 Then

'loop through the amount of rows from the datatable

For Each dr As DataRow In ds.Tables(0).Rows
If Not dr("colourdesc") = oldColor And Not dr("colourdesc") = String.Empty And Not oldColor = String.Empty Then
'padding cells
'make sure the value of zero does not appear in the header row
If _RowTotal = 0 Then
_dr("Count") = String.Empty
dt.Rows.Add(_dr)
Else
_dr("Count") = _RowTotal.ToString()
dt.Rows.Add(_dr)
End If

End If
'if the colourdesc field is not equal to the old colour value the add another row
If Not dr("colourdesc") = oldColor Then
_dr = dt.NewRow()
End If
'set the value of the new colour so that we can check to make sure the colour description is only written
newColor = dr("colourdesc")
'if the newColor value is not equal to the oldColor then add a new colour description into the table
If Not newColor = oldColor Then
_dr("Colour") = dr("colourdesc")

'set the row total to 0 when the new colour description is wirtten for the row,
'otherwise the totals are cumulative
_RowTotal = 0
End If

Select Case dr("size")
Case 1
_dr("3XS") = dr("wip")
Case 2
_dr("XXS") = dr("wip")
Case 3
_dr("XS") = dr("wip")
Case 4
_dr("S") = dr("wip")
Case 5
_dr("M") = dr("wip")
Case 6
_dr("L") = dr("wip")
Case 7
_dr("XL") = dr("wip")
Case 8
_dr("XXL") = dr("wip")
End Select
_dr("wipdue") = dr("wipdue")
_RowTotal = _RowTotal + Int32.Parse(dr("wip").ToString())

_GrandTotal = _GrandTotal + Int32.Parse(dr("wip").ToString())
oldColor = newColor
Next

_dr("Count") = _RowTotal.ToString()
dt.Rows.Add(_dr)
_dr = dt.NewRow()
_dr("Count") = " Total: " + _GrandTotal.ToString()
dt.Rows.Add(_dr)

' _dr("wipdue") = WipDueDate
's dt.Rows.Add(_dr)

Dim r As TableRow = New TableRow
Dim c As TableCell

c = New TableCell
c.Controls.Add(New LiteralControl("Colour"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(200)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("3XS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XXS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("S"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("M"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("L"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XL"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XXL"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl(" "))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("WipDueDate"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
Table1.Rows.Add(r)

For Each dataR As DataRow In dt.Rows
' Dim r As TableRow
' Dim c As TableCell

r = New TableRow
For Each dataC As DataColumn In dt.Columns
c = New TableCell
If Not dataR(dataC.ColumnName) Is DBNull.Value Then
If dataC.ColumnName = "Count" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.HorizontalAlign = HorizontalAlign.Right
c.Controls.Add(New LiteralControl(dataR(dataC.ColumnName)))
Else

If dataC.ColumnName = "wipdue" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.Controls.Add(New LiteralControl(CType(dataR(dataC.ColumnName), DateTime).ToString("MM:dd:yyyy")))
Else
c.Controls.Add(New LiteralControl(dataR(dataC.ColumnName)))
End If

End If
Else

c.Controls.Add(New LiteralControl(String.Empty))

End If
c.VerticalAlign = VerticalAlign.Top
If dataC.ColumnName = "Colour" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.Style("background-color") = "#C0C0C0"
End If
r.Cells.Add(c)
Next
Table1.Rows.Add(r)
Next

Else
lblError.Text = "no results to display"
End If

Catch SQLexc As OdbcException
' lblError.Text = "Database Error : " & DATA_EXCEPTION & ""
lblError.Text = "Database Error : " & SQLexc.ToString() & ""
Catch InvalidCastException As InvalidCastException

lblError.Text = "Invalid Exception : " & InvalidCastException.ToString() & ""
Catch GeneralException As Exception
'lblError.Text = "Invalid Exception : " & GENERAL_ERROR & ""
lblError.Text = "Invalid Exception : " & GeneralException.ToString() & ""
Finally
myconnection.Close()
myconnection.Dispose()
End Try
End Sub

Function dtreturn()

Dim myconnection As New Odbc.OdbcConnection

myconnection = CommonFunctions.ConnDB()
myconnection.Open()
Dim ds As New DataSet
Dim firstcol As String
Dim seccol As String
Dim thircol As String
Dim fortcol As String
Dim fifthcol As String
Dim sixthcol As String
Dim seventcol As String
Dim eigthcol As String
Dim count As Integer = 0

'correct statement

Dim strColourSql As String = "select * from sizes where style ='6310564'"

lblError.Text = strColourSql

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

myColourDataAdapter.Fill(ds)

Dim dt1 As DataTable = New DataTable("try")
For Each dr As DataRow In ds.Tables(0).Rows
count = count + 1

dt1.Columns.Add("Colour")
If count > 1 Then
Select Case count
Case 1
firstcol = dr("sizedesc")
dt1.Columns.Add(firstcol)
Case 2
seccol = dr("sizedesc")
dt1.Columns.Add(seccol)
Case 3
thircol = dr("sizedesc")
dt1.Columns.Add("XS")
Case 4
fortcol = dr("sizedesc")
dt1.Columns.Add(fortcol)
Case 5
fifthcol = dr("sizedesc")
dt1.Columns.Add(fifthcol)
Case 6
sixthcol = dr("sizedesc")
dt1.Columns.Add(sixthcol)
Case 7
seventcol = dr("sizedesc")
dt1.Columns.Add(seventcol)
Case 8
eigthcol = dr("sizedesc")
dt1.Columns.Add(eigthcol)
End Select
End If
dt1.Columns.Add("Count")
dt1.Columns.Add("wipdue")
Next

myconnection.Close()
myconnection.Dispose()
End Function

mmarinov

Hi bullrout,

you can use this a show point:

replace this

Dim dt As DataTable = New DataTable("try")
dt.Columns.Add("Colour")
dt.Columns.Add("3XS")
dt.Columns.Add("XXS")
dt.Columns.Add("XS")
dt.Columns.Add("S")
dt.Columns.Add("M")
dt.Columns.Add("L")
dt.Columns.Add("XL")
dt.Columns.Add("XXL")
dt.Columns.Add("Count")
dt.Columns.Add("wipdue")

with

Dim dt As DataTable = dtreturn().Clone()

and replace
Function dtreturn()
to
Function dtreturn() as DataTable

also you have to use at the end of this function

return dt1

so this will create at first a datatable with the columns that are returned from the dtreturn
in this scenario you can use the same in createing the columns for the table1 object

HTH

Regards!
B..M
mmarinov

bullrout

ASKER

Hi BM,

I made some changes to the code but when the function is called and I add the first column to the datatable " dt1.Columns.Add("Colour")" the program returns an error saying that the table already contains a column named colour. Can you have a look and tell me what I have done wrong?

Sean

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myconnection As New Odbc.OdbcConnection
Try
myconnection = CommonFunctions.ConnDB()
myconnection.Open()
Dim ds As New DataSet
Dim size1 As String
'correct statement

Dim strColourSql As String = "select SUM(stockroom.wip) as wip,stockroom.size, stockroom.style, stockroom.colour, stockroom.wipdue, co.colourdesc from stockroom inner join colours co on stockroom.style = co.style and stockroom.colour = co.colour where stockroom.style ='6310564' group by co.colourdesc,stockroom.size order by colourdesc limit 10"

'lblError.Text = strColourSql

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

myColourDataAdapter.Fill(ds)

Dim dt As DataTable = dtreturn().Clone()

' Dim dt As DataTable = New DataTable("try")
' dt.Columns.Add("Colour")
' dt.Columns.Add("3XS")
' dt.Columns.Add("XXS")
' dt.Columns.Add("XS")
' dt.Columns.Add("S")
' dt.Columns.Add("M")
' dt.Columns.Add("L")
' dt.Columns.Add("XL")
' dt.Columns.Add("XXL")
' dt.Columns.Add("Count")
' dt.Columns.Add("wipdue")

Dim oldColor As String = String.Empty
Dim newColor As String = String.Empty
Dim _RowTotal As Integer
Dim _GrandTotal As Integer
Dim WipDueDate As DateTime

Dim _dr As DataRow

If ds.Tables(0).Rows.Count <> 0 Then

'loop through the amount of rows from the datatable

For Each dr As DataRow In ds.Tables(0).Rows
If Not dr("colourdesc") = oldColor And Not dr("colourdesc") = String.Empty And Not oldColor = String.Empty Then
'padding cells
'make sure the value of zero does not appear in the header row
If _RowTotal = 0 Then
_dr("Count") = String.Empty
dt.Rows.Add(_dr)
Else
_dr("Count") = _RowTotal.ToString()
dt.Rows.Add(_dr)
End If

End If
'if the colourdesc field is not equal to the old colour value the add another row
If Not dr("colourdesc") = oldColor Then
_dr = dt.NewRow()
End If
'set the value of the new colour so that we can check to make sure the colour description is only written
newColor = dr("colourdesc")
'if the newColor value is not equal to the oldColor then add a new colour description into the table
If Not newColor = oldColor Then
_dr("Colour") = dr("colourdesc")

'set the row total to 0 when the new colour description is wirtten for the row,
'otherwise the totals are cumulative
_RowTotal = 0
End If

Select Case dr("size")
Case 1
_dr("3XS") = dr("wip")
Case 2
_dr("XXS") = dr("wip")
Case 3
_dr("XS") = dr("wip")
Case 4
_dr("S") = dr("wip")
Case 5
_dr("M") = dr("wip")
Case 6
_dr("L") = dr("wip")
Case 7
_dr("XL") = dr("wip")
Case 8
_dr("XXL") = dr("wip")
End Select
_dr("wipdue") = dr("wipdue")
_RowTotal = _RowTotal + Int32.Parse(dr("wip").ToString())

_GrandTotal = _GrandTotal + Int32.Parse(dr("wip").ToString())
oldColor = newColor
Next

_dr("Count") = _RowTotal.ToString()
dt.Rows.Add(_dr)
_dr = dt.NewRow()
_dr("Count") = " Total: " + _GrandTotal.ToString()
dt.Rows.Add(_dr)

' _dr("wipdue") = WipDueDate
's dt.Rows.Add(_dr)

Dim r As TableRow = New TableRow
Dim c As TableCell

c = New TableCell
c.Controls.Add(New LiteralControl("Colour"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(200)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("3XS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XXS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("S"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("M"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("L"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XL"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XXL"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl(" "))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("WipDueDate"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
Table1.Rows.Add(r)

For Each dataR As DataRow In dt.Rows
' Dim r As TableRow
' Dim c As TableCell

r = New TableRow
For Each dataC As DataColumn In dt.Columns
c = New TableCell
If Not dataR(dataC.ColumnName) Is DBNull.Value Then
If dataC.ColumnName = "Count" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.HorizontalAlign = HorizontalAlign.Right
c.Controls.Add(New LiteralControl(dataR(dataC.ColumnName)))
Else

If dataC.ColumnName = "wipdue" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.Controls.Add(New LiteralControl(CType(dataR(dataC.ColumnName), DateTime).ToString("MM:dd:yyyy")))
Else
c.Controls.Add(New LiteralControl(dataR(dataC.ColumnName)))
End If

End If
Else

c.Controls.Add(New LiteralControl(String.Empty))

End If
c.VerticalAlign = VerticalAlign.Top
If dataC.ColumnName = "Colour" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.Style("background-color") = "#C0C0C0"
End If
r.Cells.Add(c)
Next
Table1.Rows.Add(r)
Next

Else
lblError.Text = "no results to display"
End If

Catch SQLexc As OdbcException
' lblError.Text = "Database Error : " & DATA_EXCEPTION & ""
lblError.Text = "Database Error : " & SQLexc.ToString() & ""
Catch InvalidCastException As InvalidCastException

lblError.Text = "Invalid Exception : " & InvalidCastException.ToString() & ""
Catch GeneralException As Exception
'lblError.Text = "Invalid Exception : " & GENERAL_ERROR & ""
lblError.Text = "Invalid Exception : " & GeneralException.ToString() & ""
Finally
myconnection.Close()
myconnection.Dispose()
End Try
End Sub

Function dtreturn() As DataTable

Dim myconnection As New Odbc.OdbcConnection

myconnection = CommonFunctions.ConnDB()
myconnection.Open()
Dim ds As New DataSet
Dim firstcol As String
Dim seccol As String
Dim thircol As String
Dim fortcol As String
Dim fifthcol As String
Dim sixthcol As String
Dim seventcol As String
Dim eigthcol As String
Dim count As Integer = 0

'correct statement

Dim strColourSql As String = "select * from sizes where style ='6310564' and sizedesc <> """""

lblError.Text = strColourSql

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

myColourDataAdapter.Fill(ds)

Dim dt1 As DataTable = New DataTable("try")
For Each dr As DataRow In ds.Tables(0).Rows
count = count + 1

dt1.Columns.Add("Colour")

Select Case count
Case 1
firstcol = dr("sizedesc")
dt1.Columns.Add(firstcol)
Case 2
seccol = dr("sizedesc")
dt1.Columns.Add(seccol)
Case 3
thircol = dr("sizedesc")
dt1.Columns.Add(thircol)
Case 4
fortcol = dr("sizedesc")
dt1.Columns.Add(fortcol)
Case 5
fifthcol = dr("sizedesc")
dt1.Columns.Add(fifthcol)
Case 6
sixthcol = dr("sizedesc")
dt1.Columns.Add(sixthcol)
Case 7
seventcol = dr("sizedesc")
dt1.Columns.Add(seventcol)
Case 8
eigthcol = dr("sizedesc")
dt1.Columns.Add(eigthcol)
End Select

dt1.Columns.Add("Count")
dt1.Columns.Add("wipdue")
Next

Return dt1

myconnection.Close()
myconnection.Dispose()
End Function

bullrout

ASKER

HI BM,

Please, if you have sometime to help me with this I would be truly grateful.

Sean

mmarinov

bullrout,
excuse me for the delay
try
this

Function dtreturn() As DataTable

Dim myconnection As New Odbc.OdbcConnection

myconnection = CommonFunctions.ConnDB()
myconnection.Open()
Dim ds As New DataSet
Dim firstcol As String
Dim seccol As String
Dim thircol As String
Dim fortcol As String
Dim fifthcol As String
Dim sixthcol As String
Dim seventcol As String
Dim eigthcol As String
Dim count As Integer = 0

'correct statement

Dim strColourSql As String = "select * from sizes where style ='6310564' and sizedesc <> """""

lblError.Text = strColourSql

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

myColourDataAdapter.Fill(ds)

Dim dt1 As DataTable = New DataTable("try")
dt1.Columns.Add("Colour")
For Each dr As DataRow In ds.Tables(0).Rows
count = count + 1

Select Case count
Case 1
firstcol = dr("sizedesc")
dt1.Columns.Add(firstcol)
Case 2
seccol = dr("sizedesc")
dt1.Columns.Add(seccol)
Case 3
thircol = dr("sizedesc")
dt1.Columns.Add(thircol)
Case 4
fortcol = dr("sizedesc")
dt1.Columns.Add(fortcol)
Case 5
fifthcol = dr("sizedesc")
dt1.Columns.Add(fifthcol)
Case 6
sixthcol = dr("sizedesc")
dt1.Columns.Add(sixthcol)
Case 7
seventcol = dr("sizedesc")
dt1.Columns.Add(seventcol)
Case 8
eigthcol = dr("sizedesc")
dt1.Columns.Add(eigthcol)
End Select

dt1.Columns.Add("Count")
dt1.Columns.Add("wipdue")
Next

Return dt1

myconnection.Close()
myconnection.Dispose()
End Function

B..M
mmarinov

bullrout

ASKER

That's ok, I appreciate the help.

I still get the error:

Invalid Exception : System.Data.DuplicateNameException: A column named 'Count' already belongs to this DataTable. at System.Data.DataColumnCollection.RegisterName(String name, Object obj) at System.Data.DataColumnCollection.BaseAdd(DataColumn column, DataStorage storage) at System.Data.DataColumnCollection.AddAt(Int32 index, DataColumn column) at System.Data.DataColumnCollection.Add(String columnName) at stylebook.WebForm3.dtreturn() in c:\inetpub\wwwroot\stylebook\WebForm3.aspx.vb:line 315 at stylebook.WebForm3.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\stylebook\WebForm3.aspx.vb:line 50

mmarinov

if you have rebuild the code within the snipped that you've posted should not be an error like this
rebuild and check for error
also wich line is 315 ? and which is 50 ?

B..M
mmarinov

bullrout

ASKER

Hi BM,

I have marked the lines where the error is happening, one is when the "count" column is created and the other is when the function for the datatable is called.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myconnection As New Odbc.OdbcConnection
Try
myconnection = CommonFunctions.ConnDB()
myconnection.Open()
Dim ds As New DataSet
Dim size1 As String
'correct statement

Dim strColourSql As String = "select SUM(stockroom.wip) as wip,stockroom.size, stockroom.style, stockroom.colour, stockroom.wipdue, co.colourdesc from stockroom inner join colours co on stockroom.style = co.style and stockroom.colour = co.colour where stockroom.style ='6310564' group by co.colourdesc,stockroom.size order by colourdesc limit 10"

'lblError.Text = strColourSql

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

myColourDataAdapter.Fill(ds)

line 50 Dim dt As DataTable = dtreturn().Clone()

' Dim dt As DataTable = New DataTable("try")
' dt.Columns.Add("Colour")
' dt.Columns.Add("3XS")
' dt.Columns.Add("XXS")
' dt.Columns.Add("XS")
' dt.Columns.Add("S")
' dt.Columns.Add("M")
' dt.Columns.Add("L")
' dt.Columns.Add("XL")
' dt.Columns.Add("XXL")
' dt.Columns.Add("Count")
' dt.Columns.Add("wipdue")

Dim oldColor As String = String.Empty
Dim newColor As String = String.Empty
Dim _RowTotal As Integer
Dim _GrandTotal As Integer
Dim WipDueDate As DateTime

Dim _dr As DataRow

If ds.Tables(0).Rows.Count <> 0 Then

'loop through the amount of rows from the datatable

For Each dr As DataRow In ds.Tables(0).Rows
If Not dr("colourdesc") = oldColor And Not dr("colourdesc") = String.Empty And Not oldColor = String.Empty Then
'padding cells
'make sure the value of zero does not appear in the header row
If _RowTotal = 0 Then
_dr("Count") = String.Empty
dt.Rows.Add(_dr)
Else
_dr("Count") = _RowTotal.ToString()
dt.Rows.Add(_dr)
End If

End If
'if the colourdesc field is not equal to the old colour value the add another row
If Not dr("colourdesc") = oldColor Then
_dr = dt.NewRow()
End If
'set the value of the new colour so that we can check to make sure the colour description is only written
newColor = dr("colourdesc")
'if the newColor value is not equal to the oldColor then add a new colour description into the table
If Not newColor = oldColor Then
_dr("Colour") = dr("colourdesc")

'set the row total to 0 when the new colour description is wirtten for the row,
'otherwise the totals are cumulative
_RowTotal = 0
End If

Select Case dr("size")
Case 1
_dr("3XS") = dr("wip")
Case 2
_dr("XXS") = dr("wip")
Case 3
_dr("XS") = dr("wip")
Case 4
_dr("S") = dr("wip")
Case 5
_dr("M") = dr("wip")
Case 6
_dr("L") = dr("wip")
Case 7
_dr("XL") = dr("wip")
Case 8
_dr("XXL") = dr("wip")
End Select
_dr("wipdue") = dr("wipdue")
_RowTotal = _RowTotal + Int32.Parse(dr("wip").ToString())

_GrandTotal = _GrandTotal + Int32.Parse(dr("wip").ToString())
oldColor = newColor
Next

_dr("Count") = _RowTotal.ToString()
dt.Rows.Add(_dr)
_dr = dt.NewRow()
_dr("Count") = " Total: " + _GrandTotal.ToString()
dt.Rows.Add(_dr)

' _dr("wipdue") = WipDueDate
's dt.Rows.Add(_dr)

Dim r As TableRow = New TableRow
Dim c As TableCell

c = New TableCell
c.Controls.Add(New LiteralControl("Colour"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(200)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("3XS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XXS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XS"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("S"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("M"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(40)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("L"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XL"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("XXL"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl(" "))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
c = New TableCell
c.Controls.Add(New LiteralControl("WipDueDate"))
c.VerticalAlign = VerticalAlign.Top
c.Width = Unit.Pixel(30)
r.Cells.Add(c)
Table1.Rows.Add(r)

For Each dataR As DataRow In dt.Rows
' Dim r As TableRow
' Dim c As TableCell

r = New TableRow
For Each dataC As DataColumn In dt.Columns
c = New TableCell
If Not dataR(dataC.ColumnName) Is DBNull.Value Then
If dataC.ColumnName = "Count" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.HorizontalAlign = HorizontalAlign.Right
c.Controls.Add(New LiteralControl(dataR(dataC.ColumnName)))
Else

If dataC.ColumnName = "wipdue" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.Controls.Add(New LiteralControl(CType(dataR(dataC.ColumnName), DateTime).ToString("MM:dd:yyyy")))
Else
c.Controls.Add(New LiteralControl(dataR(dataC.ColumnName)))
End If

End If
Else

c.Controls.Add(New LiteralControl(String.Empty))

End If
c.VerticalAlign = VerticalAlign.Top
If dataC.ColumnName = "Colour" And Not dataR(dataC.ColumnName) Is DBNull.Value Then
c.Style("background-color") = "#C0C0C0"
End If
r.Cells.Add(c)
Next
Table1.Rows.Add(r)
Next

Else
lblError.Text = "no results to display"
End If

Catch SQLexc As OdbcException
' lblError.Text = "Database Error : " & DATA_EXCEPTION & ""
lblError.Text = "Database Error : " & SQLexc.ToString() & ""
Catch InvalidCastException As InvalidCastException

lblError.Text = "Invalid Exception : " & InvalidCastException.ToString() & ""
Catch GeneralException As Exception
'lblError.Text = "Invalid Exception : " & GENERAL_ERROR & ""
lblError.Text = "Invalid Exception : " & GeneralException.ToString() & ""
Finally
myconnection.Close()
myconnection.Dispose()
End Try
End Sub

Function dtreturn() As DataTable

Dim myconnection As New Odbc.OdbcConnection

myconnection = CommonFunctions.ConnDB()
myconnection.Open()
Dim ds As New DataSet
Dim firstcol As String
Dim seccol As String
Dim thircol As String
Dim fortcol As String
Dim fifthcol As String
Dim sixthcol As String
Dim seventcol As String
Dim eigthcol As String
Dim count As Integer = 0

'correct statement

Dim strColourSql As String = "select * from sizes where style ='6310564' and sizedesc <> """""

lblError.Text = strColourSql

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

myColourDataAdapter.Fill(ds)

Dim dt1 As DataTable = New DataTable("try")
dt1.Columns.Add("Colour")
For Each dr As DataRow In ds.Tables(0).Rows
count = count + 1

Select Case count
Case 1
firstcol = dr("sizedesc")
dt1.Columns.Add(firstcol)
Case 2
seccol = dr("sizedesc")
dt1.Columns.Add(seccol)
Case 3
thircol = dr("sizedesc")
dt1.Columns.Add(thircol)
Case 4
fortcol = dr("sizedesc")
dt1.Columns.Add(fortcol)
Case 5
fifthcol = dr("sizedesc")
dt1.Columns.Add(fifthcol)
Case 6
sixthcol = dr("sizedesc")
dt1.Columns.Add(sixthcol)
Case 7
seventcol = dr("sizedesc")
dt1.Columns.Add(seventcol)
Case 8
eigthcol = dr("sizedesc")
dt1.Columns.Add(eigthcol)
End Select

line 315 dt1.Columns.Add("Count")
dt1.Columns.Add("wipdue")
Next

Return dt1

myconnection.Close()
myconnection.Dispose()
End Function

ASKER CERTIFIED SOLUTION

mmarinov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial