|
 |
asp_databases thread: Problem with Null Records
Message #1 by "Richard Wootton" <r.wootton@u...> on Fri, 24 Aug 2001 12:25:55 +0100
|
|
To whom it may concern
I'm currently building a site and to help me I've purchased "Beginning ASP
Databases" ISBN 1-861002-72-6. The book has been very helpful.
As a novice to ASP I've also extracted sample code from other sources on the
web.
A particular extracted example works well in listing records then turning
them into links but the code cannot cope with a Null record. I've tried to
integrate working example code in the book with the code in question but to
no avail, so I'm turning to your site for help.
What I've done to explain myself as clearly as possible is create a web page
to demonstrate the problem.
The link to the demo is here
http://www.bigrichonline.com/asp_day6/displayList.asp
When you view the web page, put simply, I need to integrate example 1 with
example 2 to make example 3 work.(it will make more sense when you view the
page I hope!)
If yourselves or any of your subscribers could help It would be greatly
appreciated.
Find code for displaylist.asp below
Best regards
Richard Wootton
<!-- #INCLUDE FILE="adovbs.inc" -->
<%
' Get Current Category
cat = TRIM( Request( "cat" ) )
IF cat = "" THEN cat = "Home"
' Open Database Connection
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "accessDSN2"
%>
<HTML>
<HEAD>
<TITLE>Problem - Displaying a Recordset List with a Null record</TITLE>
</HEAD>
<body vlink="#6696da" alink="#6496da" vtext=#00ff00 bgcolor="#ffffff">
<TABLE BORDER=0 ALIGN=CENTER WIDTH=95% cellpadding=0 cellspacing=0>
<TR>
<TD HEIGHT=140 WIDTH=%40 ALIGN=LEFT>
<TABLE BORDER=1 ALIGN=LEFT WIDTH=140 cellpadding=0 cellspacing=0>
"Goods"
<TR>
<TD ALIGN=LEFT>
<b>product_category</b>
</TD>
</TR>
<TR>
<TD ALIGN=LEFT>
Bakers
</TD>
</TR>
<TR>
<TD ALIGN=LEFT>
Butchers
</TD>
</TR>
<TR>
<TD ALIGN=LEFT>
Florists
</TD>
</TR>
</TABLE>
</TD>
<TD WIDTH=60% ALIGN=LEFT>
<TABLE BORDER=1 ALIGN=LEFT WIDTH=140 cellpadding=0 cellspacing=0>
"Test"
<TR>
<TD ALIGN=LEFT>
<b>product_category</b>
</TD>
</TR>
<TR>
<TD ALIGN=LEFT>
Bakers
</TD>
</TR>
<TR>
<TD ALIGN=LEFT>
Butchers
</TD>
</TR>
<TR>
<TD ALIGN=LEFT>
-Null Record-
</TD>
</TR>
<TR>
<TD ALIGN=LEFT>
Florists
</TD>
</TR>
</TABLE>
</TD>
</TR>
<TR>
<TD COLSPAN=2 ALIGN=LEFT>
<b>1. Example code from book. Working using "Test" table Null record
correctly skipped</b>
</TD>
</TR>
<TR>
<TD COLSPAN=2 ALIGN=LEFT>
<%
dim oRSyc
Set oRSyc=server.createobject("ADODB.recordset")
oRSyc.open "Test", "DSN=accessDSN2"
do while not oRSyc.EOF
If IsNull (oRSyc("product_category")) Then
Response.Write ""
Else
Response.Write oRSyc("product_category") & "<br>"
End If
oRSyc.movenext
loop
oRSyc.close
set oRSyc=nothing
%>
</TD>
</TR>
<TR>
<TD COLSPAN=2 ALIGN=LEFT>
<b>2. Own example code. Working using "Goods" table with NO Null records
present<b>
</TD>
</TR>
<TR>
<TD COLSPAN=2 ALIGN=LEFT>
<table width=100% border=0 bgcolor="#ffffff"
cellpadding=0 cellspacing=0>
<td align="left" valign="top">
<table cellpadding=0 cellspacing=0
border=0>
<tr>
<td>
<font face="verdana, helvetica" size=1><b>
<%
Set catRS = Server.CreateObject( "ADODB.Recordset" )
catRS.ActiveConnection = Con
sqlString = "SELECT DISTINCT product_category FROM Goods "
sqlString = sqlString & "WHERE product_status=1 "
sqlString = sqlString & "ORDER BY product_category"
catRS.Open sqlString
%>
<% WHILE NOT catRS.EOF %>
<% IF catRS( "product_category" ) = cat THEN %>
<BR><font face="verdana, helvetica" size=1 color=#d10303><b>
<%=catRS( "product_category" )%>
</b></font>
<% ELSE %>
<BR><a href="goodsa.asp?cat=<%=Server.URLEncode( catRS(
"product_category" ) )%>"><%=catRS( "product_category" )%></a>
<% END IF %>
<%
catRS.MoveNext
WEND
%>
</UL>
<% catRS.Close %>
</b></font>
</td>
</tr>
</table>
</td>
</td>
</tr>
</table>
</TD>
</TR>
<TR>
<TD COLSPAN=2 ALIGN=LEFT>
<b>3. Own example code repeat of above. Failed using "Test" table with a
Null record present</b>
</TD>
</TR>
<TR>
<TD COLSPAN=2 ALIGN=LEFT>
<table width=100% border=0 bgcolor="#ffffff"
cellpadding=0 cellspacing=0>
<td align="left" valign="top">
<table cellpadding=0 cellspacing=0
border=0>
<tr>
<td>
<font face="verdana, helvetica" size=1><b>
<%
Set catRS = Server.CreateObject( "ADODB.Recordset" )
catRS.ActiveConnection = Con
sqlString = "SELECT DISTINCT product_category FROM Test "
sqlString = sqlString & "WHERE product_status=1 "
sqlString = sqlString & "ORDER BY product_category"
catRS.Open sqlString
%>
<% WHILE NOT catRS.EOF %>
<% IF catRS( "product_category" ) = cat THEN %>
<BR><font face="verdana, helvetica" size=1 color=#d10303><b>
<%=catRS( "product_category" )%>
</b></font>
<% ELSE %>
<BR><a href="goods.asp?cat=<%=Server.URLEncode( catRS(
"product_category" ) )%>"><%=catRS( "product_category" )%></a>
<% END IF %>
<%
catRS.MoveNext
WEND
%>
</UL>
<% catRS.Close %>
</b></font>
</td>
</tr>
</table>
</td>
</td>
</tr>
</table>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
Message #2 by Steve Carter <Steve.Carter@t...> on Fri, 24 Aug 2001 13:21:09 +0100
|
|
Clue: The working code includes the characters "If IsNull" in that sequence.
> -----Original Message-----
> From: Richard Wootton [mailto:r.wootton@u...]
> Sent: 24 August 2001 12:26
> To: ASP Databases
> Cc: Me(bigrichonline)
> Subject: [asp_databases] Problem with Null Records
>
>
> To whom it may concern
>
> I'm currently building a site and to help me I've purchased
> "Beginning ASP
> Databases" ISBN 1-861002-72-6. The book has been very helpful.
>
> As a novice to ASP I've also extracted sample code from other
> sources on the
> web.
>
> A particular extracted example works well in listing records
> then turning
> them into links but the code cannot cope with a Null record.
> I've tried to
> integrate working example code in the book with the code in
> question but to
> no avail, so I'm turning to your site for help.
>
> What I've done to explain myself as clearly as possible is
> create a web page
> to demonstrate the problem.
>
> The link to the demo is here
>
> http://www.bigrichonline.com/asp_day6/displayList.asp
>
> When you view the web page, put simply, I need to integrate
> example 1 with
> example 2 to make example 3 work.(it will make more sense
> when you view the
> page I hope!)
>
> If yourselves or any of your subscribers could help It would
> be greatly
> appreciated.
>
> Find code for displaylist.asp below
>
> Best regards
>
> Richard Wootton
>
>
>
>
>
>
> <!-- #INCLUDE FILE="adovbs.inc" -->
>
> <%
> ' Get Current Category
> cat = TRIM( Request( "cat" ) )
> IF cat = "" THEN cat = "Home"
>
> ' Open Database Connection
> Set Con = Server.CreateObject( "ADODB.Connection" )
> Con.Open "accessDSN2"
> %>
>
> <HTML>
>
> <HEAD>
>
> <TITLE>Problem - Displaying a Recordset List with a Null
> record</TITLE>
>
> </HEAD>
>
> <body vlink="#6696da" alink="#6496da" vtext=#00ff00 bgcolor="#ffffff">
>
>
>
> <TABLE BORDER=0 ALIGN=CENTER WIDTH=95% cellpadding=0 cellspacing=0>
>
> <TR>
> <TD HEIGHT=140 WIDTH=%40 ALIGN=LEFT>
> <TABLE BORDER=1 ALIGN=LEFT WIDTH=140 cellpadding=0 cellspacing=0>
> "Goods"
> <TR>
> <TD ALIGN=LEFT>
> <b>product_category</b>
> </TD>
> </TR>
>
> <TR>
> <TD ALIGN=LEFT>
> Bakers
> </TD>
> </TR>
>
> <TR>
> <TD ALIGN=LEFT>
> Butchers
> </TD>
> </TR>
>
> <TR>
> <TD ALIGN=LEFT>
> Florists
> </TD>
> </TR>
>
> </TABLE>
> </TD>
>
> <TD WIDTH=60% ALIGN=LEFT>
> <TABLE BORDER=1 ALIGN=LEFT WIDTH=140 cellpadding=0 cellspacing=0>
> "Test"
> <TR>
> <TD ALIGN=LEFT>
> <b>product_category</b>
> </TD>
> </TR>
>
> <TR>
> <TD ALIGN=LEFT>
> Bakers
> </TD>
> </TR>
>
> <TR>
> <TD ALIGN=LEFT>
> Butchers
> </TD>
> </TR>
>
> <TR>
> <TD ALIGN=LEFT>
> -Null Record-
> </TD>
> </TR>
>
> <TR>
> <TD ALIGN=LEFT>
> Florists
> </TD>
> </TR>
>
> </TABLE>
> </TD>
>
>
> </TR>
>
> <TR>
> <TD COLSPAN=2 ALIGN=LEFT>
> <b>1. Example code from book. Working using "Test" table Null record
> correctly skipped</b>
> </TD>
> </TR>
>
> <TR>
> <TD COLSPAN=2 ALIGN=LEFT>
> <%
> dim oRSyc
> Set oRSyc=server.createobject("ADODB.recordset")
> oRSyc.open "Test", "DSN=accessDSN2"
>
> do while not oRSyc.EOF
>
> If IsNull (oRSyc("product_category")) Then
> Response.Write ""
> Else
> Response.Write oRSyc("product_category") & "<br>"
> End If
>
> oRSyc.movenext
> loop
> oRSyc.close
> set oRSyc=nothing
> %>
> </TD>
> </TR>
>
> <TR>
> <TD COLSPAN=2 ALIGN=LEFT>
> <b>2. Own example code. Working using "Goods" table with NO
> Null records
> present<b>
> </TD>
> </TR>
>
> <TR>
> <TD COLSPAN=2 ALIGN=LEFT>
> <table width=100% border=0 bgcolor="#ffffff"
> cellpadding=0 cellspacing=0>
>
> <td align="left" valign="top">
>
> <table cellpadding=0 cellspacing=0
> border=0>
> <tr>
> <td>
> <font face="verdana, helvetica" size=1><b>
> <%
> Set catRS = Server.CreateObject( "ADODB.Recordset" )
> catRS.ActiveConnection = Con
> sqlString = "SELECT DISTINCT product_category FROM Goods "
> sqlString = sqlString & "WHERE product_status=1 "
> sqlString = sqlString & "ORDER BY product_category"
> catRS.Open sqlString
> %>
>
> <% WHILE NOT catRS.EOF %>
> <% IF catRS( "product_category" ) = cat THEN %>
> <BR><font face="verdana, helvetica" size=1 color=#d10303><b>
> <%=catRS( "product_category" )%>
> </b></font>
> <% ELSE %>
> <BR><a href="goodsa.asp?cat=<%=Server.URLEncode( catRS(
> "product_category" ) )%>"><%=catRS( "product_category" )%></a>
> <% END IF %>
> <%
> catRS.MoveNext
> WEND
> %>
> </UL>
> <% catRS.Close %>
>
> </b></font>
> </td>
> </tr>
> </table>
>
>
> </td>
>
> </td>
>
> </tr>
> </table>
>
> </TD>
> </TR>
>
> <TR>
> <TD COLSPAN=2 ALIGN=LEFT>
> <b>3. Own example code repeat of above. Failed using "Test"
> table with a
> Null record present</b>
> </TD>
> </TR>
>
> <TR>
> <TD COLSPAN=2 ALIGN=LEFT>
> <table width=100% border=0 bgcolor="#ffffff"
> cellpadding=0 cellspacing=0>
>
> <td align="left" valign="top">
>
> <table cellpadding=0 cellspacing=0
> border=0>
> <tr>
> <td>
> <font face="verdana, helvetica" size=1><b>
> <%
> Set catRS = Server.CreateObject( "ADODB.Recordset" )
> catRS.ActiveConnection = Con
> sqlString = "SELECT DISTINCT product_category FROM Test "
> sqlString = sqlString & "WHERE product_status=1 "
> sqlString = sqlString & "ORDER BY product_category"
> catRS.Open sqlString
> %>
>
> <% WHILE NOT catRS.EOF %>
> <% IF catRS( "product_category" ) = cat THEN %>
> <BR><font face="verdana, helvetica" size=1 color=#d10303><b>
> <%=catRS( "product_category" )%>
> </b></font>
> <% ELSE %>
> <BR><a href="goods.asp?cat=<%=Server.URLEncode( catRS(
> "product_category" ) )%>"><%=catRS( "product_category" )%></a>
> <% END IF %>
> <%
> catRS.MoveNext
> WEND
> %>
> </UL>
> <% catRS.Close %>
>
> </b></font>
> </td>
> </tr>
> </table>
>
>
> </td>
>
> </td>
>
> </tr>
> </table>
>
> </TD>
> </TR>
>
> </TABLE>
>
> </BODY>
> </HTML>
Message #3 by Kyle Burns <kburns@c...> on Fri, 24 Aug 2001 15:11:20 -0500
|
|
It looks like your problem lies in URLEncode() needing to have a non-null
parameter. Try testing the value against null and only call the method if
the value is not null. Your hyperlink would be a dead link anyway if the
field is null.
=================================
Kyle M. Burns, MCSD
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: Richard Wootton [mailto:r.wootton@u...]
Sent: Friday, August 24, 2001 6:26 AM
To: ASP Databases
Cc: Me(bigrichonline)
Subject: [asp_databases] Problem with Null Records
To whom it may concern
I'm currently building a site and to help me I've purchased "Beginning ASP
Databases" ISBN 1-861002-72-6. The book has been very helpful.
As a novice to ASP I've also extracted sample code from other sources on the
web.
A particular extracted example works well in listing records then turning
them into links but the code cannot cope with a Null record. I've tried to
integrate working example code in the book with the code in question but to
no avail, so I'm turning to your site for help.
|
|
|
View Cart
