Solved

If recordset field does not exist set value

Posted on 2010-08-16
16
732 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
ID: 33450467
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
ID: 33451502
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
ID: 33452628
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:sanjshah12
ID: 33456663
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
ID: 33458044
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
ID: 33458156
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
ID: 33460913
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
ID: 33460932
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33460944
>>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
ID: 33491499

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
ID: 33550794
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
ID: 33550820
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
ID: 33551059
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
ID: 33551463
Please post the code you are using.  Hopefully you are using VBScript.
0
 

Author Comment

by:sanjshah12
ID: 33552434
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
ID: 33552454
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

829 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