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

build table programatically vb

 ''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
Seven price
Asked:
Seven price
  • 4
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Seven priceFull StackAuthor Commented:
I am getting an error saying row is not part of   System.Web.UI.WebControls.Table
0
 
Seven priceFull StackAuthor Commented:
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
 
Seven priceFull StackAuthor Commented:
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
 
Seven priceFull StackAuthor Commented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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