Solved

build table programatically vb

Posted on 2009-03-30
5
215 Views
Last Modified: 2013-11-26
 ''yes I never really build a table programatically before only by databinding and using a stored procedure to
            ' manipulate data. I am having trouble with this one even getting my data to display.
Private Sub ShoppingCart2()

        Dim CartID As Integer
 

        Dim conn As New SqlConnection(Application("ConnString"))

        Dim cmd As SqlCommand

        Dim prm As SqlParameter

        cmd = New SqlCommand("GetShoppingCartProducts", conn)
 
 

        '-----------------------------------------------

        prm = New SqlParameter("@CartID", SqlDbType.Char, 36)

        prm.Direction = ParameterDirection.Input

        prm.Value = CartID

        cmd.Parameters.Add(prm)
 

        cmd.CommandType = CommandType.StoredProcedure

        cmd.CommandTimeout = 120 'Number of seconds to wait for a response.
 

        Dim sqlDR As SqlDataReader

        conn.Open()

        sqlDR = cmd.ExecuteReader(CommandBehavior.Default)
 

        
 

        If Not sqlDR.NextResult Then

            Throw New ApplicationException("Missing Data!")

        End If
 

        '' hWeek = 0

        Dim c As TableCell

        Dim r As TableRow

        ''  widx = -1
 

        While sqlDR.Read
 

            c.HorizontalAlign = HorizontalAlign.Center

            r.Cells.Add(c)

            Table1.Rows.Add(r)
 

            r = New TableRow

            r.CssClass = "HeaderCell"

            r.Style.Add("background", "ccffcc")

            r.Style.Add("color", "Black")
 
 

            c = New TableCell

            c.Text = "UPC<br>code"

            r.Cells.Add(c)
 

            c = New TableCell

            c.Text = "Product name"

            r.Cells.Add(c)
 

            c = New TableCell

            c.Text = "Quantity"

            r.Cells.Add(c)
 

            c = New TableCell

            c.Text = "SubTotal"

            r.Cells.Add(c)
 

            c = New TableCell

            c.Text = "Case"

            r.Cells.Add(c)
 

            c = New TableCell

            c.Text = "Case<br>Cost"

            r.Cells.Add(c)
 
 

            Table1.Rows.Add(r)

           
 

        End While
 

        'Clean Up

        If (Not sqlDR Is Nothing) Then

            If (sqlDR.IsClosed) Then

                sqlDR.Close()

            End If

        End If

        If (Not conn Is Nothing) Then

            If conn.State <> ConnectionState.Closed Then conn.Close()

            conn.Dispose()

        End If

        If Not cmd Is Nothing Then cmd.Dispose()
 

    End Sub

Open in new window

0
Comment
Question by:sevensnake77
  • 4
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 24019698
try this:

dim row as DataRow
while sqlDr.Read()
  'get table 1 row definition
 row = Table1.newrow()
 'assign values from DR into Row
 row("Subtotal") = sqlDr("SubTotal")
 'append for other columns
'add row to table
 Table1.Rows.Add(row)
end while

You can set the row headers in the table1 html declaration.  You never assign the data from the datareader to the row before you add it, which is why you're not getting any values.

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 24020043
I am getting an error saying row is not part of   System.Web.UI.WebControls.Table
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 24020101
I tried like this but no data shows up

Dim c As TableCell

        Dim r As TableRow

        ''  widx = -1
 

        '### New row
 

        Dim row As DataRow

        While sqlDR.Read()

            c = New TableCell

            c.Text = row("Subtotal") = sqlDR("SubTotal")

            r.Cells.Add(c)

            Table1.Rows.Add(r)
 

       
 
 

        End While

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 24020482
lets start all over, I this part I run the stored procedure and try to execute the reader.  If I can get it to a point where I can see my data I can take it from there.

Private Sub shoppingCart2()
 

        Dim CartID As Integer
 

        Dim conn As New SqlConnection(Application("ConnString"))

        Dim cmd As SqlCommand

        Dim prm As SqlParameter

        cmd = New SqlCommand("GetShoppingCartProducts", conn)
 
 

        '-----------------------------------------------

        prm = New SqlParameter("@CartID", SqlDbType.Char, 36)

        prm.Direction = ParameterDirection.Input

        prm.Value = " "

        cmd.Parameters.Add(prm)
 

        cmd.CommandType = CommandType.StoredProcedure

        ' cmd.CommandTimeout = 120 'Number of seconds to wait for a response.

        Dim row As DataRow

        Dim sqlDR As SqlDataReader

        conn.Open()

        sqlDR = cmd.ExecuteReader(CommandBehavior.Default)

        While sqlDR.Read()

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 24022338
Ok I got the header to show up but cannot get the values or data to show any suggestions

Dim cmd As SqlCommand

        Dim prm As SqlParameter

        cmd = New SqlCommand("GetShoppingCartProducts", conn)
 

        Dim c As TableCell

        Dim r As TableRow

        '-----------------------------------------------

        prm = New SqlParameter("@CartID", SqlDbType.Char, 36)

        prm.Direction = ParameterDirection.Input

        prm.Value = CartID

        cmd.Parameters.Add(prm)
 

        cmd.CommandType = CommandType.StoredProcedure

        cmd.CommandTimeout = 120 'Number of seconds to wait for a response.
 

        Dim sqlDR As SqlDataReader

        conn.Open()

        sqlDR = cmd.ExecuteReader(CommandBehavior.Default)
 
 

        r = New TableRow

        r.CssClass = "HeaderCell"

        r.Style.Add("background", "ccffcc")

        r.Style.Add("color", "Black")

        c = New TableCell

        c.ColumnSpan = 5
 

        c = New TableCell

        c.Text = "UPC<br>code"

        r.Cells.Add(c)
 

        c = New TableCell

        c.Text = "Product Name"

        r.Cells.Add(c)
 

        c = New TableCell

        c.Text = "Quantity"

        r.Cells.Add(c)
 

        c = New TableCell

        c.Text = "Subtotal"

        r.Cells.Add(c)
 

        'c = New TableCell

        'c.Text = "Case"

        'r.Cells.Add(c)
 

        'c = New TableCell

        'c.Text = "pallet"

        'r.Cells.Add(c)
 

        'c = New TableCell

        'c.Text = "Trailer"

        'r.Cells.Add(c)
 

        Table1.Rows.Add(r)
 
 

        While sqlDR.Read

            r = New TableRow

            r.CssClass = "prgdata"

            c = New TableCell

            c.Text = sqlDR("Item") & "<br>" & sqlDR("Item")

            r.Cells.Add(c)
 

            r = New TableRow

            r.CssClass = "prgdata"

            c = New TableCell

            c.Text = sqlDR("Name") & "<br>" & sqlDR("Name")

            r.Cells.Add(c)
 

            r = New TableRow

            r.CssClass = "prgdata"

            c = New TableCell

            c.Text = sqlDR("Quantity") & "<br>" & sqlDR("Quantity")

            r.Cells.Add(c)
 

            r = New TableRow

            r.CssClass = "prgdata"

            c = New TableCell

            c.Text = sqlDR("SubTotal") & "<br>" & sqlDR("SubTotal")

            r.Cells.Add(c)
 
 

            Table1.Rows.Add(r)
 
 

        End While
 

        
 

        If (Not conn Is Nothing) Then

            If conn.State <> ConnectionState.Closed Then conn.Close()

            conn.Dispose()

        End If

        If Not cmd Is Nothing Then cmd.Dispose()

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now