Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 25th, 2003, 01:22 AM
Registered User
 
Join Date: Aug 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need Help!! ASP Paging


======================
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

 
Old August 25th, 2003, 10:00 AM
Authorized User
 
Join Date: Aug 2003
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

rs.open sql,conn



Chintu


 
Old August 25th, 2003, 09:27 PM
Registered User
 
Join Date: Aug 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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")

 
Old August 26th, 2003, 03:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
 
Old August 26th, 2003, 04:43 AM
Registered User
 
Join Date: Aug 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default



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.

 
Old August 26th, 2003, 05:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by stitch


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





Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP Paging Query Bozmeister ASP.NET 3.5 Basics 3 November 8th, 2008 02:20 PM
Paging using asp and javascript x_ray Classic ASP Basics 2 February 4th, 2006 06:14 AM
Paging in ASP. rupen Classic ASP Basics 0 October 4th, 2005 08:34 AM
Paging with ASP Luke24 Classic ASP Basics 0 February 9th, 2004 06:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.