check if field exists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smk23 - 31 Dec 2005 12:16 GMT

I open an ADODB recordset rs and want to determine if a particular field is
present in the recordset. For example rs.Fields("ClientID"). I want to do
something if the field is in the recordset and move on if not. How can I do
that?

Thanks so much!!

Signature

sam

Reply to this Message

Ralph - 31 Dec 2005 12:41 GMT

> I open an ADODB recordset rs and want to determine if a particular field is
> present in the recordset. For example rs.Fields("ClientID"). I want to do
> something if the field is in the recordset and move on if not. How can I do
> that?
>
> Thanks so much!!

Use the Fields Collection...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdco
lfields.asp

The check the name of the various Field Objects...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdob
jfield.asp

Reply to this Message

smk23 - 31 Dec 2005 13:18 GMT

So something like

If rs.Fields(3).Name="ClientID" Then

would work, but the particular field in question would have to be in exactly
the same place (index) in the recordset each time. I guess I could iterate
through the collection checking each member... Any other thoughts?

Signature

sam

> > I open an ADODB recordset rs and want to determine if a particular field
> is
[quoted text clipped - 10 lines]
> The check the name of the various Field Objects...
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdob
jfield.asp

Reply to this Message

TC - 31 Dec 2005 13:34 GMT

dim bExists as boolean, s as string
on error resume next
s = rs.fields("ClientId").name
bExists = (err.number = 0)
on error goto 0
if bExists then
' it exists.
else
' it doesn't exist.
endif

HTH,
TC

Reply to this Message

Ralph - 31 Dec 2005 18:10 GMT

> dim bExists as boolean, s as string
> on error resume next
[quoted text clipped - 9 lines]
> HTH,
> TC

That's very good, and certainly a lot quicker than enumerating a Collection.

I hesitated to go there as I was concerned about the OP's level of
experience. However, on further reflection, this an excellent suggestion no
matter what level, because every VB programmer needs to learn how to use
Error Handling in a "positive" way.

I would add only one suggestion. Instead of simply turning off the Error
Handler (On Error Goto 0), it would likely be more useful to reset it to
something else to inorder to guard the remaining code.

-ralph

Reply to this Message

smk23 - 31 Dec 2005 22:34 GMT

thanks! really useful.

Signature

sam

> > dim bExists as boolean, s as string
> > on error resume next
[quoted text clipped - 22 lines]
>
> -ralph

Reply to this Message

TC - 31 Dec 2005 22:59 GMT

I look at it this way. If the person understands error handling, they
will automatically adjust it to suit their own requirements. But if
they /don't/ understand it - the safest assumption, imho - they will
get the safest style, which is, to die on the spot, thereby showing
exactly where the error occured.

Cheers,
TC

Reply to this Message

Ralph - 31 Dec 2005 23:47 GMT

> I look at it this way. If the person understands error handling, they
> will automatically adjust it to suit their own requirements. But if
[quoted text clipped - 4 lines]
> Cheers,
> TC

LOL.

The whole subject of "Error Handling" and programmers has always fascinated
me. (And as I have already started on several cups of Holiday Cheer I am
beginning to lean philosophically. Which always proceeds the physical lean.
<g>)

As you pointed out - 'to die on the spot' - should be a great learning tool,
but so seldom is. All concentration is on the error and how to 'fix it',
little is saved for appreciating a means to guard for it and provide
alternatives. While all history is against the view - it continues that
programmers envision they can write absolutely error-free code.

We can insure code is always logically correct, but never error-free. Error
Handling can authenticate the former and protect us from the assumptions of
the latter.

Happy New Year.
-ralph

Reply to this Message

VB Forum / Database Access / December 2005

check if field exists