quote:

---

Mmm, not a particularly good analogy Doug ...

---

quote:

---

Originally posted by Nikkol

I get the idea. Just was wondering the efficiency of opening db connection ... opening and closing multiple recordsets ... closing db connection VERSUS open db conn, open recordset, close recordset, close db conn multiple times. I use GetRows almost exclusively when I get recordsets, but usually i only open one connection to db.

---

quote:

---

Originally posted by HuwR


It is more like a room with many doors , but while you hold a door open nobody else can pass through it, closing it quickly allows someone else to use the door rather than have to wait or open find another door

---

quote:

---

Originally posted by davemaxwell

According to a source I am working on a work-related project for, there is no real reason to open a connection at all. All you need to do is pass the Connection String to the recordset object and let it handle it on it's own. According to the guy I'm working with, this approach came directly from the ADO docs and is supposed to be much more efficient. I haven't taken the time to test it thoroughly to verify this claim, however.

---

Function fAPG_DBGetRows(strSQL, intPageSize, intPage)
DIM my_Conn, rs, arr
	if not isNumeric(intPageSize) or not (intPageSize > 0) then intPageSize = 0
	if not isNumeric(intPage) or not (intPage > 0) then intPage = 0
	set my_Conn = Server.CreateObject("ADODB.Connection")
	my_Conn.Open strAPG_Connection
	set rs = Server.CreateObject("ADODB.Recordset")
	if intPageSize > 0 then rs.cachesize = intPageSize
	rs.open strSql, my_Conn, 3
	If not (rs.EOF or rs.BOF) then
		if intPageSize > 0 then rs.PageSize = intPageSize
		intAPG_DBQAllRecCount = cLng(rs.RecordCount)
		if intPage > 0 then rs.AbsolutePage = intPage
		intAPG_DBQPageCount = cLng(rs.PageCount)
		if intPage > 0 then
			arr = rs.GetRows(intPageSize)
		else
			arr = rs.GetRows()
		end if
		intAPG_DBQRecCount = UBound(arr, 2)
	else
		arr = Null
		intAPG_DBQRecCount = 0
		intAPG_DBQPageCount = 0
	end if
	rs.Close
	set rs = nothing
	my_Conn.Close
	set my_Conn = nothing
	fAPG_DBGetRows = arr
End Function

' very global variables
dim intAPG_DBQAllRecCount, intAPG_DBQPageCount, intAPG_DBQRecCount

' some task specific array
Dim arr, i

strSql = "SELECT FIELD1, FIELD2 FROM MYTABLE"
arr = fAPG_DBGetRows(strSQL, 0, 0)
if intAPG_DBQRecCount > 0 then 'this is like rs.eof check
	for i=0 to intAPG_DBQRecCount-1
		'do some task
	next
else 'empty result
   'do alternative task
end if

Function fAPG_DBGetRows(strSQL, intPageSize, intPage)
DIM my_Conn, rs, arr
	if not isNumeric(intPageSize) or not (intPageSize > 0) then intPageSize = 0
	if not isNumeric(intPage) or not (intPage > 0) then intPage = 0
	set my_Conn = Server.CreateObject("ADODB.Connection")
	my_Conn.Open strAPG_Connection
	set rs = Server.CreateObject("ADODB.Recordset")
	if (intPageSize > 0) AND (strAPG_DataBaseType <> "mysql") then rs.cachesize = intPageSize
	rs.open strSql, my_Conn, adOpenStatic, adLockReadOnly, adCmdText
	If not (rs.EOF or rs.BOF) then
		if intPageSize > 0 then rs.PageSize = intPageSize
		intAPG_DBQAllRecCount = cLng(rs.RecordCount)
		if intPage > 0 then rs.AbsolutePage = intPage
		intAPG_DBQPageCount = cLng(rs.PageCount)
		if intPage > 0 then
			arr = rs.GetRows(intPageSize)
		else
			arr = rs.GetRows()
		end if
		intAPG_DBQRecCount = UBound(arr, 2)+1
	else
		arr = Null
		intAPG_DBQAllRecCount = 0
		intAPG_DBQRecCount = 0
		intAPG_DBQPageCount = 0
	end if
	rs.Close
	set rs = nothing
	my_Conn.Close
	set my_Conn = nothing
	fAPG_DBGetRows = arr
End Function