• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 760
  • Last Modified:

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") = "&nbsp;Total:&nbsp;" + _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("&nbsp;"))
                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
0
bullrout
Asked:
bullrout
  • 4
  • 4
1 Solution
 
mmarinovCommented:
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
0
 
bullroutAuthor Commented:
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") = "&nbsp;Total:&nbsp;" + _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("&nbsp;"))
                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
0
 
bullroutAuthor Commented:
HI BM,

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

Sean
0
Technology Partners: We Want Your Opinion!

We value your feedback.

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

 
mmarinovCommented:
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
0
 
bullroutAuthor Commented:
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

0
 
mmarinovCommented:
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
0
 
bullroutAuthor Commented:
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") = "&nbsp;Total:&nbsp;" + _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("&nbsp;"))
                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
0
 
mmarinovCommented:
i didn't notice that is a different column name
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

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

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

B..M
mmarinov
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now