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

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

Example of ADODB.Connection and ADODB.Recordset in VBscript

    Set MyConn = CreateObject("ADODB.Connection")
    MdbFilePath = "C:\Users\ladn\Desktop\Neeraj.mdb"
    MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"

 
    SQL_query = "SELECT * from Student"
 
     set rs=CreateObject("ADODB.recordset")
     rs.Open SQL_query, MyConn
 
    count = 0
    WHILE NOT RS.EOF
count = count + 1
name = rs("name")
add = rs("address")
msgbox name & add
       rs.MoveNext
    WEND
msgbox count
    rs.Close
    set rs = nothing
    MyConn.close
    set MyConn = nothing

ODBC Connection for MS Access

• Click on Start button
• Select Control Panal
• Click on Administrator Tools
• Click on Data Sources (ODBC)
• ODBC Data Source Administrator window will open as below screenshot

• To Add MS Access User Data Source, Click on Add button
• Create New Data Source window will open

• Select “Diver do Microsoft Access (*.mdb)”  from Create New Data Source window and click on finish button.
• ODBC Microsoft Access Setup Window will open

• Write Data Source Name and Description if need,
• Click on Select button  and select mdb file from the location (see below screenshot)

• Click on OK button after selecting mdb file

• Database will display under ODBC Microsoft Access Setup window and click on OK button

• MS Access User DSN Will display MS Access Driver name.

Reverse String without using any String function

Dim str1,regExpObj,x,result
Dim ArrString
str1="Neeraj"

Set regExpObj = new regexp
regExpObj.pattern="[a-z A-Z]"
regExpObj.global=true
set ArrString=regExpObj.execute(str1)
For each x in ArrString
result = x.value & result
Next
msgbox result

Search entered text from inside of the text file.

set fso=CreateObject("Scripting.FileSystemObject")
set f=fso.OpenTextFile("Path_of_the_text_file.txt",1, False)
val=Inputbox("enter the word to be searched")
flag = false
do until f.AtEndOfStream
str1=f.readLine
if inStr(str1,val) then
flag = True
exit do
else
flag = false
End if
Loop
if flag = True then
msgbox val & " found"
else
Msgbox val & " not Found"
f.close
set f=nothing
set fso=nothing

Find no is Prime or not from range in vbscript

Function primeNumber()
flag=1
primeNo=inputbox("Enter a number")
For n=2 to primeNo
flag=1
For j=2 to n/2
If n mod j=0 Then
flag=0
End If
Next
If flag=1 Then
msgbox "prime no is:" & n
End If
Next
End Function

check enter string or number is Palindrome or not in Vbscript

Function CheckPalindrom2StringFunction()

MyStr=Ucase(inputbox("Enter the String:"))
RevStr=strreverse(MyStr)

if strcomp(MyStr,RevStr)=0 then
  msgbox "It is a Palindrome"
else
  msgbox "It is not a Palindrome"
end if
End Function

Function CheckPalindromeString()
Word = Ucase(inputbox("Enter the String:"))
length = Len(Word)
For i = 1 To length
Str1 = Str1 + Mid(Word, i, 1)
Next
For i = length To 1 Step -1
Str2 = Str2 + Mid(Word, i, 1)
Next

If Str1 = Str2 Then
MsgBox "given word is palindrome"
Else
MsgBox "given word is not palindrome "
End If
End Function

Function CheckPalindromeInt()
     n = InputBox(" Enter a number=")
     temp = n
     rev = 0
      Do While temp > 0
         r = temp Mod 10
         rev = rev * 10 + r
         temp = Int(temp / 10)
      Loop
     If Int(n) = rev Then
        MsgBox "The palindrom number is=" & rev
     Else
       MsgBox "Please enter correct palindrom number"
     End If
End Function

Find the Factorial without and with recursive

Function Fact()
n = int(InputBox(" Enter a number="))
' Coding for normal execution
f = 1
for i = 1 to n
f = f * i
next
msgbox f
'--------------------

' Coding for recursive logic
REM if n < 0 then
REM msgbox "Invalid input"
REM else
REM f = FactNumber(n)
REM msgbox "Factorial of " & n & " is " & f
REM end if

End Function

'------------------------------
' Recursive Function
Function FactNumber(n)

if n = 0 then
FactNumber = 1
else
FactNumber = n * FactNumber(n-1)
end if
End Function

Print * triangle in VBscript

Function printStar()
maxLen = int(InputBox(" Enter a number="))

REM for i = 0 to n
REM print Space(n - i) + String(i, "*") + vbNewLine
REM next

For lineLen = 1 To maxLen
iSpaces = (maxLen - lineLen)
If iSpaces > 0 Then
padSpace = Space(iSpaces / 2)
x = x & padSpace & Replace(Space(lineLen), Space(1), "*") & padSpace & VbCrLf
Else
x = x &  Replace(Space(lineLen), Space(1), "*") & VbCrLf
End If
Next
msgbox x
End Function

O/P: n=5
 
       *
      **
     ***
    ****
   *****

Program to find Fibonacci series

Function Fibonacci()
dim a,b,c
        n = 20
a=0
b=1
do while c<=n
x = x & b
c=a+b
a=b
b=c
loop
msgbox x
end Function

Program that reverses the order of words (not characters) in a sentence. e.g: “Good Morning Everybody” to “Everybody Morning Good”

 
    X = 0
    inString = "Good Morning Everybody"
    inStringArr = Split(inString)
    inStringArrLen = UBound(inStringArr)
    ReDim strArr(inStringArrLen)
    For i = 0 To Len(inString)
        aChar = Mid(inString, i + 1, 1)
        If aChar = " " Or i = Len(inString) Then
            strArr(X) = tempStr + " "
            tempStr = Empty
            X = X + 1
        Else
            tempStr = tempStr + aChar
        End If
     
    Next
    ReDim revArr(inStringArrLen)
    i = 0
    For j = UBound(strArr) To 0 Step -1
        revArr(i) = strArr(j)
        i = i + 1
    Next
    MsgBox Join(revArr)