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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)