Having trouble figuring out my loop.

[VBBeginner17]

Right now in my image below you can see my form. However, I want to show the account number, purchase order number.gridview..totals..etc.. for each different purchase order number. How would I go about fixing that?


Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

Declaring all Subs
GetSessionVariables()
GetOrders()
GetCheckForData()
GetSummaryDataGrid()
GetAdjustments()
GetTaxes()
GetShipping()
GetGrandTotal()
GetStandardShipping()
GetWLADShipping()
GetMTADShipping()
GetRushShipping()
GetReshipShipping()
GetWLADSales()
GetMTADSales()
GetALASales()
GetHandling()

End Sub

Public Sub GetSessionVariables()

Login Text that shows up on top right of every page showing that you are logged in as a user
Dim lbl As Label = Me.Master.FindControl("lblUser")
lbl.Visible = True
lbl.Text = Session("LOGINNAME").ToString

Account Number
lblAccount.Visible = True
lblAccount.Text = Session("LOGINNAME").ToString

Account Name
lblProgramName.Visible = True
lblProgramName.Text = Session("PROGRAMNAME").ToString

Start Date
lblstartDate.Visible = True
lblstartDate.Text = Session("StartDate").ToString

End Date
lblEndDate.Visible = True
lblEndDate.Text = Session("EndDate").ToString

End Sub

Public Sub GetOrders()

SqlShippingReport.SelectCommand = "SELECT ORD1PONBR, FULLORDID FROM ORDERHDR " & _
" WHERE ACCTNBR=" & lblAccount.Text & " and CRDT >=" & Session("StartDate") & _
" and CRDT <=" & Session("EndDate") & ""

Dim dv As DataView = CType(SqlShippingReport.Select(DataSourceSelectArguments.Empty), DataView)
Dim orderCount As Integer = 0
If dv.Count > 0 Then
Dim i As Integer = 0
Do Until i = dv.Count
Dim dr As DataRowView = dv.Item(i)
lblPurchaseOrder.Text += dr.Item(0).ToString & ","
lblAllOrders.Text += dr.Item(1).ToString & ","
i = i + 1
orderCount = orderCount + 1
Loop
lblAllOrders.Text = lblAllOrders.Text.TrimEnd(",")
lblHMROrdersTotal.Text = orderCount
lblHandling.Text = orderCount

End If

End Sub

Protected Sub GridView1_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound

If e.Row.RowType = DataControlRowType.DataRow Then
For i As Integer = 0 To e.Row.Cells.Count - 1
GridView1.HeaderRow.Style.Add("border-bottom-style", "ridge")
GridView1.HeaderRow.Style.Add("text-align", "left")
Next
End If

End Sub

Public Sub GetCheckForData()

If String.IsNullOrEmpty(lblHandling.Text) Then
Check if your DateRange exists (assuming you have two TextBoxes for these)
If String.IsNullOrEmpty("StartDate") AndAlso String.IsNullOrEmpty("EndDate") Then
If no records were found - display your message instead of navigating
Else
Dim Action As Windows.Forms.DialogResult
Action = Windows.Forms.MessageBox.Show("No records are found in the date range.", "No Records", Windows.Forms.MessageBoxButtons.OK, _
Windows.Forms.MessageBoxIcon.Exclamation, Windows.Forms.MessageBoxDefaultButton.Button2, Windows.Forms.MessageBoxOptions.DefaultDesktopOnly)
Select Case Action
Case Windows.Forms.DialogResult.OK
Response.Redirect("ReportSelection.aspx")
End Select
End If
End If

End Sub

Public Sub GetSummaryDataGrid()

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 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 IsDBNull(dr1.Item(0).ToString) = False Then
row("Quantity") = dr1.Item(0).ToString
Else
row("Quantity") = "0"
End If
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 row("Quantity").ToString = String.Empty Then
row("Quantity") = "0"
End If

If row("Price").ToString = String.Empty Then
row("Price") = "0.00"
End If

row("Extension") = (CDbl(row("Quantity").ToString) * CDbl(row("Price").ToString)).ToString("C")
row("Price") = (CDec(dr.Item(2).ToString)).ToString("C")
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
GridView1.DataSource = Session("DT")
GridView1.DataBind()

End Sub

Public Sub GetAdjustments()

SqlShippingReport.SelectCommand = "SELECT SUM(ORD1ADJUST) FROM ORDERHDR " & _
" WHERE ACCTNBR=" & lblAccount.Text & " and CRDT >=" & Session("StartDate") & _
" and CRDT <=" & Session("EndDate") & ""

Dim dvAdjustment As DataView = CType(SqlShippingReport.Select(DataSourceSelectArguments.Empty), DataView)
If dvAdjustment.Count > 0 Then
Dim dr As DataRowView = dvAdjustment.Item(0)
Checking for null values
If (Not String.IsNullOrEmpty(dr.Item(0).ToString())) Then
lblAdjustments.Text = (CDec(dr.Item(0).ToString)).ToString("C")
Else
lblAdjustments.Text = "$0.00"
End If
Session("ADJUSTMENTS") = lblAdjustments.Text

End If
End Sub

Continue reading...