Web method file

Imports System.Web
Imports System.Data.OleDb
Imports System.Data
Imports System.Web.Services
Imports System.Web.Services.Protocols

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Access
   Inherits System.Web.Services.WebService

   <WebMethod()> _
   Public Function HelloWorld() As String
       Return "Hello World"
   End Function

   <System.Web.Services.WebMethod()> _
   Public Function GetAccessData(ByVal command As String) As DataSet
       Dim dataset As DataSet = Nothing
       ' Connect to Access DB here, execute command and populate data
set with returned data.

       Return dataset

   End Function

   ' UpdateAccessData connects to and access database and inserts the
data tables into the correct locations.
   ' Parameters: DonorChanges as DataSet. DonorChanges is the dataset
of pick-ups and deliverys.
   ' Return value: Boolean. Success or failure.
   <System.Web.Services.WebMethod()> _
   Public Function UpdateAccessData(ByVal DonorChanges As DataSet) As
Boolean
       Me.Server.ScriptTimeout = 60000000
       Dim DonorStartingRows As Integer
       Dim AgencyStartingRows As Integer
       Dim LinkStartingRows As Integer
       'Dim DriverStartingRows As Integer

       ' Connection string to connect to access database
       Dim FHDB As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:
\testpre\Replica_of_be\Replica_of_harvest2000_be.mdb;User
Id=admin;Password=;")

       ' Table adapter to get information from tables in the database
       Dim daFoodDonor As New OleDb.OleDbDataAdapter("select * from
tblFoodDonations", FHDB)
       ' Data table to store info
       Dim FoodDonorTable As New DataTable

       ' Fill the data table with info
       daFoodDonor.Fill(FoodDonorTable)
       daFoodDonor.Dispose()
       ' No longer using this adapter so free memory

       DonorStartingRows = FoodDonorTable.Rows.Count

       FoodDonorTable.Dispose()

       ' Find the number of rows we will be inserting
       Dim rowsDonor As Integer = DonorChanges.Tables(0).Rows.Count
       ' insert that number of rows
       For rowsCount As Integer = 1 To rowsDonor
           Dim addDonorReciepts As New OleDb.OleDbCommand ' Declare
new command
           Dim oneRowDon As DataRow =
DonorChanges.Tables(0).Rows.Item((rowsCount - 1)) ' The row to be
inserted
           Dim julda As Long = CLng(Format(Year(oneRowDon.Item(8)),
"0000") _
                 + Format(DateDiff("d", CDate("01/01/" _
                 + Format(Year(oneRowDon.Item(8)), "0000")),
oneRowDon.Item(8)) _
                 + 1, "000"))
           Dim mt As String =
String.Concat(oneRowDon.Item(9).Hours.ToString, _

oneRowDon.Item(9).Minutes.ToString, _

oneRowDon.Item(9).Seconds.ToString)
           Dim uniID As String = String.Concat(julda.ToString,
mt.ToString, oneRowDon.Item(10).ToString)
           If oneRowDon.Item(1).Equals("Inventory") Then
               DonorStartingRows = DonorStartingRows - 1
           Else
               With addDonorReciepts
                   .CommandTimeout = 600000
                   'insert command
                   .CommandText = "insert into tblFoodDonations
([Date], [Donor Time], [Bakery], [Meat]," & _
                   "[Fruit], [Dairy], [Vegetable], [Prepared],
[Juice], [Non-Perishable], [Non-Food]," & _
                   "[Total Donations], [DonorReceiptId], Driver,
TruckNumber, DonorID, FoodDonationsID)" & _
                   "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                   .Parameters.Add(New OleDbParameter("Date",
oneRowDon.Item(8).Date))
                   .Parameters.Add(New OleDbParameter("Time",
oneRowDon.Item(9)))
                   .Parameters.Add(New OleDbParameter("Bakery",
oneRowDon.Item(12)))
                   .Parameters.Add(New OleDbParameter("Meat",
oneRowDon.Item(14)))
                   .Parameters.Add(New OleDbParameter("Fruit",
oneRowDon.Item(15)))
                   .Parameters.Add(New OleDbParameter("Dairy",
oneRowDon.Item(13)))
                   .Parameters.Add(New OleDbParameter("Vege",
oneRowDon.Item(16)))
                   .Parameters.Add(New OleDbParameter("Prep",
oneRowDon.Item(17)))
                   .Parameters.Add(New OleDbParameter("Juice",
oneRowDon.Item(20)))
                   .Parameters.Add(New OleDbParameter("NP",
oneRowDon.Item(21)))
                   .Parameters.Add(New OleDbParameter("NF",
oneRowDon.Item(22)))
                   .Parameters.Add(New OleDbParameter("Total",
oneRowDon.Item(23)))
                   .Parameters.Add(New OleDbParameter("ReceiptID",
uniID))
                   .Parameters.Add(New OleDbParameter("Driver",
oneRowDon.Item(11)))
                   .Parameters.Add(New OleDbParameter("Truck",
oneRowDon.Item(10)))
                   .Parameters.Add(New OleDbParameter("DonorID",
oneRowDon.Item(0)))
                   .Parameters.Add(New OleDbParameter("FoodID",
uniID))
                   Dim fs As IO.FileStream = New IO.FileStream("C:
\testpre\Signature\Donor\" & uniID & ".bmp", IO.FileMode.Create)
                   fs.Write(oneRowDon.Item(19), 0,
oneRowDon.Item(19).Length)
                   fs.Flush()
                   fs.Close()
                   .Connection = FHDB ' connection string
                   .Connection.Open() ' open connection
                   .ExecuteNonQuery() ' execute command
                   .Connection.Close() ' close connection
                   .Dispose() ' free memory
               End With
               For linkRowNum As Integer = 0 To
DonorChanges.Tables(2).Rows.Count - 1
                   If
DonorChanges.Tables(2).Rows(linkRowNum).Item(1).Equals(oneRowDon.Item(24).ToString)
Then

DonorChanges.Tables(2).Rows(linkRowNum).Item(1) = uniID
                   End If
               Next
           End If
       Next

       Dim daAgencyRec As New OleDb.OleDbDataAdapter("select * from
tblAgencyReceipt", FHDB)
       Dim AgencyRecTable As New DataTable

       daAgencyRec.Fill(AgencyRecTable)
       daAgencyRec.Dispose()

       AgencyStartingRows = AgencyRecTable.Rows.Count

       AgencyRecTable.Dispose()

       ' Find the number of rows we will be inserting
       Dim rowsAgency As Integer = DonorChanges.Tables(1).Rows.Count
       ' insert that number of rows
       For rowscount As Integer = 1 To rowsAgency
           Dim addAgeRec As New OleDb.OleDbCommand ' Declare new
command
           Dim oneRowAge As DataRow =
DonorChanges.Tables(1).Rows.Item((rowscount - 1)) ' The row to be
inserted
           Dim julda As Long = CLng(Format(Year(oneRowAge.Item(8)),
"0000") _
                 + Format(DateDiff("d", CDate("01/01/" _
                 + Format(Year(oneRowAge.Item(8)), "0000")),
oneRowAge.Item(8)) _
                 + 1, "000"))
           Dim mt As String =
String.Concat(oneRowAge.Item(9).Hours.ToString, _

oneRowAge.Item(9).Minutes.ToString, _

oneRowAge.Item(9).Seconds.ToString)
           Dim uniID As String = String.Concat(julda.ToString,
mt.ToString, oneRowAge.Item(10).ToString)
           If oneRowAge.Item(1).Equals("Inventory") Then
               AgencyStartingRows = AgencyStartingRows - 1
           Else
               With addAgeRec
                   .CommandTimeout = 600000
                   'insert command
                   .CommandText = "insert into tblAgencyReceipt
(AgencyReceiptId, [Date], ReceiverId, Bakery," & _
                   "Dairy, Meat, Fruit, Vegetable, Prepared, [Non-
Perishable], [Non-Food], [DateEntered], Driver," & _
                   "TruckNumber, DropTime, Juice, ReceiptID) values
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                   .Parameters.Add(New OleDbParameter("AgencyRecID",
uniID))
                   .Parameters.Add(New OleDbParameter("Date",
oneRowAge.Item(8).Date))
                   .Parameters.Add(New OleDbParameter("ReceiverID",
oneRowAge.Item(0)))
                   .Parameters.Add(New OleDbParameter("Bakery",
oneRowAge.Item(13)))
                   .Parameters.Add(New OleDbParameter("Dairy",
oneRowAge.Item(14)))
                   .Parameters.Add(New OleDbParameter("Meat",
oneRowAge.Item(15)))
                   .Parameters.Add(New OleDbParameter("Fruit",
oneRowAge.Item(16)))
                   .Parameters.Add(New OleDbParameter("Vege",
oneRowAge.Item(17)))
                   .Parameters.Add(New OleDbParameter("Prep",
oneRowAge.Item(18)))
                   .Parameters.Add(New OleDbParameter("NP",
oneRowAge.Item(20)))
                   .Parameters.Add(New OleDbParameter("NF",
oneRowAge.Item(21)))
                   .Parameters.Add(New OleDbParameter("DateEnt",
DateTime.Today))
                   .Parameters.Add(New OleDbParameter("Driver",
oneRowAge.Item(11)))
                   .Parameters.Add(New OleDbParameter("Truck",
oneRowAge.Item(10)))
                   .Parameters.Add(New OleDbParameter("Time",
oneRowAge.Item(9)))
                   .Parameters.Add(New OleDbParameter("Juice",
oneRowAge.Item(19)))
                   .Parameters.Add(New OleDbParameter("ReceiptID",
uniID))
                   Dim fs As IO.FileStream = New IO.FileStream("C:
\testpre\Signature\Agency\" & uniID & ".bmp", IO.FileMode.Create)
                   fs.Write(oneRowAge.Item(12), 0,
oneRowAge.Item(12).Length)
                   fs.Flush()
                   fs.Close()
                   .Connection = FHDB ' connection string
                   .Connection.Open() ' open connection
                   .ExecuteNonQuery() ' execute command
                   .Connection.Close() ' close connection
                   .Dispose() ' free memory
               End With
               For linkRowNum As Integer = 0 To
DonorChanges.Tables(2).Rows.Count - 1
                   If
DonorChanges.Tables(2).Rows(linkRowNum).Item(0).Equals(oneRowAge.Item(23).ToString)
Then

DonorChanges.Tables(2).Rows(linkRowNum).Item(0) = uniID
                   End If
               Next
           End If
       Next
       Dim rowsInventory As Integer =
DonorChanges.Tables(3).Rows.Count

       For rowscount As Integer = 1 To rowsInventory
           Dim addInventoryRec As New OleDb.OleDbCommand
           Dim oneRowInv As DataRow =
DonorChanges.Tables(3).Rows.Item((rowscount - 1))
           DonorStartingRows = DonorStartingRows + 1
           With addInventoryRec
               .CommandTimeout = 600000
               .CommandText = "insert into tblFoodDonations ([Date],
[Donor Time], [Bakery], [Meat]," & _
                   "[Fruit], [Dairy], [Vegetable], [Prepared],
[Juice], [Non-Perishable], [Non-Food]," & _
                   "[Total Donations], [DonorReceiptId], Driver,
TruckNumber, DonorID, FoodDonationsID, Description, InventoryTypeId)"
& _
                   "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
               .Parameters.Add(New OleDbParameter("Date",
oneRowInv.Item(10).Date))
               .Parameters.Add(New OleDbParameter("Time",
oneRowInv.Item(11)))
               .Parameters.Add(New OleDbParameter("Bakery",
oneRowInv.Item(1)))
               .Parameters.Add(New OleDbParameter("Meat",
oneRowInv.Item(3)))
               .Parameters.Add(New OleDbParameter("Fruit",
oneRowInv.Item(4)))
               .Parameters.Add(New OleDbParameter("Dairy",
oneRowInv.Item(2)))
               .Parameters.Add(New OleDbParameter("Vege",
oneRowInv.Item(5)))
               .Parameters.Add(New OleDbParameter("Prep",
oneRowInv.Item(6)))
               .Parameters.Add(New OleDbParameter("Juice",
oneRowInv.Item(7)))
               .Parameters.Add(New OleDbParameter("NP",
oneRowInv.Item(8)))
               .Parameters.Add(New OleDbParameter("NF",
oneRowInv.Item(9)))
               .Parameters.Add(New OleDbParameter("Total",
oneRowInv.Item(23)))
               .Parameters.Add(New OleDbParameter("ReceiptID",
oneRowInv.Item(0)))
               .Parameters.Add(New OleDbParameter("Driver",
oneRowInv.Item(12)))
               .Parameters.Add(New OleDbParameter("Truck",
oneRowInv.Item(13)))
               .Parameters.Add(New OleDbParameter("DonorID",
oneRowInv.Item(24)))
               .Parameters.Add(New OleDbParameter("FoodID",
oneRowInv.Item(0)))
               .Parameters.Add(New OleDbParameter("Description",
oneRowInv.Item(25)))
               .Parameters.Add(New OleDbParameter("InvTypeID", 1))
               .Connection = FHDB ' connection string
               .Connection.Open() ' open connection
               .ExecuteNonQuery() ' execute command
               .Connection.Close() ' close connection
               .Dispose() ' free memory
           End With
       Next

       Dim daLink As New OleDb.OleDbDataAdapter("select * from
tblAgencyDonationLink", FHDB)
       Dim LinkTable As New DataTable
       daLink.Fill(LinkTable)

       LinkStartingRows = LinkTable.Rows.Count
       daLink.Dispose()
       LinkTable.Dispose()

       Dim rowsLink As Integer = DonorChanges.Tables(2).Rows.Count

       For rowscount As Integer = 1 To rowsLink
           Dim addLinkRec As New OleDb.OleDbCommand ' Declare new
command
           Dim oneRowLink As DataRow =
DonorChanges.Tables(2).Rows.Item((rowscount - 1)) ' The row to be
inserted
           With addLinkRec
               .CommandTimeout = 600000
               'insert command
               .CommandText = "insert into tblAgencyDonationLink
(ReceiptID, FoodDonationsID, Bakery, Dairy, Meat," _
               & "Fruit, Vegetable, Prepared, Beverage, [Non-
Perishable], [Non-Food], DateEntered, DistributionDate)" _
               & "values (?,?,?,?,?,?,?,?,?,?,?,?,?)"
               .Parameters.Add(New OleDbParameter("ReceiptID",
oneRowLink.Item(0)))
               .Parameters.Add(New OleDbParameter("FoodID",
oneRowLink.Item(1)))
               .Parameters.Add(New OleDbParameter("Bakery",
oneRowLink.Item(2)))
               .Parameters.Add(New OleDbParameter("Dairy",
oneRowLink.Item(3)))
               .Parameters.Add(New OleDbParameter("Meat",
oneRowLink.Item(4)))
               .Parameters.Add(New OleDbParameter("Fruit",
oneRowLink.Item(5)))
               .Parameters.Add(New OleDbParameter("Vege",
oneRowLink.Item(6)))
               .Parameters.Add(New OleDbParameter("Prep",
oneRowLink.Item(7)))
               .Parameters.Add(New OleDbParameter("Bev",
oneRowLink.Item(8)))
               .Parameters.Add(New OleDbParameter("NP",
oneRowLink.Item(9)))
               .Parameters.Add(New OleDbParameter("NF",
oneRowLink.Item(10)))
               .Parameters.Add(New OleDbParameter("DateEnt",
DateTime.Today))
               .Parameters.Add(New OleDbParameter("DistDate",
oneRowLink.Item(11)))
               .Connection = FHDB ' connection string
               .Connection.Open() ' open connection
               .ExecuteNonQuery() ' execute command
               .Connection.Close() ' close connection
               .Dispose() ' free memory
           End With
       Next

       FHDB.Dispose() ' No longer need the sting free the memory
       ' If all the rows were send return true else thor exeption to
catch
       If EndOfTables(DonorStartingRows, AgencyStartingRows,
LinkStartingRows, DonorChanges) Then
           Return True
       Else
           Throw New ArithmeticException
       End If

   End Function

   <System.Web.Services.WebMethod()> _
   Public Function EndOfTables(ByVal DonorStartingRows As Integer,
ByVal AgencyStartingRows As Integer, ByVal LinkStartingRows As
Integer, ByVal DonorChanges As DataSet) As Boolean
       Me.Server.ScriptTimeout = 600000
       ' Connection string to connect to access database
       Dim FHDB As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:
\testpre\Replica_of_be\Replica_of_harvest2000_be.mdb;User
Id=admin;Password=;")

       ' Table adapter to get information from tables in the database
       Dim daFoodDonor As New OleDb.OleDbDataAdapter("select * from
tblFoodDonations", FHDB)
       Dim daAgencyRec As New OleDb.OleDbDataAdapter("select * from
tblAgencyReceipt", FHDB)
       Dim daLink As New OleDb.OleDbDataAdapter("select * from
tblAgencyDonationLink", FHDB)
       ' Data table to store info
       Dim FoodDonorTable As New DataTable
       Dim AgencyRecTable As New DataTable
       Dim LinkTable As New DataTable
       ' Fill the data table with info
       daFoodDonor.Fill(FoodDonorTable)
       daAgencyRec.Fill(AgencyRecTable)
       daLink.Fill(LinkTable)
       ' Free some memory
       daFoodDonor.Dispose()
       daAgencyRec.Dispose()
       daLink.Dispose()
       ' The number of rows that is supposed to be sent
       Dim DonorTargetRows As Integer =
DonorChanges.Tables(0).Rows.Count
       Dim AgencyTargetRows As Integer =
DonorChanges.Tables(1).Rows.Count
       Dim LinkTargetRows As Integer =
DonorChanges.Tables(2).Rows.Count
       ' free some memory
       DonorChanges.Dispose()
       ' The total number of rows after data has been sent
       Dim DonorEndingRows As Integer = FoodDonorTable.Rows.Count
       Dim AgencyEndingRows As Integer = AgencyRecTable.Rows.Count
       Dim LinkEndingRows As Integer = LinkTable.Rows.Count
       ' free some memory
       FoodDonorTable.Dispose()
       AgencyRecTable.Dispose()
       LinkTable.Dispose()
       ' The number of rows sent
       Dim DonorRows As Integer = DonorEndingRows - DonorStartingRows
       Dim AgencyRows As Integer = AgencyEndingRows -
AgencyStartingRows
       Dim LinkRows As Integer = LinkEndingRows - LinkStartingRows
       ' If everything equals return true if not return false
       If DonorRows.Equals(DonorTargetRows) And _
          AgencyRows.Equals(AgencyTargetRows) And _
          LinkRows.Equals(LinkTargetRows) Then
           Return True
       Else
           Return False
       End If
   End Function

End Class