ADO/ASP/Isnull issue.

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("
runtime 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

Re: ADO/ASP/Isnull issue.

jimm.sander@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("
runtime 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

Re: ADO/ASP/Isnull issue.

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("
runtime 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" wrote:
> jimm.san...@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("
runtime 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

Re: ADO/ASP/Isnull issue.

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_display 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, jimm.san...@gmail.com 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("
runtime 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" wrote:
>
> > jimm.san...@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("
runtime 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

Re: ADO/ASP/Isnull issue.

No worries I wrote a workaround into a diff part of the app


jimm.sander@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_display 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, jimm.san...@gmail.com 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("
runtime 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" wrote:
> >
> > > jimm.san...@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("
runtime 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