If recordset field does not exist set value

I have a recordset that contains several fields, however sometimes some of these fields may not exist, how can I capture this, I tried:

If ISNULL(Recordset1.Fields.Item("Feb").Value) then

but this does not work

sanjshah12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne BarronAuthor, Web DeveloperCommented:
I just found something, not sure about it.

if rsNew("IP")&"" = "" then

Suppose to check for NULL (or) Blank fields.

Carrzkiss


0
Om PrakashCommented:
If you are not sure if the field exists or not then you can loop through all fields and display the data:

Example:
<%
set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select * from table_name", conn
for each x in rs.fields
  response.write(x.name)
  response.write(x.value)
next
%>

you don't have to check if field exists or not.
0
sybeCommented:
What do you mean with "sometimes does  not exist"? You mean it is there, but its contents is null? Or do you mean it isn't there at all?

> If ISNULL(Recordset1.Fields.Item("Feb").Value) then

That should work to check if the contents of the field is Null. But an empty string is not Null.

I use a function as attached:





' example of its use:
MyValue = ReplaceEmptyString(oRS("fieldname").Value, "&nbsp;")

Function ReplaceEmptyString(ByVal sTest, ByVal sAlt)
    On Error Resume Next
    ReplaceEmptyString = sAlt
    If Len(Trim(Cstr("" & sTest))) > 0 Then ReplaceEmptyString = sTest
    Err.Clear
End Function

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sanjshah12Author Commented:
No the field may not exist sometimes, its where you recieve an error:

ADODB.Fields error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

Regards,

Sanj
0
sybeCommented:
So why do you ask for the value of the non-existing field? Of course there is an error.

I don't think that you can have a recordset which (with the same code) sometimes does have a field named "Feb" and sometimes it does not have such a field.

In fact you know in advance if such a field is present. If there is no such field, don't ask for its value.
0
Wayne BarronAuthor, Web DeveloperCommented:
I agree with sybe.

Are you writing the code or is someone else writing it and you are maintaning it?

Example

sql="select col1, col2, col4 from tab1"
rs.....

Now, when you write out your record.

rs("col3")

Now, you will get the error that you mentioned because the col3 is not listed in the statement.

sql="select col1, col2, col3, col4 from tab1"
rs.....

rs("col3")

Now, it will display without issue, because we have added it into the statement.

---------------
You need to make sure that all columns are added in your select statement.
If you are worried that you are going to miss one, then use the Database programs Management suite to generate the SQL Code for you.
This gives you 100% control over what is displayed and what is not displayed.

I use both ACCESS and SQL Server 2005 management suits, to do all my sql coding for my select statements.
(I hand code my inserts, update and deletes, but select statements can be complexe and long writing, so I let the program generate it for my, saving tones of time but correct code)

Good Luck
Carrzkiss
0
Anthony PerkinsCommented:
Please find a function to do what you want.  I personally would never recommend using this approach, but if that is what you want to do ...

You call it as follows:
If FieldExists(Recordset1.Fields, "Feb") Then

Function FieldExists(ByVal Flds, ByVal FieldName)

FieldExists = False
For Each Fld In Flds
	If Fld.Name = FieldName Then
		FieldExists = True
		Exit For
	End
Next

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
If you are worried about Case Sensitivity, then change it as follows:
Function FieldExists(ByVal Flds, ByVal FieldName)

FieldExists = False
For Each Fld In Flds
	If StrComp(Fld.Name, FieldName, 1) = 0 Then
		FieldExists = True
		Exit For
	End
Next

End Function

Open in new window

0
Anthony PerkinsCommented:
>>I don't think that you can have a recordset which (with the same code) sometimes does have a field named "Feb" and sometimes it does not have such a field. <<
You could probably do it with some really evil dynamic SQL.
0
alaspinCommented:

Hi,

The basic issue, as has already been pointed out, is that you do not know the structure of your recordset before it arrives.

There are 4 ways around this:
1. Don't use an explicit field name in your code unless you know that field exists.
2. If you have to use an explicit field name - code defensively using something like the FieldExists() idea posted earlier and wrap all your field access inside If... Then to avoid errors being thrown.
3. If you have control over the query that generates the recordset - look into why you only sometimes get the field, you can always amend your SQL for example to include some dummy columns to guarantee you will get them in your recordset - even if full of nulls at that point at least your VB won't fail.
4. If your have to do SELECT * from your data source consider checking the schema on the table first and only use the field names you know you have. I'd question why you don't always get the same columns back - you may need to trace the issue back in your data server to understand this, make a change there and the whole issue goes away (maybe).

If you're working with stored procedures written by somebody else then talk to them about why the columns are unpredictable. It may be an easy fix for them...

Looks like you might be writing ASP?
In that case you want your data server to get all the work done before passing the smallest recordset possible back to your web server.

How often does the schema change? Is it predicable when it will change? If so you could schedule how often you check it and store it in Application level variables (ASP or ASP.NET) Not brilliant but at least you will know up front what fields you have to play with until the next schema change and it will be shared across all your user sessions. Similarly if the columns are determined by the user (either through their choice or maybe business rules you have) then store it on a per user basis in Session level variables...

Playing with Application or Session variables can seriously impact how scalable your web application is, so think carefully before going down that route

"Evil" dynamic SQL? :) Sometimes there's no getting away from it - you find you do a bunch of string handling in VB/VBA up front prior to execution to get the correct SQL or you use a SQL generating tool to build defensive SQL that has lots of extra processing in the query itself - but at least you have that overhead on your data server and not on your client program. Depends how you want to play it.

A few of these methods start sounding like a lot of work (and it could be) but you'll also find a lot of this stuff becomes reusable and second nature to include in your solutions. Stick all your defensive data access code inside a class and use that all the time to do your db hits - write once - safe db access always from there on :)

I'm not going to give you code - the other guys have already helped there - Hopefully I've helped you consider the issues rather than the specifics.

OK, could be more than 4 but that's off the top of my head :)
0
sanjshah12Author Commented:
aceperkins,

Thanks for your code and help, I'm getting script error:


Microsoft VBScript compilation error '800a03ea'

Syntax error

/test/statstest.asp, line 265

Function FieldExists(ByVal Flds, ByVal FieldName)
^

Any idea?
0
Anthony PerkinsCommented:
You are right.  Depending on which function you chose the code should be:
Function FieldExists(ByVal Flds, ByVal FieldName)

FieldExists = False
For Each Fld In Flds
	If Fld.Name = FieldName Then
		FieldExists = True
		Exit For
	End If
Next

End Function

Or:

Function FieldExists(ByVal Flds, ByVal FieldName)

FieldExists = False
For Each Fld In Flds
	If StrComp(Fld.Name, FieldName, 1) = 0 Then
		FieldExists = True
		Exit For
	End If
Next

End Function

Open in new window

0
sanjshah12Author Commented:
strange, I'm still getting the same error - I've checked the code again to ensure  have the corect code?
0
Anthony PerkinsCommented:
Please post the code you are using.  Hopefully you are using VBScript.
0
sanjshah12Author Commented:
aceperkins,

this is the code I'm using:

Thanks for your help!
<% 
Function FieldExists(ByVal Flds, ByVal FieldName)

FieldExists = False
For Each Fld In Flds
	If Fld.Name = FieldName Then
		FieldExists = True
		Exit For
	End If
Next

End Function
%>

<%
If FieldExists(Recordset1.Fields, "Jan") = True Then 
Response.Write("Yes, field exists") 
else 
Response.Write("No, field does not exist!") 
end if 
%>

Open in new window

0
sanjshah12Author Commented:
aceperkins,

the code works, I had placed it in the wrong area (within a table) which made it fail.

Thanks for your help on this!

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.