Click here to Skip to main content
15,889,315 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Right now my code takes all the order between a specified date and puts them into one table ..However I want each order to have its own table.. how would i go about doing that

VB
Public Sub GetBrokenUpOrders()

        Dim dv As DataView = CType(SqlShippingReport.Select(DataSourceSelectArguments.Empty), DataView)
        If dv.Count > 0 Then
            Dim dr As DataRowView = dv.Item(0)
            Session("SHIPPEDSUMMARYTABLE") = GridView1.ToString
        End If


        SqlShippingReportDataTable.SelectCommand = "SELECT ITEM.ITEMNM, ITEM.DSC, ITMPRICE.PRICE" & _
                                       "FROM ITEM INNER JOIN ITMPRICE ON ITEM.ITEMNM = ITMPRICE.ITEMNM" & _
                                       "WHERE (ITMPRICE.ACCTNBR = ?)'" & lblAccount.Text & "'"

        SqlShippingReportDataTable.SelectCommand = "SELECT QTY" & _
                                        "FROM ORDERDET " & _
                                        "WHERE ACCTNBR='" & lblAccount.Text & "'"
        'Create Datatable to store info
        Dim dt As New DataTable
        dt.Columns.Add("Inventory Code", GetType(String))
        dt.Columns.Add("Inventory Description", GetType(String))
        dt.Columns.Add("Quantity", GetType(String))
        dt.Columns.Add("Price", GetType(String))
        dt.Columns.Add("Extension", GetType(String))
        Session("DT") = dt

        'Fill Table with Info
        Dim t As DataTable = DirectCast(Session("DT"), DataTable)
        Dim sGroup As String = ""
        Dim groupCt As Integer = 0
        Do Until groupCt = 7
            Select Case groupCt
                Case 0
                    sGroup = "Shakes"
                Case 1
                    sGroup = "PackKits"
                Case 2
                    sGroup = "Entrees"
                Case 3
                    sGroup = "Cereal"
                Case 4
                    sGroup = "Flavorings"
                Case 5
                    sGroup = "Bars"
                Case 6
                    sGroup = "Materials"
            End Select

            SqlShippingReportDataTable.SelectCommand = "SELECT ITEM.ITEMNM, ITEM.DSC, ITMPRICE.PRICE " & _
                                                    "FROM ITEM INNER JOIN ITMPRICE ON ITEM.ITEMNM = ITMPRICE.ITEMNM " & _
                                                    "WHERE (ITMPRICE.ACCTNBR = '" & lblAccount.Text & "')and ITEM.STATUS = 'A'" & _
                                                "and GroupCode IN (select '" & sGroup & "') order by rptsrtodr"
            ' Dataview
            Dim DV2 As DataView = CType(SqlShippingReportDataTable.Select(DataSourceSelectArguments.Empty), DataView)
            If DV2.Count > 0 Then
                Dim i As Integer = 0
                Do Until i = DV2.Count
                    'Create & fill Datarow
                    Dim dr As DataRowView = DV2.Item(i)
                    'Create & fill Data Table Row
                    Dim row As DataRow = t.NewRow()

                    row("Inventory Code") = dr.Item(0).ToString
                    row("Inventory Description") = dr.Item(1).ToString
                    row("Price") = (CDec(dr.Item(2).ToString)).ToString("C")

                    SqlShippingReportDataTable.SelectCommand = "Select SUM(OD.QTY) from ORDERDET as OD, Orderhdr as O where OD.ITEMID ='" & _
                        dr.Item(0).ToString & "' and O.CRDT >='" & Session("StartDate") & "' and O.CRDT <='" & _
                        Session("EndDate") & "' and OD.ORDERHDRID = O.ORDERHDRID and O.ACCTNBR = '" & _
                        lblAccount.Text & "' and O.ORDSTATUS <> 'Deleted'"

                    Dim DV3 As DataView = CType(SqlShippingReportDataTable.Select(DataSourceSelectArguments.Empty), DataView)
                    If DV3.Count > 0 Then
                        Dim dr1 As DataRowView = DV3.Item(0)
                        ' If Quanity is null then put in a 0 in the quantity column
                        If IsDBNull(dr1.Item(0).ToString) = False Then
                            row("Quantity") = dr1.Item(0).ToString
                        Else
                            row("Quantity") = "0"
                        End If
                        ' If Price is null then put 0.00 in the quantity column
                        If IsDBNull(dr.Item(2).ToString) = False Then
                            row("Price") = dr.Item(2).ToString
                        Else
                            row("Price") = "0.00"
                        End If
                    Else
                        row("Quantity") = "0"
                        row("Price") = "0.00"
                    End If

                    ' If Quantity is empty place a 0 in the column
                    If row("Quantity").ToString = String.Empty Then
                        row("Quantity") = "0"
                    End If

                    ' If Price is empty place 0.00 in the column 
                    If row("Price").ToString = String.Empty Then
                        row("Price") = "0.00"
                    End If

                    ' Get Extension by multiplying Quantity by the Price
                    row("Extension") = (CDbl(row("Quantity").ToString) * CDbl(row("Price").ToString)).ToString("C")
                    row("Price") = (CDec(dr.Item(2).ToString)).ToString("C")

                    ' If the quantity is greater than 0 then show the row, if the quantity is 0 don't show the row
                    If row("Quantity") > "0" Then
                        t.Rows.Add(row)
                    End If

                    i = i + 1
                Loop
            End If
            groupCt = groupCt + 1
        Loop


        'Save table so we can add to it
        Session("DT") = t
        'Write Datatable to gridview and display results
        GridView2.DataSource = Session("DT")
        GridView2.DataBind()

    End Sub
Posted

1 solution

The way you are doing it now is correct, you do NOT want to create a new table for each order. What happens when you have 1000 orders, or 5000? You will have 5000 tables. This is a nightmare from a database management standpoint, after you start adding relationships and triggers then you are going to have a real problem.

Keep your orders in a single table, its much easier to work with, much easier to expand later, and is a hell of a lot easier on the database engine.
 
Share this answer
 
Comments
PythonProgrammer 2-Dec-13 14:28pm    
The company I work for wants it to be broken out into different tables for each order, so how would I do that
Ron Beyer 2-Dec-13 14:35pm    
Does your company employ a DB manager? Whoever is making that decision is making a poor one without knowing what databases are, (s)he's probably thinking tables are like excel sheets. I would demonstrate a single table and develop a view query that only shows one order at a time.

Honestly, truly honestly its possible, but I can't in good conscience recommend it. What database backend are you using (please don't say Access)? There are ways to loop through the schema tables and get the table names, but really this approach breaks everything databases are meant to do, at that point you might as well store them in flat files.
PythonProgrammer 2-Dec-13 14:36pm    
Yes I understand where you are coming from they want both a table with all the orders combined together, and also one that is separate for each order. So how would i change my code above to separate into a new table for each order
Mike Meinz 2-Dec-13 15:28pm    
No one does that! No one here is going to advise you on how to do that. You need to explain to your employers that doing that would be a disaster.
PythonProgrammer 2-Dec-13 15:32pm    
I did, they just want to be able to select a small date range of a specific customer and see all the orders separated out

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900