Sunday, November 22, 2015

Quick Language Reference: VBScript

These are old notes that were initially for making edits to VBScript ASP pages (before .Net), though most the VBScript syntax will carry over to windows shell scripting (if you want to use VBScript instead of DOS-style syntax).  The last few examples are for Windows shell.

echo

    Wscript.Echo "hello world"

execute vbscript on command line

    rem This is the command line version
    cscript "C:\Users\guest\Desktop\123\MyScript.vbs"
    OR

    rem This is the windowed version
    wscript "C:\Users\guest\Desktop\123\MyScript.vbs"

ASP page def, char encoding

    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

require explicit variable declaration

    Option Explicit

include file

    <!--#include file="z_common.asp"-->

declare variable

    Dim lstrID

comment

    ' this is a comment

multiline comments

    ' bwa hahaha
    '   hahaha
    '   hahaha !
    ' (evil laugh)

string concatenation

    Dim lstrSQL;

    lstrSQL = "UPDATE " & YOURTABLE & ""
    lstrSQL = lstrSQL & " " & "SET"
    lstrSQL = lstrSQL & " " & "  AccountNumber = '" & replaceForDB(lstrAccountNum) & "',"
    lstrSQL = lstrSQL & " " & "  ContactType = '" & replaceForDB(lstrContactType) & "',"
    lstrSQL = lstrSQL & " " & "  Comments = '" & replaceForDB(lstrComments) & "',"
    lstrSQL = lstrSQL & " " & "  NextContactDate = #" & replaceForDB(lstrNextContactDate) & "#"
    lstrSQL = lstrSQL & " " & "WHERE"
    lstrSQL = lstrSQL & " " & "  CallID = " & replaceForDB(lstrID) & ""


if, else if,  else

    If ((lstrAction = "edit") AND (isAdmin(gstrUsername) = 1)) Then


    ElseIf ((lstrAction = "delete") AND (isAdmin(gstrUsername) <> 1)) Then

        ' example

    Else

        Response.Write("Unknown action or unauthorized user passed to call detail action page!")
        Response.End

    End If

form vars

    if (request.Form("submit")="Submit") Then

print

    Response.Write("Unknown action or unauthorized user passed to call detail action page!")
    Response.End

function

    Function replaceForDB(argstrToReplace)
    ' PASS    : String to replace
    ' RETURNS : String with baddies removed

        Dim lstrReturn

        If (IsNull(argstrToReplace) <> True) Then
            lstrReturn = Replace(argstrToReplace,"'","''")
        Else
            lstrReturn = ""
        End If

        ' return by setting function name
        replaceForDB = lstrReturn

    End Function 'replaceForDB


redirect

    Response.Redirect("test.asp?id=" & id)

date math

    thisMonth = Now
    nextMonth = DateAdd("m", +1, thisMonth)
    nextYear = DateAdd("yyyy", +1, thisMonth)

    firstDay = dateFormatYYYYMMDD(DateSerial(Year(thisMonth), Month(thisMonth), 1))
    firstDayNextMonth = dateFormatYYYYMMDD(DateSerial(Year(nextMonth), Month(nextMonth), 1))
    firstDayNextYear = dateFormatYYYYMMDD(DateSerial(Year(nextYear), Month(nextYear), 1))

    Wscript.Echo firstDay
    Wscript.Echo firstDayNextMonth
    Wscript.Echo firstDayNextYear


    Function dateFormatYYYYMMDD(myDate)
         d = lPad(Day(myDate), 2, "0")
         m = lPad(Month(myDate), 2, "0")
         y = Year(myDate)
         dateFormatYYYYMMDD= y & "-" & m & "-" & d
    End Function


    Function lPad(s, l, c)
      Dim n : n = 0
      If l > Len(s) Then n = l - Len(s)
      lPad = String(n, c) & s
    End Function


DB connection strings


    ' trusted connection
    Const DB_CONNECT_STRING = "Driver={SQL Server};server=YOURSERVER;Database=YOURTABLE;Trusted_Connection = yes;"


    ' user password

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=YOUR_HOST;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASSWORD;"


execute query

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")

    objConnection.Open DB_CONNECT_STRING

    Set objCommand = CreateObject("ADODB.Command")
    objCommand.ActiveConnection = objConnection

    objCommand.CommandText = strScript    ' executes first script
    objCommand.Execute

    objConnection.Close


database select/loop

    Set objRS = Server.CreateObject("ADODB.Connection")
    strSQL = "SELECT * FROM news WHERE news_id= " & news_id & ""
    objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly

    ' Loop through the recordset and populate the list
     Do Until objRS.EOF = True
      headline  = objRS("news_headline").Value
      copy  = objRS("news_copy").Value
      datetime  = objRS("news_date").Value
     objRS.Movenext
     Loop

    objRS.Close


transaction

    Dim lstrDBError
    lstrDBError = ""
    If lobjDBConn.Errors.Count <> 0 then
         For Each lobjDBErr In lobjDBConn.Errors
             lstrDBError = lstrDBError & "" & lstrDBError.Number & " - " & lstrDBError.Description & "<br>" & CHr(13)
         Next
    End If

    If (lstrDBError = "") Then
        lobjDBConn.CommitTrans
    Else
        lstrDBError = "<br><strong>An error has occured:<br>" & lstrDBError & "</strong><br>" & Chr(13)
        lobjDBConn.RollbackTrans
        Response.Write(lstrDBError)
        Response.End
    End If

    lobjDBConn.Close
    Set lobjDBConn = Nothing


for loop

    For i = 0 To UBound(ArrayOfValues)
        Response.Write "Value " & i & " is " & ArrayOfValues(i) & "<br>"
    Next


constants

    Const filename = ".\ILCOLL.csv"

split string

    sVal = Request.Form("Listbox")
    ''sVal = "Joe, Bob, Tom, Phil, Toby" for argument's sake

    ArrayOfValues = Split(sVal, ", ")

    For i = 0 To UBound(ArrayOfValues)
    Response.Write "Value " & i & " is " & ArrayOfValues(i) & "<br>"
    Next


loop over text file

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set listFile = fso.OpenTextFile("list.txt")
    do while not listFile.AtEndOfStream
        fName =  listFile.ReadLine()
        fso.CreateTextFile(fName + ".txt")
    loop


str replace

    Replace(string,find,replacewith[,start[,count[,compare]]])


escape

    You can escape by doubling the quotes

    g="abcd """ & a & """"
    or write an explicit chr() call

    g="abcd " & chr(34) & a & chr(34)


scrape webpage


  ' Create an xmlhttp object:
  With CreateObject("MSXML2.XMLHTTP")
    .open "GET", sURL, False
    .send
    Do Until .readyState = 4 : Wscript.Sleep 50 : loop
    sPageText = .responseText
    ' or responseHTTP, or responsebody (BIN)
  end with

  if Instr(sPageText, "Your Match String") > 0 Then
    with CreateObject("Scripting.FileSystemObject)
      with .OpenTextFile("C:\Somewhere\logfile.log, 8)
        .writeline "Found Match: : & Now()
      end with
    end with
  end if



complete example - update dates in table

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const DB_CONNECT_STRING = "Driver={SQL Server};server=YOURSERVER;Database=YOURDATABASE;Trusted_Connection = yes;"
    Dim strScript
    Dim strScript2

    strScript = ""
    strScript2 = ""

    thisMonth = Now
    nextMonth = DateAdd("m", +1, thisMonth)
    nextYear = DateAdd("yyyy", +1, thisMonth)

    firstDay = dateFormatYYYYMMDD(DateSerial(Year(thisMonth), Month(thisMonth), 1))
    firstDayNextMonth = dateFormatYYYYMMDD(DateSerial(Year(nextMonth), Month(nextMonth), 1))
    firstDayNextYear = dateFormatYYYYMMDD(DateSerial(Year(nextYear), Month(nextYear), 1))

    strScript = "update YOURTABLE.dbo.payments set YOURFIELD = '" + firstDayNextMonth + "' where YOURFIELD =  CONVERT(DATETIME, '" + firstDay + "', 102) "
    strScript2 = "update YOURTABLE.dbo.payments set YOURFIELD = '" + firstDayNextYear + "' where YOURFIELD =  CONVERT(DATETIME, '" + firstDay + "', 102) "

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")

    objConnection.Open DB_CONNECT_STRING

    Set objCommand = CreateObject("ADODB.Command")
    objCommand.ActiveConnection = objConnection

    objCommand.CommandText = strScript
    objCommand.Execute

    objCommand.CommandText = strScript2
    objCommand.Execute

    objConnection.Close

    Function dateFormatYYYYMMDD(myDate)
         d = lPad(Day(myDate), 2, "0")
         m = lPad(Month(myDate), 2, "0")
         y = Year(myDate)
         dateFormatYYYYMMDD= y & "-" & m & "-" & d
    End Function


    Function lPad(s, l, c)
      Dim n : n = 0
      If l > Len(s) Then n = l - Len(s)
      lPad = String(n, c) & s
    End Function


execute com object

    Set ccObj = CreateObject("YOURDLL.CLASS")

    Dim somestring
    somestring = "some data"

    ccObj.InBuffer = somestring
    ccObj.Encrypt
    somestring = ccObj.OutBuffer

    Wscript.Echo pass


No comments: