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
