Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

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

0
sanjshah12
Asked:
sanjshah12
  • 5
  • 5
  • 2
  • +3
1 Solution
 
Wayne BarronCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 BarronCommented:
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now