VBS script help

reefoid · 25 Jun 2013 at 12:27

I'm trying to create a script that will email the results of a mysql query using VBS. I don't have any experience of VBS but have found a script online that will do what I want. However, I only want the script to email results if rows are actually returned. If the query returns zero rows then I want the script to end before emailing anything. Here is the script I'm using (some details redacted for security), can anyone advise on how I can stop the mail sending if zero rows are returned.

Thanks.

Code:

option explicit
dim dbHost, dbName, dbUser, dbPass, outputFile, email, emailFrom, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbHost = "localhost"
dbName = "xxxx"
dbUser = "xxxx"
dbPass = "xxxx"
outputFile = "c:\inetpub\wwwroot\dev\bs\batch\Scheduled Works.csv"
email = "xxxx"
emailFrom = "xxxx"
  subj = "Scheduled Works"
  body = "Body text"
  smtp = "xxxx"
  smtpPort = 25
sqlStr = "SELECT tasks.task_ref AS REF, clients.client_name AS CLIENT, tasks.task_address AS ADDRESS, tasks.task_description AS DESCRIPTION, DATE_FORMAT(tasks.task_next_date,'%d/%m/%Y') AS DUE_DATE FROM tasks, clients WHERE tasks.task_client = clients.client_ref AND DATEDIFF (tasks.task_next_date, CURRENT_DATE) <= 14;"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount
	set a = fso.createtextfile(fPath)
	
	set showList = conn.execute(sqlstr)
	for intcount = 0 to showList.fields.count -1
		if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		else
			a.write """" & showList.fields(intcount).name & """"
		end if
	next
	a.writeline ""
	
	do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	loop
	showList.close
	set showList = nothing

	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = subj
	objMessage.From = emailFrom
	objMessage.To = email
	objMessage.TextBody = "There are scheduled works due, please see the attached spreadsheet for details."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

GravyMonster · 25 Jun 2013 at 12:33

With the structure you have there the best thing would be to return the row count from your MakeDataFile function, and then stick the email logic in an if block testing it's > 0.

Alternatively, put the email logic in it's own function and call that from within MakeDataFile when not showlist.EOF.

Brian The Snail · 25 Jun 2013 at 20:38

reefoid said:
... advise on how I can stop the mail sending if zero rows are returned.

New code below. Wraps everything in

Code:

 if not(showList.eof) then
...
end if

including creation of the text file.

Code:

option explicit
dim dbHost, dbName, dbUser, dbPass, outputFile, email, emailFrom, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbHost = "localhost"
dbName = "xxxx"
dbUser = "xxxx"
dbPass = "xxxx"
outputFile = "c:\inetpub\wwwroot\dev\bs\batch\Scheduled Works.csv"
email = "xxxx"
emailFrom = "xxxx"
  subj = "Scheduled Works"
  body = "Body text"
  smtp = "xxxx"
  smtpPort = 25
sqlStr = "SELECT tasks.task_ref AS REF, clients.client_name AS CLIENT, tasks.task_address AS ADDRESS, tasks.task_description AS DESCRIPTION, DATE_FORMAT(tasks.task_next_date,'%d/%m/%Y') AS DUE_DATE FROM tasks, clients WHERE tasks.task_client = clients.client_ref AND DATEDIFF (tasks.task_next_date, CURRENT_DATE) <= 14;"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount

	set showList = conn.execute(sqlstr)

        if not(showList.eof) then
	    set a = fso.createtextfile(fPath)

	    for intcount = 0 to showList.fields.count -1
		    if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		    else
			a.write """" & showList.fields(intcount).name & """"
		end if
	    next
	    a.writeline ""
	
	    do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	    loop
	end if

	showList.close
	set showList = nothing
	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = subj
	objMessage.From = emailFrom
	objMessage.To = email
	objMessage.TextBody = "There are scheduled works due, please see the attached spreadsheet for details."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

VBS script help

reefoid

reefoid

GravyMonster

GravyMonster

Brian The Snail

Brian The Snail