how to get the maximum value?

[choohean]

I try to get a maximum value from a columm and my coding is like that:


SQL= "SELECT max(service_id) FROM tracking "
set rs = openConn.Execute(SQL)

dim id

id=rs("service_id&quot

Response.Write(id)

but it still got error! The error is:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

can some one help me to check over it! Thanks for your kindness...

regards, eddy

 

[choohean]

Can anyone just give me a help, is quite urgent...

 

[bperry]

I think you need to give the calculated column a name:

example:
SQL= "SELECT max(service_id) as MaxSerId FROM tracking "

 

[choohean]

I have tried but it can't work....

 

[bperry]

Maybe a gave you a poor suggestion for a column name. Is your calling code still looking for service_id ?

Try this:
SQL= "SELECT max(service_id) as service_id FROM tracking "

 

[choohean]

Still canot work...

 

[bperry]

"Item cannot be found in the collection...." means there's no such column in the returned result set.

There's nothing wrong with the SQL statement. So I think we're left with either (1) the table Tracking does not have a column called service_id at all (unlikely), or (2) somewhere in your client code (ASP? VB?), you are referring to a name other that service_id.

Are you referring to the returned item in the record set by name or by ordinal number? If by name, double-check all spellings again. If by ordinal number, you not referring to it with a bad index, are you? (like 3 or something like that?)

bp