Solved

If recordset field does not exist set value

Posted on 2010-08-16
16
719 Views
Last Modified: 2013-12-25
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

0
Comment
Question by:sanjshah12
  • 5
  • 5
  • 2
  • +3
16 Comments
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
I just found something, not sure about it.

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

Suppose to check for NULL (or) Blank fields.

Carrzkiss


0
 
LVL 22

Expert Comment

by:Om Prakash
Comment Utility
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
 
LVL 28

Expert Comment

by:sybe
Comment Utility
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
 

Author Comment

by:sanjshah12
Comment Utility
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
 
LVL 28

Expert Comment

by:sybe
Comment Utility
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
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
 

Expert Comment

by:alaspin
Comment Utility

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
 

Author Comment

by:sanjshah12
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Comment

by:sanjshah12
Comment Utility
strange, I'm still getting the same error - I've checked the code again to ensure  have the corect code?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Please post the code you are using.  Hopefully you are using VBScript.
0
 

Author Comment

by:sanjshah12
Comment Utility
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
 

Author Comment

by:sanjshah12
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now