<%@Page Language="VB" Debug="True" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<%@Import Namespace="System.Threading" %>
<script runat="server">

Dim oDS As DataSet

Sub Page_Load()

  Dim sCustID As String = Request.QueryString("custID")
  Dim sStep As String = Request.QueryString("step")
  Dim sSelect As String

  ' force current thread to sleep for 3 seconds
  ' to simulate complex code execution
  Thread.Sleep(3000)

  Select Case sStep
    Case "1"
      oDS = New DataSet()
      sSelect = "SELECT CustomerID, CompanyName, City, " _
        & "Country, Phone FROM Customers " _
        & "WHERE CustomerID LIKE @CustomerID"
      AddTable("Customers", sCustID, sSelect)
    Case "2"
      oDS = CType(Session("thedata"), DataSet)
      sSelect = "SELECT OrderID, OrderDate FROM Orders " _
        & "WHERE CustomerID LIKE @CustomerID"
      AddTable("Orders", sCustID, sSelect)
    Case "3"
      oDS = CType(Session("thedata"), DataSet)
      sSelect = "SELECT [Order Details].OrderID, " _
        & "Products.ProductID, Products.ProductName, " _
        & "[Order Details].Quantity, [Order Details].UnitPrice " _
        & "FROM [Order Details] JOIN Products " _
        & "ON [Order Details].ProductID = Products.ProductID " _
        & "WHERE [Order Details].OrderID IN " _
        & " (SELECT OrderID FROM Orders " _
        & "  WHERE CustomerID LIKE @CustomerID)"
      AddTable("OrderDetails", sCustID, sSelect)
    Case "4"
      oDS = CType(Session("thedata"), DataSet)
      CalculateTotal()
    Case Else
      Response.Status = "500 Internal Server Error"
      Response.Write("Error: Invalid Query String Parameter")
  End Select

End Sub

<%-------------------------------------------------------------%>

Sub AddTable(sTableName As String, sCustID As String, sSelect As String)

  If oDS Is Nothing Then

    Response.Status = "500 Internal Server Error"
    Response.Write("Error: Cannot access DataSet in ASP.NET session")

  Else

    Dim sConnect As String _
      = ConfigurationSettings.AppSettings("NorthwindSqlClientConnectString")
    Dim oConnect As New SqlConnection(sConnect)

    Dim oDA As New SqlDataAdapter(sSelect, oConnect)
    oDA.SelectCommand.Parameters.Add("@CustomerID", sCustID & "%")

    Try

      oDA.Fill(oDS, sTableName)
      Session("thedata") = oDS

      Response.Status = "200 OK"
      Response.Write("OK")

    Catch oErr As Exception

      Response.Status = "500 Internal Server Error"
      Response.Write("Error: " & oErr.Message)

    End Try

  End If

End Sub

<%-------------------------------------------------------------%>

Sub CalculateTotal()

  Dim dTotal As Decimal = 0

  Try

    For Each oRow As DataRow In oDS.Tables("OrderDetails").Rows
      dTotal += (oRow("Quantity") * oRow("UnitPrice"))
    Next

    Response.Status = "200 OK"
    Response.Write(dTotal.ToString("F2"))

  Catch oErr As Exception

    Response.Status = "500 Internal Server Error"
    Response.Write("Error: " & oErr.Message)

  End Try

End Sub

<%-------------------------------------------------------------%>
</script>