[thelist] ASP Question: If there is nothing to pull from the database...

[Warden, Matt]

On Dec 27, Scott Schrantz had something to say about RE: [thelist] ASP...

>	If, though, you have a simpler database structure, where there is a
>tblArticles and the comments are stored in one of the fields, 

But, you'd never do that, would you?

it is not a good thing to try to stick a 1-many relationship into a single
table.

>	Josh's method depends on the database you are using, too. Some
>databases (like MS Access) store empty text fields as a Zero-Length String,
>not Null. IsNull would return False, so instead you'd have to use:  If
>rs("Comments") = "" Then....

This isn't totally accurate. You have to set a field to allow zero-length
strings. That just means that "" is a valid value for the field. You can
still insert NULL. If you don't check the "allow zero-length
strings" checkbox, all that means is that you can't insert an empty
string.

It's up to your code whether you want to treat "" the same as a NULL
value.

<tip>
If you can avoid returning a recordset from the database tables, that's
generally a good thing. So, if all you're doing is spitting out a "See
comments" link, but you only want to display it if there are comments for
that article, try something like this:

SELECT 1 FROM comment WHERE articleid=123;

Why 'SELECT 1'? If you are just looking for a boolean response like
whether or not there are comments for article number 123, the values in
the recordset is irrelevant, just whether or not there IS something
returned. So, it's generally faster to return a literal like 1 than a
value from a table. THis means that you might get a resultset like this:

+-------+
|   1   |
+-------+
|   1   |
|   1   |
|   1   |
|   1   |
+-------+

But, that's okay if all you want to know is whether there are comments for
that article. Hell, if you can get a recordcount of the resultset in your
language of choice (without cycling through the resultset with a counter),
then you know exactly how many comments there are (4 in this case).

So, your pseudo-code would be:

if resultset.hasRecords() then
	print '<a ...>View Comments (' + resultset.recordcount() +')</a>'
else
	print 'no comments for this article'
end if

Here, that code would spit out:

<a ...>View Comments (4)</a>

All this without opening the recordset at all.

</tip>

--
mattwarden
mattwarden.com