Database-2-Web.vbs

From iMacros

This example is for reading from a database and populating a web page with the data. It calls the macro Wsh-Submit-2-Web.iim. This examples uses a MS ACCESS database, but the same method works with every scripting/programming language and with every database. You only need to edit the connection string to use a different database.

Visual Basic Script:

 'Internet Macros
 'Read from a Microsoft ACCESS database
 'This example requires the ADODB component (MDAC/ADO) from Microsoft
 'If you have ACCESS installed, this component is installed as well.
  
 Option Explicit
 dim rs, iim1, sql
 dim myname, mypath, connstring
 dim iret
 
 
 MsgBox "This script demonstrates how to read data from an ACCESS database and submit this information to a website. It reads
 from the database <IIM-TEST-SUBMIT.MDB> and uses the macro <wsh-submit-2-web>." + vbCrLf + VbCrLf + "Tip: This script has the
 same function as <file-2-web-method2.vbs> but reads the data from a database instead of a text file."
 
 
 ' find out current folder
 myname = WScript.ScriptFullName
 mypath = Left(myname, InstrRev(myname, "\"))
 
 
 ' open ACCESS database
 set rs = CreateObject("ADODB.Connection")
 
 'CONNECTION STRING => change this to use another database!
 connstring = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & mypath & "IIM-TEST-SUBMIT.MDB"
 
 'NOTE: Top open an SQL database instead of an ACCESS database please use:
 '
 'connstring = "driver={SQL 
Server};server=<SQLServerNameHere>;uid=<SQLUserIDHere>;pwd=<SQLPasswordHere>;database=databaseNameHere>"
 '
 
 rs.Open (connstring)
 
 ' use SQL to select information
 sql = "select * from table1"
 set rs = rs.Execute(sql)
 
 set iim1= CreateObject ("imacros")
 iret = iim1.iimInit
 iret = iim1.iimDisplay("Submitting Data from MS ACCESS")
 
 do until rs.eof
    'Set the variable
    iret = iim1.iimSet("-var_FNAME", rs.fields(0))
    iret = iim1.iimSet("-var_LNAME", rs.fields(1))
    iret = iim1.iimSet("-var_ADDRESS", rs.fields(2))
    iret = iim1.iimSet("-var_CITY", rs.fields(3))
    iret = iim1.iimSet("-var_ZIP", rs.fields(4))
    iret = iim1.iimSet("-var_STATE-ID", rs.fields(5))
    iret = iim1.iimSet("-var_COUNTRY-ID", rs.fields(6))
    iret = iim1.iimSet("-var_EMAIL", rs.fields(7))
    'Run the macro
    'Note: This is the SAME macro, as in the FILE-2-WEB-METHOD2.VBS example script!!!
    iret = iim1.iimPlay("wsh-submit-2-web")
    If iret < 0 Then
       MsgBox iim1.iimGetLastError()
    End If
   rs.movenext
 loop
 
 iret = iim1.iimDisplay("Done!")
 iret = iim1.iimExit
 WScript.Quit(0)

Macro code for Wsh-Submit-2-Web.iim:

 VERSION BUILD=300705  
 TAB T=1     
 TAB CLOSEALLOTHERS     
 URL GOTO=http://www.iopus.com/imacros/demo/v5/f2/automatic-data-entry.asp        
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:FNAME CONTENT={{FNAME}}
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:LNAME CONTENT={{LNAME}}
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:ADDRESS CONTENT={{ADDRESS}}
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:CITY CONTENT={{CITY}} 
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:ZIP CONTENT={{ZIP}}
 'Tip: Use the string ($) instead of the index to select the state and country 
 'Index would be the position of an entry in the combo box list, e. g. 161 for United States
 'The $ option is more robust against changes of the combo box content
 'Note * is used to ignore leading and trailing blanks that could be in the input data
 TAG POS=1 TYPE=SELECT FORM=NAME:WebDataEntry ATTR=NAME:STATEID CONTENT=$*{{STATE-ID}}*
 TAG POS=1 TYPE=SELECT FORM=NAME:WebDataEntry ATTR=NAME:COUNTRYID CONTENT=$*{{COUNTRY-ID}}*
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:EMAIL CONTENT={{EMAIL}}
 TAG POS=1 TYPE=INPUT:SUBMIT FORM=NAME:WebDataEntry ATTR=NAME:SendButton&&VALUE:Click<SP>to<SP>submit<SP>data  
 TAG POS=1 TYPE=A ATTR=TXT:Back