Test for Empty Recordset 1

[Chopstik]

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.

 

[AndyDuke]

ChopStik,

I simply check for (rs.EOF = True) after executing the SQL command. If the recordset has returned a record then EOF will be false.


Andy.

 

[Chopstik]

Makes sense, so I tried that. My code works fine so long as the record exists. But when I try to test for a record I know doesn't exist, I receive a "Run-time error 91: Object variable or With block variable does not exist". Darned if I know why. It is almost like it doesn't recognise the recordset. My testing code simply opens the .dll, then runs the GetProperty function to get the string back. This can be really vexing, and I'm only trying to prevent problems... Insanity is merely a state of mind while crazy people have a mind of their own.

 

[AndyDuke]

ChopStik,

Should the function GetProperty read: -

Public Function GetProperty(PropertyID As String, Page As String) As String
Dim rs As ADODB.Recordset
Set rs = GetPropertyData(PropertyID)
If (rs Is Nothing) Then
GetProperty = "This record does not exist"
Else
GetProperty = BuildTable(Page, PropertyID)
End If
End Function


The test should be for Nothing and not Null.

Andy.

 

[Chopstik]

Would you believe I had done that several times and never gotten it to work, kept saying that I could not use "Nothing" unless I was setting it to nothing. Instead, just needed to add the parentheses () to it and that solves the problem... There are days I truly dislike programming. ;-)

Thanks very much for your help! Insanity is merely a state of mind while crazy people have a mind of their own.

 

[Chopstik]

Ooops, never mind, now I get it...

rs is nothing
NOT
rs = nothing

The more I learn... the more I need to learn... Insanity is merely a state of mind while crazy people have a mind of their own.