/// Frank Hagen: Professional Web Developer, C# User, Reformed Über-geek RSS 2.0
# Friday, September 21, 2007

Sometimes its necessary to script out data manipulation for deployments or automation.  The easiest vehicle in Windows is Windows Script Host (WSH) using VBScript.  Below is a very basic framework for accessing a database (MSSql) and looping a RecordSet.

''===// Retrieve Data from Database //=====
Const DBSERVER = "SQLDEV01"
Const DATABASE = "Cellular"
Const DBUSERID = "app_user"
Const DBPASSWD = "app_pass"

'' Construct ConnectionString
szADOConn = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Persist Security Info=True;" &_ 
            "Data Source=" & DBSERVER & ";" &_
            "DATABASE=" & DATABASE & ";" &_
            "User ID=" & DBUSERID & ";Password=" & DBPASSWD & ";" 

Set DBConn = CreateObject("ADODB.Connection")
    DBConn.open szADOConn        
    szSqlQuery = "SELECT PhoneID, Field2, etc  FROM Import_Name WHERE PhoneID <= 200"
Set cmd = CreateObject("ADOdb.Command")
    cmd.CommandType = &H0001  ''adCmdText
    cmd.ActiveConnection = DBConn
    cmd.CommandText = szSqlQuery
Set RecordSet = CreateObject("ADODB.Recordset")
Set RecordSet = cmd.Execute(szSQLlc)

Do While Not RecordSet.EOF
  WScript.Echo RecordSet("PhoneID")  
  RecordSet.movenext
Loop

Set RecordSet = Nothing
Set cmd = Nothing
    DBConn.close
Set DBConn = Nothing

Of Course, it's possible to distill this down further, but I didn't for clarity (Consts) and it's better to not use implicit command objects by opening the database directly with the Recordset object.

Friday, September 21, 2007 10:08:01 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] -
SQL
<%--
--%>
Statistics
Total Posts: 186
This Year: 0
This Month: 0
This Week: 0
Comments: 72
Locations of visitors to this page
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2010
Frank W Hagen
Sign In
All Content © 2010, Frank W Hagen
Custom DasBlog theme based on 'Business' by Christoph De Baene