Back in January, Gary taught us a lot during his tenure with Initech.

Whenever Jake would offer him an idea on how to get something done in his ASP.NET project, Gary would say that it could not be done for one or more of a list of stock reasons: “security issues”, “incompatibility with ASP.NET”, “.NET doesn’t have that feature”, et cetera. One wonders which of these motivations produced the following code for copying the business database customer table to the database that backs the web presence.

Public Function UpdateWebCustomerTable() As Boolean 'SQL and data variables
   Dim DS As New CustomerData
   Dim DA As New CustomerDataTableAdapters.CustomerMasterViewTableAdapter
   Dim DT As New CustomerData.CustomerMasterViewDataTable
   Dim Count As Integer
   Dim i As Integer
   Dim cmd As SqlCommand

   'Clear tblCustomerMaster in preparation for update
   cmd = New SqlCommand("DELETE FROM tblCustomerMaster", conn)
   conn.Open()
   cmd.ExecuteNonQuery()
   conn.Close()

   'INSERT spaceholder for dropdown list
   cmd = New SqlCommand("INSERT INTO tblCustomerMaster (CustomerID, " _
              & "IndustryID, CustomerName, Address1, Address2, City, " _
              & "State, Zip, Salesperson, Allocation,Supervisor,Manager)" _
              & " VALUES('0', '0','Select Store', '.', '.', '.', '.', " _
              & "'.','.','.','.','.')", conn)
   conn.Open()
   cmd.ExecuteNonQuery()
   conn.Close()

   'Fill Dataset
   DA.Fill(DS.CustomerMasterView)

   'Count rows in dataset
   Count = DS.Tables(1).Rows.Count - 1

   'Create arrays for data columns
   Dim CustomerID(Count) As String
   Dim IndustryId(Count) As Integer
   Dim CustomerName(Count) As String
   Dim Address1(Count) As String
   Dim Address2(Count) As String
   Dim City(Count) As String
   Dim State(Count) As String
   Dim Zip(Count) As String
   Dim Allocation(Count) As String
   Dim PaymentSource(Count) As String

   For i = 0 To Count
       CustomerID(i) = DS.Tables(1).Rows(i).Item(0)
       CustomerName(i) = DS.Tables(1).Rows(i).Item(1)
       Address1(i) = DS.Tables(1).Rows(i).Item(2)
       Address2(i) = DS.Tables(1).Rows(i).Item(3)
       City(i) = DS.Tables(1).Rows(i).Item(4)
       State(i) = DS.Tables(1).Rows(i).Item(5)
       Zip(i) = DS.Tables(1).Rows(i).Item(6)
       Allocation(i) = DS.Tables(1).Rows(i).Item(7)
       PaymentSource(i) = DS.Tables(1).Rows(i).Item(8)
       PaymentSource(i) = RTrim(PaymentSource(i))
       IndustryId(i) = DS.Tables(1).Rows(i).Item(9)

   Next

   For i = 0 To Count
       'Strip out single quotation marks
       If CustomerName(i).Contains("'") Then
      CustomerName(i) = CustomerName(i).Replace("'", "")
       End If

       If Address1(i).Contains("'") Then
      Address1(i) = Address1(i).Replace("'", "")
       End If

       If Address2(i).Contains("'") Then
      Address2(i) = Address2(i).Replace("'", "")
       End If

       If City(i).Contains("'") Then
      City(i) = City(i).Replace("'", "")
       End If

       cmd = New SqlCommand("INSERT INTO tblCustomerMaster (CustomerID, " _
             & "CustomerName, IndustryID, Address1, Address2, City, State, " _
             & " Zip, Allocation, cPaymentSource)" _
             & "VALUES('" & CustomerID(i) & "','" & CustomerName(i) & "','" _
             & IndustryId(i) & "','" & Address1(i) & "','" & Address2(i) _
             & "','" & City(i) & "','" & State(i) & "','" & Zip(i) & "','" _
             & Allocation(i) & "','" & PaymentSource(i) & "')", conn)
       conn.Open()
       cmd.ExecuteNonQuery()
       conn.Close()
   Next
End Function