[thelist] ASP, SQL, Recordsets, Best Practices

[Warden, Matt]

> >... when there can only be one
> > unique record (field doesn't allow duplicates) or no record at all.
>
> presumably you want to do something with the fields for this particular
> row?  if you don't need to use any of the fields, i.e. if you only need to
> know *whether* a row exists, then you should be using
>
>     select count(*) from [table] where [uniqueness]
>
> you'll get back a 0 or some positive number, and you can build the IF
> ELSE condition just by testing on zero
>
> count(*) is a lot quicker for the database, too -- no rows to access

Wouldn't this be quicker:

select 1 from [table] where [uniqueness]

No functions... just a literal.

If I recall, I learned this from you...

And she doesn't have to test for a value at all, just test if there are
records in the recordset:

if objRS.eof and objRS.bof then
    bSuccess = Dexter.stopUsingFrontPage()
else
    bSuccess = Rudeman.learnRealSQL()
end if

And, of course, bSuccess will always be false in both cases...


Michele, sgd is being slow, so I'll let you know what .bof means: Beginning
of File. BOF is the "space" before the first record and EOF is the "space"
after the last record. So, if both are true, then there are no records. Most
people use EOF only because ADO defaults to setting the... umm... pointer
before the first record in the recordset (so, BOF *should* always be true
for an untouched recordset).

Yes, the fields collection starts at zero and oRS(0) is the same as
oRS.fields(0). And, since all it does is access a value, then it doesn't
matter what the fieldtype is (number, text, autonumber, date, etc.) at this
point, anyway.


--
mattwarden
mattwarden.com