ADO/ASP/Isnull issue.

[jimm.sander]

Hello,

Problem: Im using isnull() in vbscript to determine if a field returned
from a ado object call is in fact null. The problem is when I use
isnull in anything other than a response.write() I get the following
error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
/mypage.asp, line 1514

I have double checked spelling, the field Im looking at does infact
exist.

NOTE: I'll use # to denote comments edited into code and output not
actually part of code or output
CODE:
Response.Write("<BRruntime sql is null?" &
isnull(rs.Fields("runtime_sql")))
if not isnull(rs.Fields("runtime_sql")) then
report_fields = report_fields & concat & rs.Fields("runtime_sql")
end if
#debug output.
runtime sql is null?True
#end debug output.
If I remove the not it does not error. if I add an else it does.
After extensive testing Ive determined that if the condidtion in the if
statement is false the page errs.


NOTE2: isnull is used throughout this asp app and this is the first
time it has thrown an err.
Any suggestions?

Thanks in advance,

Jimm

[Daniel Crichton]

ji*********@gmail.com wrote on 2 Jan 2007 08:52:05 -0800:

Hello,

Problem: Im using isnull() in vbscript to determine if a field returned
from a ado object call is in fact null. The problem is when I use
isnull in anything other than a response.write() I get the following
error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
/mypage.asp, line 1514

I have double checked spelling, the field Im looking at does infact
exist.

NOTE: I'll use # to denote comments edited into code and output not
actually part of code or output

CODE:
Response.Write("<BRruntime sql is null?" &
isnull(rs.Fields("runtime_sql")))
if not isnull(rs.Fields("runtime_sql")) then
report_fields = report_fields & concat & rs.Fields("runtime_sql")
end if

#debug output.
runtime sql is null?True
#end debug output.

If I remove the not it does not error. if I add an else it does.
After extensive testing Ive determined that if the condidtion in the if
statement is false the page errs.

NOTE2: isnull is used throughout this asp app and this is the first
time it has thrown an err.

Any suggestions?

If you post the full code that is erroring, it might give a clue as to what
is going wrong. The error message does suggest a typo somewhere though, as
it's the expected response when a column is requested that does not exist in
the recordset.

Dan

[jimm.sander]

chunk of code :

if not isnull(rs("child_report_id")) or (not isnull(rs("link")) and
not isnull(rs("access_level_id"))) then
Response.Write("<BRruntime sql is null?" &
((isnull(rs.Fields("runtime_sql"))) Xor True ))
'debug
dim a
'a = isnull(rs.Fields("runtime_sql"))
' if not a then
' if (isnull(rs.Fields("runtime_sql")) xor true) then
' if isnull(rs.Fields("runtime_sql")) <>
true then

if not isnull(rs.Fields("runtime_sql"))
then
report_fields = report_fields & concat &
rs.Fields("runtime_sql")
end if

'Edebug

if concat="" then
report_fields = report_fields & ", "
end if
column_names = column_names & "DO NOT DISPLAY" & vbTab
column_datatypes = column_datatypes & "99,"
column_funcs = column_funcs & "N,"
if not isnull(rs("link")) then
child_report_ids = child_report_ids & rs("link") & ","
if not isnull(rs("link_display")) then
child_report_displays = child_report_displays &
rs("link_display") & ","
else
child_report_displays = child_report_displays & ","
end if
else
child_report_ids = child_report_ids & rs("child_report_id") & ","
child_report_displays = child_report_displays &
rs("child_report_id") & ","
end if
num_cols = num_cols + 1

if(IsSortField) then
if(rs.Fields("datatype")=1) then
listcount = listcount + 1
end if
stcount = stcount + 1
end if
end if

On Jan 2, 11:07 am, "Daniel Crichton" <[e-mail]:

[e-mail] wrote on 2 Jan 2007 08:52:05 -0800:

Hello,

Problem: Im using isnull() in vbscript to determine if a field returned
from a ado object call is in fact null. The problem is when I use
isnull in anything other than a response.write() I get the following
error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
/mypage.asp, line 1514

I have double checked spelling, the field Im looking at does infact
exist.

NOTE: I'll use # to denote comments edited into code and output not
actually part of code or output

CODE:
Response.Write("<BRruntime sql is null?" &
isnull(rs.Fields("runtime_sql")))
if not isnull(rs.Fields("runtime_sql")) then
report_fields = report_fields & concat & rs.Fields("runtime_sql")
end if

#debug output.
runtime sql is null?True
#end debug output.

If I remove the not it does not error. if I add an else it does.
After extensive testing Ive determined that if the condidtion in the if
statement is false the page errs.

NOTE2: isnull is used throughout this asp app and this is the first
time it has thrown an err.

Any suggestions?If you post the full code that is erroring, it might give a clue as to what

is going wrong. The error message does suggest a typo somewhere though, as
it's the expected response when a column is requested that does not exist in
the recordset.

Dan

[jimm.sander]

forgot the sql that generates the rs.

select pr.display as prdisplay,pr.sort_order as prsort_order,
r.value_list, r.report_id,r.field_id, field_sql,
coalesce(r.field_display_name,f.field_display_name ) as
field_display_name, coalesce(rtf.display,r.display) as display,
r.agg_func, coalesce(rtf.sort_order,r.sort_order) as sort_order,
sort_dir, datatype, lookup_field, coalesce(r.field_width,f.field_width)
as field_width, r.child_report_id,
r.runtime_sql,r.link,sa.access_level_id,r.link_dis play from
hl_reportfield r inner join hl_field f on f.field_id=r.field_id left
join screenaccess sa on sa.screen_id=r.screen_id and
sa.access_level_id=103 left join #runtimeFields rtf on
rtf.field_id=r.field_id inner join hl_reportfield pr on
pr.field_id=r.field_id where (r.report_id=1337 and
coalesce(r.enabled,'Y')='Y') and pr.report_id=1337 order by
coalesce(rtf.sort_order,r.sort_order), r.default_order, r.field_id
On Jan 2, 11:19 am, [e-mail] wrote:

chunk of code :

if not isnull(rs("child_report_id")) or (not isnull(rs("link")) and
not isnull(rs("access_level_id"))) then
Response.Write("<BRruntime sql is null?" &
((isnull(rs.Fields("runtime_sql"))) Xor True ))
'debug
dim a
'a = isnull(rs.Fields("runtime_sql"))
' if not a then
' if (isnull(rs.Fields("runtime_sql")) xor true) then
' if isnull(rs.Fields("runtime_sql")) <>
true then

if not isnull(rs.Fields("runtime_sql"))
then
report_fields = report_fields & concat &
rs.Fields("runtime_sql")
end if

'Edebug

if concat="" then
report_fields = report_fields & ", "
end if
column_names = column_names & "DO NOT DISPLAY" & vbTab
column_datatypes = column_datatypes & "99,"
column_funcs = column_funcs & "N,"
if not isnull(rs("link")) then
child_report_ids = child_report_ids & rs("link") & ","
if not isnull(rs("link_display")) then
child_report_displays = child_report_displays &
rs("link_display") & ","
else
child_report_displays = child_report_displays & ","
end if
else
child_report_ids = child_report_ids & rs("child_report_id") & ","
child_report_displays = child_report_displays &
rs("child_report_id") & ","
end if
num_cols = num_cols + 1

if(IsSortField) then
if(rs.Fields("datatype")=1) then
listcount = listcount + 1
end if
stcount = stcount + 1
end if
end if

On Jan 2, 11:07 am, "Daniel Crichton" <[e-mail]:

[e-mail] wrote on 2 Jan 2007 08:52:05 -0800:

Hello,

Problem: Im using isnull() in vbscript to determine if a field returned
from a ado object call is in fact null. The problem is when I use
isnull in anything other than a response.write() I get the following
error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
/mypage.asp, line 1514

I have double checked spelling, the field Im looking at does infact
exist.

NOTE: I'll use # to denote comments edited into code and output not
actually part of code or output

CODE:
Response.Write("<BRruntime sql is null?" &
isnull(rs.Fields("runtime_sql")))
if not isnull(rs.Fields("runtime_sql")) then
report_fields = report_fields & concat & rs.Fields("runtime_sql")
end if

#debug output.
runtime sql is null?True
#end debug output.

If I remove the not it does not error. if I add an else it does.
After extensive testing Ive determined that if the condidtion in the if
statement is false the page errs.

NOTE2: isnull is used throughout this asp app and this is the first
time it has thrown an err.

Any suggestions?If you post the full code that is erroring, it might give a clue as to what

is going wrong. The error message does suggest a typo somewhere though, as
it's the expected response when a column is requested that does not exist in
the recordset.

Dan

[jimm.sander]

No worries I wrote a workaround into a diff part of the app
ji*********@gmail.com wrote:

forgot the sql that generates the rs.

select pr.display as prdisplay,pr.sort_order as prsort_order,
r.value_list, r.report_id,r.field_id, field_sql,
coalesce(r.field_display_name,f.field_display_name ) as
field_display_name, coalesce(rtf.display,r.display) as display,
r.agg_func, coalesce(rtf.sort_order,r.sort_order) as sort_order,
sort_dir, datatype, lookup_field, coalesce(r.field_width,f.field_width)
as field_width, r.child_report_id,
r.runtime_sql,r.link,sa.access_level_id,r.link_dis play from
hl_reportfield r inner join hl_field f on f.field_id=r.field_id left
join screenaccess sa on sa.screen_id=r.screen_id and
sa.access_level_id=103 left join #runtimeFields rtf on
rtf.field_id=r.field_id inner join hl_reportfield pr on
pr.field_id=r.field_id where (r.report_id=1337 and
coalesce(r.enabled,'Y')='Y') and pr.report_id=1337 order by
coalesce(rtf.sort_order,r.sort_order), r.default_order, r.field_id
On Jan 2, 11:19 am, [e-mail] wrote:

chunk of code :

if not isnull(rs("child_report_id")) or (not isnull(rs("link")) and
not isnull(rs("access_level_id"))) then
Response.Write("<BRruntime sql is null?" &
((isnull(rs.Fields("runtime_sql"))) Xor True ))
'debug
dim a
'a = isnull(rs.Fields("runtime_sql"))
' if not a then
' if (isnull(rs.Fields("runtime_sql")) xor true) then
' if isnull(rs.Fields("runtime_sql")) <>
true then

if not isnull(rs.Fields("runtime_sql"))
then
report_fields = report_fields & concat &
rs.Fields("runtime_sql")
end if

'Edebug

if concat="" then
report_fields = report_fields & ", "
end if
column_names = column_names & "DO NOT DISPLAY" & vbTab
column_datatypes = column_datatypes & "99,"
column_funcs = column_funcs & "N,"
if not isnull(rs("link")) then
child_report_ids = child_report_ids & rs("link") & ","
if not isnull(rs("link_display")) then
child_report_displays = child_report_displays &
rs("link_display") & ","
else
child_report_displays = child_report_displays & ","
end if
else
child_report_ids = child_report_ids & rs("child_report_id") & ","
child_report_displays = child_report_displays &
rs("child_report_id") & ","
end if
num_cols = num_cols + 1

if(IsSortField) then
if(rs.Fields("datatype")=1) then
listcount = listcount + 1
end if
stcount = stcount + 1
end if
end if

On Jan 2, 11:07 am, "Daniel Crichton" <[e-mail]:

[e-mail] wrote on 2 Jan 2007 08:52:05 -0800:

Hello,

Problem: Im using isnull() in vbscript to determine if a field returned
from a ado object call is in fact null. The problem is when I use
isnull in anything other than a response.write() I get the following
error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
/mypage.asp, line 1514

I have double checked spelling, the field Im looking at does infact
exist.

NOTE: I'll use # to denote comments edited into code and output not
actually part of code or output

CODE:
Response.Write("<BRruntime sql is null?" &
isnull(rs.Fields("runtime_sql")))
if not isnull(rs.Fields("runtime_sql")) then
report_fields = report_fields & concat & rs.Fields("runtime_sql")
end if

#debug output.
runtime sql is null?True
#end debug output.

If I remove the not it does not error. if I add an else it does.
After extensive testing Ive determined that if the condidtion in the if
statement is false the page errs.

NOTE2: isnull is used throughout this asp app and this is the first
time it has thrown an err.

Any suggestions?If you post the full code that is erroring, it might give a clue as to what
is going wrong. The error message does suggest a typo somewhere though, as
it's the expected response when a column is requested that does not exist in
the recordset.

Dan