Solved

Execute vbscript code only if results of query exist

Posted on 2011-02-25
7
668 Views
Last Modified: 2012-05-11
I have a vbscript that currently emails a list of accounts that are past due.  I want to schedule the report to run every day, however, i don't want to receive the email if no results are found.  Can someone help me build in some logic to skip the email processing if no results are returned?

Thanks in advance
'Declare Constants
Const CDO_SCHEMA = "http://schemas.microsoft.com/cdo/configuration/"
Const CDO_MAIL_HEADER = "urn:schemas:mailheader:"

'Method used to send mail
Const CDO_SEND_USING_REMOTE = 2 'Send using Remote SMTP Server

'Security method used on remote SMTP server
Const CDO_ANONYMOUS = 0 'Use no authentication
Const CDO_BASIC = 1 'Use the basic (clear text) authentication
Const CDO_NTLM = 2 'Use the NTLM authentication

'Delivery Status Notifications
Const cdoDSNDefault = 0 'No DSN commands are issued
Const cdoDSNNever = 1 'No DSN commands are issued
Const cdoDSNFailure = 2 'Return a DSN if delivery fails
Const cdoDSNSuccess = 4 'Return a DSN if delivery succeeds
Const cdoDSNDelay = 8 'Return a DSN if delivery is delayed
Const cdoDSNSuccessFailOrDelay = 14 'Return a DSN if delivery succeeds, fails, or is delayed

'Set method of sending
strSendMethod = CDO_SEND_USING_REMOTE

'Remote SMTP Server Settings
strSmtpServer = "10.30.1.10" 'Name or IP of SMTP Server
intSmtpPort = 25 'SMTP Server Port; typically 25
intSmtpTimeout = 60 'Number of seconds to try establishing a connection to the SMTP Server
strAuthenticationMethod = CDO_ANONYMOUS

'SMTP Server Authentication - IF BASIC or NTLM; NOT needed for ANONYMOUS
strUserName = ""
strPassword = ""
strUserSSL = False 'True if SMTP Server uses SSL; False if Not

'Message Settings
strTo = "test@acme.com" 'Separate multiple addresses with a semi-colon (;)
strCC = ""
strBCC = ""
strFrom = "events@acme.com"
strSubject = "The following events are past due"
strBodyType = "TEXT" 'Message Body Format = HTML or TEXT -Text is Plain Text
strAttachment = "" 'Attachment Path i.e. C:\Temp\File.txt
strDSNotification = cdoDSNDefault 'Delivery Status Option Change as needed

'WScript.Echo "Connecting to database..."

'Connect to database & select all from Table
Set objDB = DBConnect()
Set oRS = objDB.Execute("SELECT DATE_FORMAT(eventdate, '%m.%d.%Y') as eventdate, eventtype, DATE_FORMAT(setuptime, '%r') as SETUPTIME, DATE_FORMAT(starttime, '%r') as STARTTIME, DATE_FORMAT(stoptime, '%r') as STOPTIME, downpaid, paidinfull from event where eventdate < date_add(curdate(), INTERVAL 14 DAY) and paidinfull='false' order by eventdate" )

'Dump Records from Table

strOutput = "EVENT DATE" & vbtab & "EVENT TYPE" & vbtab & vbtab & "SETUP TIME" & vbtab & "START TIME" & vbtab & "STOP TIME" & vbcrlf
nRec = 0

  ors.MoveFirst  
	Do Until ors.EOF
        strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab &  ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
        oRS.MoveNext    
  Loop
ors.close
'WScript.Echo "Sending Email..."

SendEmail strOutput

'WScript.Echo "Script Finished"

'This function sets up DB Connection using specified DSN
Function DBConnect
    Set objDB = CreateObject("ADODB.Connection")
    objDB.Open "vbrs"
    Set DBConnect = objDB
End Function

Sub SendEmail(strBody)
    'Create Objects
    Set objConfig = CreateObject("CDO.Configuration")
    Set objEmail = CreateObject("CDO.Message")
    
    'Prepare email configuration
    With objConfig.Fields
        .Item(CDO_SCHEMA & "sendusing") = strSendMethod
        .Item(CDO_SCHEMA & "smtpserver") = strSmtpServer
        .Item(CDO_SCHEMA & "smtpserverport") = intSmtpPort
        .Item(CDO_SCHEMA & "smtpconnectiontimeout") = intSmtpTimeout
        .Item(CDO_SCHEMA & "smtpauthenticate") = strAuthenticationMethod
        
        If.Item(CDO_SCHEMA & "smtpauthenticate") <> 0 Then
            .Item(CDO_SCHEMA & "sendusername") = strUsername
            .Item(CDO_SCHEMA & "sendpassword") = strPassword
            .Item(CDO_SCHEMA & "smtpusessl") = strUserSSL
        End If
        .Update
    End With
    
    'Create email and send
    With objEmail
        Set.Configuration = objConfig
        
        .To = strTo
        
        If strCC <> "" Then
            .CC = strCC
        End If
        
        If strBCC <> "" Then
            .BCC = strBCC
        End If
        
        .From = strFrom
        
        .Subject = strSubject
        
        If strBodyType = "HTML" Then
            .HTMLBody = strBody
        ElseIf strBodyType = "TEXT" Then
            .TextBody = strBody
        End If
        
        If strAttachment <> "" Then
            .AddAttachment strAttachment
        End If
        
        If strDSNotification <> 0 And strDSNotification <> 1 Then
            .Fields(CDO_MAIL_HEADER & "disposition-notification-to") = strFrom
            .Fields(CDO_MAIL_HEADER & "return-receipt-to") = strFrom
            .DSNOptions = strDSNotification
            .Fields.update
        End If
        .Send
    End With
End Sub

Open in new window

0
Comment
Question by:bmsande
  • 3
  • 2
  • 2
7 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 34980837
Replace:


  ors.MoveFirst  
	Do Until ors.EOF
        strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab &  ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
        oRS.MoveNext    
  Loop
ors.close
'WScript.Echo "Sending Email..."

SendEmail strOutput

Open in new window




with:



  ors.MoveFirst  
If Not ors.EOF Then
	Do Until ors.EOF
        strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab &  ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
        oRS.MoveNext    
  Loop
ors.close
'WScript.Echo "Sending Email..."

SendEmail strOutput
End If

Open in new window

0
 

Author Comment

by:bmsande
ID: 34980872
error

"Either BOF or EOF is true, or the current record has been deleted.  Requested Operation requires a current record."

0
 

Author Comment

by:bmsande
ID: 34980927
ok, it seems to be an issue with my original code as well.  If no records are selected, then it throws that error.  any ideas?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 33

Expert Comment

by:it_saige
ID: 34981052
One thing you might try (not the cleanest solution mind you):
'Dump Records from Table

strOutput = "EVENT DATE" & vbtab & "EVENT TYPE" & vbtab & vbtab & "SETUP TIME" & vbtab & "START TIME" & vbtab & "STOP TIME" & vbcrlf
' Did not see this variable being used, assuming it is a record count.
nRec = 0

  ors.MoveFirst  
	Do Until ors.EOF
        strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab &  ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
        nRec = nRec + 1
        oRS.MoveNext    
  Loop
ors.close
'WScript.Echo "Sending Email..."

' Assuming that nRec is 1 simply because the loob above may run through once before failing out.  2 would 
' be true in the event of 1 record returned since nRec would be equal to 2 as the loop would fail out on the
' second loop through.
If nRed >= 2 Then
        SendEmail strOutput
End If

Open in new window

HTH,

-saige-
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 500 total points
ID: 34981155
If you simply comment out ors.MoveFirst, does it work correctly?
0
 
LVL 33

Expert Comment

by:it_saige
ID: 34981364
Dismiss my last, try this instead:
If Not ors.BOS Then ors.MoveFirst  
If Not ors.EOF Then
	Do Until ors.EOF
        strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab &  ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
        oRS.MoveNext    
  Loop
ors.close
'WScript.Echo "Sending Email..."

SendEmail strOutput
End If

Open in new window

HTH,

-saige-
0
 
LVL 33

Expert Comment

by:it_saige
ID: 34981375
Typo:
If Not ors.BOF Then ors.MoveFirst  
If Not ors.EOF Then
	Do Until ors.EOF
        strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab &  ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
        oRS.MoveNext    
  Loop
ors.close
'WScript.Echo "Sending Email..."

SendEmail strOutput
End If

Open in new window

HTH,

-saige-
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now