[RESOLVED] Can you optimize this part of code?

[Mololo]

Hello, it's working but I'm sure it's not the right way to do it.
As you can see, I just want to find the highest id value from a table.


<%
set rs=server.CreateObject("adodb.recordset")
sql="select id from Comments order by cint(id) desc"
rs.open sql,conn,1,1
if rs.bof and rs.eof then
'//
else
for i=1 to 1
if rs.eof then exit for
sTopid=rs("id")

rs.movenext
next
end if
rs.close
set rs=nothing
%>

[ryanbutler]

I'd adjust the SQL statement and use TOP, since top will always get the highest value.

select TOP id from Comments order by cint(id) desc

That might not be the exact syntax, but its close.

[Mololo]

You're right abt the sql no doubts abt that.
select TOP 1 id from Comments order by cint(id) desc

I just don't know how to write the ASP for it. Since I only have one value, I guess I can skip some of the ASP codes after that, no?

[ryanbutler]

Why do you need to do all that ASP code. You'll have the result in the record set from the SQL query. Just output the result.

[Mololo]

Can you show me how to do that?

[Mololo]

Like this?


<%
set rs=server.CreateObject("adodb.recordset")
sql="select id from Comments order by cint(id) desc"
rs.open sql,conn,1,1

sTopid=rs("id")

rs.close
set rs=nothing
%>

[ryanbutler]

Except you forgot your amended SQL statement that I gave in my first response.

[Mololo]

Thank you Ryan! It worked all very good. I guess that now, I just need to make sure my table is never empty, right?

[ryanbutler]

Either that or just perform a check on the record set object checking for end of file. If its true, output an error message, otherwise, output the result.

[Mololo]

Yep, got it. Thanks again Ryan. = )