Recordset for only one record - silly question 1

[Rexolio]

Hi... just had a simple question that I was curious about. I try to be very complete with my coding, using "Dim" and closing recordsets and command objects.

In the past when I want to return a specific record according to an ID number where there is going to be only 1 record, I have still been using the following code:

----------------------------

set rsList=Conn.execute("select * from table where id = " & id & "&quot
do while not rsList.eof
name = rsList("name&quot
rsList.movenext
loop
rsList.close

-----------------------------

I thought this was the proper way to do it, even though there's extra code thats not necessarily needed.

I recently started doing the following for this type of situation (one record):

-----------------------------
set rsList = same as above
if rsList.eof then
Response.write ("No such record&quot
else
name = rsList("name&quot
end if
rsList.close

--------------------------------

Maybe a silly question and I'm still pretty new to this stuff, but is this okay? It works fine, of course. But I'd rather avoid and incorrect scripting or bad practices that may cause problems in the future.

Thanks for your time.

 

[Rexolio]

Follow up question:

I have my database connection script for "Conn" in a server side include. Should I still, on the ASP that is using the include, close "Conn" at the end of the page/script? i.e.
Conn=nothing
Conn.close?

 

[Rexolio]

anyone?

 

[Jonax]

Ok - here goes:
The conn.object in the include ;(
Place your connection string in the include, but set, open, close and destroy (set conn = nothing) your connection as late/early as possible and in that order (call close before destroying).

As for the best practice:
I usually use...

If not (rs.bof and rs.eof) then
    'Something
Else
    'Some other thing
End If

...to check for empty recordsets, but in this case you might want to look in to the getstring-method This is not a bug - it's an undocumented feature...
;-)

 

[Jonax]

Furthermore - If your table contains more than the name column you might want to only SELECT the NAME FROM TABLE WHERE id = "... This is not a bug - it's an undocumented feature...
;-)

 

[Rexolio]

Thanks for the help!!

Unfortunately I'm getting the following error message now:

Microsoft VBScript runtime error '800a005b'
Object variable not set: 'Conn'

/new/professionals.asp, line 506

This message is at the exact line that goes:

Conn.close
Conn = nothing

There's no use of Conn after this point. Everythings finished using it. What am I doing wrong?

 

[Rexolio]

One more question too... I always thought that .bof and .eof were essentially the same thing. But since you use them both in your sample code above, they're obviously not. Can you tell me the deal?

 

[Jonax]

bof = Beginning of File, eof = End of File...
If both are true, you're dealing with an empty recordset!

As for the other:

Search for Conn - how does the results differ from this order:

set Conn = server.createobject("ADODB.CONNECTION")
Conn.open strSQL, strConn, 1, 3
Conn.close
set Conn = Nothing

My guess is that you destroy your connection-object (set Conn = Nothing) before the mentioned .close-line This is not a bug - it's an undocumented feature...
;-)

 

[Rexolio]

thanks, Jonax

No, I believe I'm doing everything right. Here is the exact code (more or less):

set Conn = server.createobject("ADODB.CONNECTION&quot
Set rs=Conn.Execute("SELECT * FROM Table&quot
while not rs.eof then
'blah blah blah
rs.movenext
wend
rs.close
Conn.close
set Conn = Nothing...

AND JUST AS i TYPED THAT IN I REALIZED THE STUPID MISTAKE THAT I MADE.... I was entering "Conn = Nothing", not "Set Conn = Nothing". It works now!!!

Thank you sooooooooooo much!!!