checking database field names

10-25-2004, 08:00 AM
hi all, i have a foxpro table that i can connect to.
I need to put a handle for when the table cannot find the requested field name, the error it has is:

error :"40041 Object Collection: Couldn't find item indicated by text."

Can i put a check like

if isnull(rs!Date) then
Mydate = rs!date
end if

....but check if the field data cannot be found.

I have tried this but it comes up with the same error.

thanks in advance.

10-25-2004, 08:16 AM
The code is failing because, as you indicate, there's no field by that name to even check for null.

One way to check for existance of a field is to iterate through all the fields, looking at their names.

For example, here's a function (untested) that would check for existance of a field name in a recordset.

Function IsField (ByRef rsCheck as ADODB.Recordset, ByRef strFieldName as String) as Boolean
' Function returns TRUE if the field name is in the recordset

Dim N as Integer
Dim strFN as String

strFN = trim(strFieldName)

IsField = False
Do While N < rsCheck.Fields.Count-1
If strFN = rsCheck.Fields(N).Name then
IsField = True
End If
N = N + 1
Next N

Exit Function

Note; the function has no error checking for recordset state, so you'll need to ensure that the recordset is open before use, or add error checking (if rsCheck.State = adStateOpen then...)

And, by the way: a field named DATE is an inappropriate choice, as both VB and most database servers reserve that name as a function...

10-25-2004, 08:18 AM
I know someone will shoot me again for using “on error goto” code but it’s the only solution I know to solve this

Public Function CheckFieldName(rs As Recordset, fieldname As String) As Boolean
On Error GoTo ErrorHandler
Dim tstr As String
tstr = rs.Fields(fieldname).Name
CheckFieldName = True
Exit Function
CheckFieldName = False
End Function

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum