Check if recordset is populated

[Baz_Singh]

Hi,

I am trying to create a vb script in DTS that will succeed if the recordset is empty and will fail (and call an email function) if the recordset has any records. I am trying to use a if statement but not sure if am on the right path or there is a better way of doing this.

Would appreciate if someone could assist.


Dim connEWORK
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adOpenDynamic = 2
Const adOpenKeyset = 1


Function Main()

Dim SQL
Dim strRecipients
Dim CheckForDiscrepancy

Set connEWORK = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset") connEWORK.Provider = "FGDSGDSGDS" connEWORK.Properties("Data Source").Value = "DGDSGDSGDSG" connEWORK.Properties("Initial Catalog").Value ="e-DGFSDSGDSGF" connEWORK.Properties("User ID").Value = "ewDSFGDDSork" connEWORK.Properties("Password").Value = "DSFGDSGDS" connEWORK.Properties("Integrated Security").Value = "FDGDSGDG" connEWORK.ConnectionTimeout = 5 connEWORK.commandtimeout = 5

connEWORK.Open
SQL = "select * from dbo.MDS_DuplicateRecords"
RS.Open SQL, connEWORK, adOpenKeyset, adLockReadOnly, adCmdText
RS.Close Set RS = Nothing
connEWORK.Close Set connEWORK = Nothing

If (RS isnull) Then
Main = DTSTaskExecResult_Success

Else

Main = DTSTaskExecResult_Failure

strRecipients = "FDSGDSFGDSGFDFG"

Call sendEmail(strRecipients)

End If

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Name: Function Send_Email

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function sendEmail(strRecipients)

Dim iMsg

Set iMsg = CreateObject("CDO.Message")

With iMsg

.To = strRecipients
.From = "HAHA@HAHA"
.Subject = "NNNNNNNNNNNNNNNNNNNN"

' Content of email

.TextBody = "NNNNNNNNNNNNNNNNNNNNN" & vbCrLf & vbCrLf &_
"NNNNNNNNNNNNNNNNNNNNNNNNNNN'" & vbCrLf & vbCrLf &_
"NNNNNNNNNNNNNNNNNNNNNNN"

.Send

End With

Set iMsg = Nothing

End Function

[GaryMazzone]

connEWORK.Open
SQL = "select * from dbo.MDS_DuplicateRecords"
RS.Open SQL, connEWORK, adOpenKeyset, adLockReadOnly, adCmdText

If rs.BOF and rs.EOF Then
Main = DTSTaskExecResult_Success

Else

Main = DTSTaskExecResult_Failure

strRecipients = "FDSGDSFGDSGFDFG"

Call sendEmail(strRecipients)

End If

RS.Close Set RS = Nothing
connEWORK.Close
Set connEWORK = Nothing

[Baz_Singh]

Thanks Gary. I'll give this a go and let you know how i get on.