In VBA I have a procedure that while it is looping through 2 record sets it adds new records to another SQL database table.
I want to move this program to a web application using vb.net (visual studio 8) however as I am rather new to VB.net I donot know what is the best method I should use and how to go about it. Below I have incuded a simplified example of my code in VBA, I would really appreciate it if someone can help me in the right direction, including an example.
The function is looking at a table with multiple machines in a quotation (TBLCOMPROOM)
It is then creating a service schedule in hours based on the service items in the Service Table(TBLTEMPSERVICEPARTS), into a Temporary Calculation Tample (TBLTEMP)
It is looping through the TBLCOMPROOM to find the first machine and its parameters
It looks up the service parts in TBLTEMPSERVICEPARTS gets the first part, looks at the hours and adds a new Record to TBLTEMP it will then loop through the service duration and add the same part with the next value of Service Hours until it reaches the end of the service duration, goes to the next part and does the same thing. When it finished this loop it will select the next machine, find the first part etc etc. (the loops in this example or not 100% correct as I had to remove a lot of fields to shorten the example)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim f, g, h, start As Double
Dim sqldata. SQLCompRoom As String
Set db = CurrentDb()
SQLComproom = "SELECT TblCompRoomContract.comprooomid, TblCompRoomContract.quoteref, TblCompRoomContract.runhours, TblCompRoomContract.starthours, TblCompRoomContract.Actualrunhours " & _
"FROM TblCompRoomContract " & _
"WHERE (((TblCompRoomContract.quoteref) = '" & [Forms]![frmquote].[Quotenumber] & "')) " & _
"ORDER BY TblCompRoomContract.comprooomid;"
Set rs1 = db.OpenRecordset("TblTemp", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset(SQLComproom, dbOpenDynaset, dbSeeChanges)
If rs2.EOF = False Then
rs2.MoveFirst
Do Until rs2.EOF '1st loop
varcomp = rs2!comprooomid
Start = rs2!starthours
Averagerunninghours = rs2!runhours
Actualrunhours = rs2!Actualrunhours
g = 0
g = (ContractLenght * Averagerunninghours) + Start
sqldata = "SELECT TblTempServiceParts.* FROM TblTempServiceParts WHERE (((TblTempServiceParts.comproomID)=" & varcomp & ") AND ((TblTempServiceParts.Use)=True));"
Set rs = db.OpenRecordset(sqldata, dbOpenDynaset, dbSeeChanges)
rs.MoveFirst
Do Until rs.EOF 'rs!comproomID <> varcomp And Not rs.EOF ' 2nd loop
'Do Until rs.EOF
h = 0
f = rs!servicehours
If f <= g Then
Do Until h > g
rs1.AddNew
If h = 0 Then
rs1!Hrs = f
Else
rs1!Hrs = h
End If
rs1!comprooomid = rs!comproomID
End If
rs1.Update
rs1.MoveLast
h = rs1!Hrs + rs!RepHours '+ f
Loop ' 2nd loop
End If
rs1.Update
End If
End If
rs.MoveNext
Loop ' 2nd loop
rs2.MoveNext
Loop
End If
I want to move this program to a web application using vb.net (visual studio 8) however as I am rather new to VB.net I donot know what is the best method I should use and how to go about it. Below I have incuded a simplified example of my code in VBA, I would really appreciate it if someone can help me in the right direction, including an example.
The function is looking at a table with multiple machines in a quotation (TBLCOMPROOM)
It is then creating a service schedule in hours based on the service items in the Service Table(TBLTEMPSERVICEPARTS), into a Temporary Calculation Tample (TBLTEMP)
It is looping through the TBLCOMPROOM to find the first machine and its parameters
It looks up the service parts in TBLTEMPSERVICEPARTS gets the first part, looks at the hours and adds a new Record to TBLTEMP it will then loop through the service duration and add the same part with the next value of Service Hours until it reaches the end of the service duration, goes to the next part and does the same thing. When it finished this loop it will select the next machine, find the first part etc etc. (the loops in this example or not 100% correct as I had to remove a lot of fields to shorten the example)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim f, g, h, start As Double
Dim sqldata. SQLCompRoom As String
Set db = CurrentDb()
SQLComproom = "SELECT TblCompRoomContract.comprooomid, TblCompRoomContract.quoteref, TblCompRoomContract.runhours, TblCompRoomContract.starthours, TblCompRoomContract.Actualrunhours " & _
"FROM TblCompRoomContract " & _
"WHERE (((TblCompRoomContract.quoteref) = '" & [Forms]![frmquote].[Quotenumber] & "')) " & _
"ORDER BY TblCompRoomContract.comprooomid;"
Set rs1 = db.OpenRecordset("TblTemp", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset(SQLComproom, dbOpenDynaset, dbSeeChanges)
If rs2.EOF = False Then
rs2.MoveFirst
Do Until rs2.EOF '1st loop
varcomp = rs2!comprooomid
Start = rs2!starthours
Averagerunninghours = rs2!runhours
Actualrunhours = rs2!Actualrunhours
g = 0
g = (ContractLenght * Averagerunninghours) + Start
sqldata = "SELECT TblTempServiceParts.* FROM TblTempServiceParts WHERE (((TblTempServiceParts.comproomID)=" & varcomp & ") AND ((TblTempServiceParts.Use)=True));"
Set rs = db.OpenRecordset(sqldata, dbOpenDynaset, dbSeeChanges)
rs.MoveFirst
Do Until rs.EOF 'rs!comproomID <> varcomp And Not rs.EOF ' 2nd loop
'Do Until rs.EOF
h = 0
f = rs!servicehours
If f <= g Then
Do Until h > g
rs1.AddNew
If h = 0 Then
rs1!Hrs = f
Else
rs1!Hrs = h
End If
rs1!comprooomid = rs!comproomID
End If
rs1.Update
rs1.MoveLast
h = rs1!Hrs + rs!RepHours '+ f
Loop ' 2nd loop
End If
rs1.Update
End If
End If
rs.MoveNext
Loop ' 2nd loop
rs2.MoveNext
Loop
End If
