Solved

build table programatically vb

Posted on 2009-03-30
5
216 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

840 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