Example Student table: Update Query in VBScript

''================================================================= 

            Create a new table in MS Access called Student and perform given task.

        Student Table Structure

           1.       RollNo

           2.       Name

           3.       Address

           4.       Subject

           5.       Mark1

           6.       Mark2

           7.       Mark3

           8.       Avg Marks

           9.       Total

Write a script to perform below task:

1.       Function to calculate Avg Marks of all subjects and update in Avg Marks column.(for all subject)

2.       Function to calculate total marks and update in the total column.

3.       Function to update the address of particular student ( Take input from user)

''================================================================= 

Dim a,b,Avg, TOtal,Address
f = False

Call Avg_Marks

Function Avg_Marks
Set a=CreateObject("adodb.connection")
a.Open "Data Source=E:\Student.mdb;Provider=Microsoft.jet.oledb.4.0"
Set b=a.Execute("select * from student ")
RNo=Cint(inputbox("Enter the RollNo"))

WHILE NOT b.EOF
count = count + 1
if RNo = b("RollNo") then
sm1 = b("Mark1")
sm2 = b("Mark2")
sm3 = b("Mark3")
msgbox "Roll No:" & RNo & " sm1:" &sm1 & " sm2:" &sm2 & " sm3:" &sm3

end if
b.MoveNext
    WEND
if Rno > count then
msgbox "record no found"
else
Avg=(sm1+sm2+sm3)/3
Msgbox "Avg:" & Avg
sql="update student set AvgMarks="&Avg&" where RollNo =" & RNo
'msgbox sql
a.Execute (sql)
Call Total_Marks(RNo,sm1,sm2,sm3,Avg)
end if
End Function

Function Total_Marks(RNo,sm1,sm2,sm3,Avg)
Total=sm1+sm2+sm3
msgbox "Total:" & Total
sql="update student set Total="&Total&" where RollNo="&RNo
'msgbox sql
a.Execute (sql)
Call Address_value(RNo,sm1,sm2,sm3,Avg,Total)
End Function

Function Address_Value(RNo,sm1,sm2,sm3,Avg,Total)
    Address=inputbox("Enter the address")
  msgbox "Address:" & Address
  sql="update student set AvgMarks="&Avg&",Total="&Total&",Address='"&Address&"' where RollNo="&RNo
msgbox sql
a.Execute (sql)
a.Close
End Function