Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

build table programatically vb

Posted on 2009-03-30
5
Medium Priority
?
223 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:Seven price
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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:Seven price
ID: 24020043
I am getting an error saying row is not part of   System.Web.UI.WebControls.Table
0
 
LVL 9

Author Comment

by:Seven price
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:Seven price
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:Seven price
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 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