======================
Global.asa
======================
Sub Session_OnStart
  Set Session("Conn") = Server.CreateObject("ADODB.Connection")
  Session("Conn").Provider = "SQLOLEDB"
  Session("Conn").Open("server=192.168.0.22;UID=sa;P WD=test;database=test;")
end sub

======================
index.asp
======================
sql = "SELECT IDX, CLIENTIDX, COMPANY FROM CL_ADDRESSES WHERE CLIENTIDX= '" & Session.Contents("clientidx") & "' ORDER BY COMPANY"

rs.CursorLocation = 3
rs.CursorType = 3
rs.ActiveConnection = Session("Conn")
set rs = Session("Conn").Execute(Sql)

rs.PageSize = 20
rs.CacheSize = rs.PageSize
intPageCount = rs.PageCount
intRecordCount = rs.RecordCount

================================================== ===================
Instead of using Execute, how do i use "Open" to run the statement? I need to retrieve the value from the "Select" statement.
The way i'm doing now will return me -1 for rs.pagecount and rs.recordcount

Thanks in advance

rs.open sql,conn



Chintu

Thanks! didm't know it was so simple.
i tried the statements below.
i thought if i need to refer the connection in global.asa i have to use "SET"

set rs = Session("Conn").Open(Sql)
and
set rs.Open(sql), Session("Conn")

You should not store a connection object in the Session because you are losing the great benefit of automatic connection pooling and forcing a separate db connection to be made for each user. This will use a lot more resources on your web server and make it far less scalable. You should store just the connection string (in either the Session or Application collection) and open the connection on each page that needs it, then close the connection as soon as you are finished with it.

You mean it's better to do the connection on everypage they requires me to pull data from the database?

================================================== ===================
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="SQLOLEDB"
conn.Open ="server=192.168.0.22;UID=sa;PWD=test;database=tes t;"

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT IDX, CLIENTIDX, COMPANY FROM CL_ADDRESSES WHERE CLIENTIDX= '" & Session.Contents("clientidx") & "' ORDER BY COMPANY", conn
================================================== ===================

In that case, what's the global.asa for used? Initially i thought it will be good to place all the connection at one location [global.asa].
When timeout Session_OnEnd would do all the closing for a particular user for me.

Yes, i do mean its better to do it on every page.

You're right that the global.asa is a good place to put common processing, you just have to use it with care. Databases are expensive resources so you don't want to keep them open for any longer than necessary. In your case of putting an open connection into the session, you are then leaving that connection open for at least 20 mins (depending on timeout settings on your server) and what's even worse, you are doing that for each user that visits your site. This is not a good design to support a lot of users. Still, it could have been worse, you could have put the open connection into the Application object - that would reduce the number of open connections, but it has the massive drawback of forcing all users to go through the same db connection which chokes your site.

By all means store connection strings in Session or Application, just be very careful with objects.

hth
Phil