Example if Bank Table: Insert Query in VBScript

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

Create a Bank Table with following fields,

a.       Sr No

b.      Name

c.       Account No

d.      Address

e.      Debit

f.        Credit

g.       Total

Write a script to perform below task.

a.       Write a function to insert a new entry for Debit and it should update total field also.

b.      Write a function to insert a new entry for credit and it should update total field also.

Write a function to update address of user choice Account
'=================================================================

Dim MyConn,cmd,rs
set MyConn= CreateObject("ADODB.Connection")

connectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "data source=D:\bankDB.accdb;Persist Security Info=False;"

MyConn.Open connectionString
Set adx = CreateObject("ADOX.Catalog")
set adx.ActiveConnection = MyConn

initialdebitVal = 0
initialTotalVal = 0
initialCreditVal = 0
userAcctNo = 0
user_exists=false

id=Inputbox ("Enter your Choice 1:debit amount 2:credit amount 3: Change Address 4: New Entry")
Select Case id
case 1: call debitAmt
case 2: call creditAmt
case 3: call changeAddr
case 4: call newEntry
End Select
MyConn.close

function getAmtVals
sInsData = "select * from Bank where Account = " & userAcctNo
MyConn.Execute(sInsData)
Set RS = MyConn.Execute(sInsData)

WHILE NOT RS.EOF
initialdebitVal =  RS("Debit")
initialCreditVal = RS("Credit")
initialTotalVal = RS("Total")

user_exists=true
RS.MoveNext
WEND

RS.Close
set RS = nothing
end function


'function to debit amount from the account entered
function debitAmt

userAcctNo = Inputbox ("Enter the Account number")
call getAmtVals
'validating account exists or not
if user_exists=false  then
msgbox "Account not found"
exit function
end if

debitVal=Inputbox ("Enter the debit amount")

totalVal =   initialTotalVal - debitVal
sInsData = "update Bank  set Debit = " & debitVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
sInsData = "update Bank  set Total = " & totalVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
msgbox "Debited Successfully"

end function


'function to credit amount to the account entered
function creditAmt

userAcctNo = Inputbox ("Enter the Account number")
call getAmtVals
'validating account exists or not
if user_exists=false  then
msgbox "Account not found"
exit function
end if

creditVal=Inputbox ("Enter the credit amount")
totalVal =   initialTotalVal + creditVal
sInsData = "update Bank  set Credit = " & creditVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
sInsData = "update Bank  set Total = " & totalVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
msgbox "Credited Successfully"

end function


'function to update date to the account entered
function changeAddr

userAcctNo = Inputbox ("Enter the Account number")
call getAmtVals
'validating account exists or not
if user_exists=false  then
msgbox "Account not found"
exit function
end if

address=Inputbox ("Enter the new address value")
sInsData = "update Bank  set address = '" & address & "'" & " where account=" & userAcctNo
MyConn.Execute(sInsData)
msgbox "Address Updated Successfully"

end function


'function to add new entry
function newEntry

hName=Inputbox ("Enter the Account Holder Name")
hAccount=Inputbox ("Enter the Account number")
hAddress=Inputbox ("Enter the Address")
sql = "insert into Bank(HName,Account,address,debit,credit,total) values('" & hName & "',"& hAccount & ",'" & hAddress & "',0,0,0);"
MyConn.Execute(sql)
msgbox "User Added Successfully!!!!"
end function