I am creating a data access component that will return a recordset providing that the argument for the function exists in the database. The function will be called inside of another that will return an ASP string depending upon the recordset and another variable. However, I wanted to test to verify that the first function actually was returning a recordset and, if not, have it produce an error message in the second function. I cannot seem to test for it being an empty recordset and am at a loss as to how best to conduct this. Any ideas?
Here's my code below:
Public Function GetProperty(PropertyID As String, Page As String) As String
Dim rs As ADODB.Recordset
Set rs = GetPropertyData(PropertyID)
If rs Is Null Then
GetProperty = "This record does not exist"
Else
GetProperty = BuildTable(Page, PropertyID)
End If
End Function
Public Function GetPropertyData(PropID As String) As ADODB.Recordset
Dim strSQL As String
Dim rs As ADODB.Recordset
strSQL = "SELECT prpty_id, actv_flg, prpty_nm, co_cd, cstctr_cd FROM T_PRPTY WHERE " & _
"prpty_id = " & PropID
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.Source = strSQL
.ActiveConnection = "Driver={SQL Server};Server=(servername);Database=(dbname);uid=(userid);password=(password)"
.Open
'Disconnect the recordset now that it's open.
Set .ActiveConnection = Nothing
End With
If rs.BOF And rs.EOF Then
Set rs = Nothing
Set GetPropertyData = Nothing
Exit Function
End If
Set GetPropertyData = rs
exithandler:
Set rs = Nothing
Exit Function
End Function Insanity is merely a state of mind while crazy people have a mind of their own.
Here's my code below:
Public Function GetProperty(PropertyID As String, Page As String) As String
Dim rs As ADODB.Recordset
Set rs = GetPropertyData(PropertyID)
If rs Is Null Then
GetProperty = "This record does not exist"
Else
GetProperty = BuildTable(Page, PropertyID)
End If
End Function
Public Function GetPropertyData(PropID As String) As ADODB.Recordset
Dim strSQL As String
Dim rs As ADODB.Recordset
strSQL = "SELECT prpty_id, actv_flg, prpty_nm, co_cd, cstctr_cd FROM T_PRPTY WHERE " & _
"prpty_id = " & PropID
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.Source = strSQL
.ActiveConnection = "Driver={SQL Server};Server=(servername);Database=(dbname);uid=(userid);password=(password)"
.Open
'Disconnect the recordset now that it's open.
Set .ActiveConnection = Nothing
End With
If rs.BOF And rs.EOF Then
Set rs = Nothing
Set GetPropertyData = Nothing
Exit Function
End If
Set GetPropertyData = rs
exithandler:
Set rs = Nothing
Exit Function
End Function Insanity is merely a state of mind while crazy people have a mind of their own.