Every so often, I need to put together a script to do some simple database queries or updates and I can never remember the proper format of the connection string and I have to go look it up somewhere. So this post is an attempt to solve that for myself. I figure I will always be able to find this sample, regardless of how I organize or reorgnize my local storage.

This is a simple MSSQL sample that performs a count based on a where clause.

[More:]


'--------------------------------------------------------------------
'-- define the userid, password, server, and table that will be used
'-- for the query
'--------------------------------------------------------------------
dim dbUID, dbPswd, dbServer, dbTable

dbUID = "Your Database User ID"
dbPswd = "Your Database Password"
dbServer = "The Machine where the Database lives"
dbTable = "The Database Table that will be queried"

'--------------------------------------------------------------------
'-- create the connection object
'--------------------------------------------------------------------
dim objCXN
Set objCXN = createobject("ADODB.Connection")

'--------------------------------------------------------------------
'-- set up the connection string, we are using not using a dsn in
'-- this example
'--------------------------------------------------------------------
dim strCXN
strCXN = "Provider=SQLOLEDB.1;User ID='" & dbUID & "';Password='" & dbPswd & _
"';Data Source=" & dbServer & ";Initial Catalog=" & dbTable & ";"

'--------------------------------------------------------------------
'-- try to open the connection to the database
'--------------------------------------------------------------------
objCXN.Open strCXN

'--------------------------------------------------------------------
'-- try to open the connection to the database
'--------------------------------------------------------------------
dim RS
set RS = CreateObject("ADODB.RecordSet")

'--------------------------------------------------------------------
'-- build the query to issue against the database
'-- replace [TABLE_NAME], [SOMETHING], && [SOMEVALUE] with the appropriate
'-- value for your query
'-- for example:
'-- SELECT count(*) as count FROM dbo.MyTestTable WHERE LastName='smith'
'--------------------------------------------------------------------
dim strQuery
strQuery ="SELECT count(*) as count FROM [TABLE_NAME] WHERE [SOMETHING]='[SOME VALUE]'"

'--------------------------------------------------------------------
'-- execute the query
'--------------------------------------------------------------------
set RS = objCXN.execute(strQuery)

'--------------------------------------------------------------------
'-- just a simple echo of the count we performed during the SQL query
'--------------------------------------------------------------------
wscript.echo RS.Fields("count")

'--------------------------------------------------------------------
'-- destroy the recordset object
'--------------------------------------------------------------------
set RS = nothing

'--------------------------------------------------------------------
'-- close the connection object and destroy it
'--------------------------------------------------------------------
objCXN.Close
Set objCXN = Nothing


I hope this post helped you out. If it didn't, I am always looking for new scripts to add so submit a request for your question or need and I will see if I can answer it.