using cast sql statement in a adodb.connection

[keak]

HI there
I have the following code wich is pretty straight forward:

Set Conn = Server.CreateObject("ADODB.Connection")
set Rs = server.createobject("ADODB.Recordset")
Conn.Open "my db specs here"

Rs.Open "select max(cast (mem_id as int) ) as maxid from members", Conn

Rs.MoveFirst
response.write "+++++" + Rs("maxid")

however, if I change the query to select max(mem_id) as maxid from members

I get a output correctly in my asp.

I have run the original query directly in postgres and it returns the maxid fine to me ....

am i missing something here ?

any input will be greatly appreciated.

thanks,
kazu

 

[tsuji]

>however, if I change the query to select max(mem_id) as maxid from members I get a output correctly in my asp.
That seems to indicate cast() is not supported (such as access db). Maybe you can try cint()?

>Rs.Open "select max(cast (mem_id as int) ) as maxid from members", Conn
[tt]Rs.Open "select max(cint(mem_id)) as maxid from members", Conn[/tt]

 

[keak]

Hi there, Thanks for the reply !

I tried that statement and I got the message:
function cint(character varing) does not exist

My backend db is postgres, and it does seem to support cast; Can it be the ADODB.Connection that is not supporting this feature of sql ?

 

[Sheco]

presumably casting a null or non-numeric value to int will raise an error but nulls and such would be ignored in the max aggregate function...

What is it about "[tt]select max(mem_id) as maxid from members[/tt]" that you are trying to improve by using the cast function?


Also, and this is sort of beside the point since you are using Postgres, but I have seen things that would work inside Access that don't work using an ODBC connector to Access. I'm not remembering exactly what at this late hour but I think it had to do with queries that included non-SQL functions... so functions that were implemented by Access but not by the ODBC connection to Access.