Integration Manager Script Problem

[greg52]

I have a script that successfully queries SQL for the employee id based on the SSN. However, the problem is in trying to do some error handling code. When testing for the record count returned, which should be either 0 or 1, the Recordcound property always returns a negative 1 (-1). Does anybody see what I'm doing wrong? Thanks.

'Create a new connection
Set pConnection = CreateObject("ADODB.Connection")

'Open the connection to the database
Call pConnection.Open("DARWIN7SQL", "query2", "qu23ex")

'Create a new recordset -- that will hold returned data
Set pRecordset = CreateObject("ADODB.Recordset")
ssn = SourceFields("SSN")

'Build a string containing your SQL Select Statement
sSQL = "USE TST99 SELECT UPR00100.SOCSCNUM, UPR00100.EMPLOYID from UPR00100 where UPR00100.SOCSCNUM = '"
sSQL = sSQL & SourceFields("SSN") & "'"

'MsgBox ("The SQL statement is: " & sSQL)

'Open the recordset
Call pRecordset.Open(sSQL, pConnection, 3, 1)

'Test for record not found condition. 'If not found, log the SSN and set flag to cancel integration.
MsgBox pRecordSet.Recordcount

Select Case pRecordset.Recordcount
Case 0
MsgBox "Integration failed. Employee(s) not found. Check log"
Call pRecordset.Close
Call pConnection.Close
CancelIntegration "The last employee SSN " & ssn & " was not found. " & _
"Unable to determine EmployeeID."
Case Else
End Select

' If pRecordset.RecordCount = 0 then
' Call pRecordset.Close
' Call pConnection.Close
' MsgBox "Integration failed. Employee(s) not found. Check log"
' CancelIntegration "The last employee SSN " & ssn & " was not found. " & _
' "Unable to determine EmployeeID."
' End If

xx = pRecordset("EMPLOYID")

DestinationFields("Employee ID") = xx
Msgbox DestinationFields("Employee ID")

'Close recordset when finished
Call pRecordset.Close

'Close connection when finished
Call pConnection.Close

 

[greg52]

Update - I was able to work around the problem.