Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using cast sql statement in a adodb.connection

Status
Not open for further replies.

keak

Programmer
Sep 12, 2005
247
CA
HI there
I have the following code wich is pretty straight forward:

Code:
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
 
>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]
 
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 ?

 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top