ADODB Connection and Recordset resource handling

[erstwhileShirker]

Working on ASP page which returns the results of three ADODB queries to populate drop down menus. If I return from connection from a function, it does not seem to work. Is this because the type of the returned value in VBScript is Variant? If the connection is returned, then it can be closed once the three queries

I'd like to do this-:
============================================

conn = dbConnection()
rs = dbQuery(table, field)
while not rs.EOF
%> <option value="<%=rs("Field")%>"><%=rs("Field")
%>
</option>
<%
rs.MoveNext
wend
set rs = nothing

rs = dbQuery(table2, field2)
while not rs.EOF
%> <option value="<%=rs("Field")%>"><%=rs("Field")
%>
</option>
<%
rs.MoveNext
wend
set rs = nothing

etc
conn.Close
conn = nothing

=====================================
but the only working version I've managed is

=====================================

rs = sqlConnect(table, field) ' Opens connection in here
' Leaves connection open
<populate drop down 1>

sqlConnect(table, field) ' Opens connection in here
' Leaves connection open
<populate drop down 2>


Can't close connnection, unless i make it global.
What is the normal idiom, use a function and
return the connection (which I couldnt get to work, error
424 Object Required) or make the connection object global?

At present the code leaks memory as I see it.
thanks

 

[dilettante]

You don't seem to be using Set for those object references.

 

[erstwhileShirker]

The code above does use set, it works. Sorry the original post is a bit unclear however.

I'm trying to return a connection object from a function, use that connection object to call
set rs = conn.execute(...)
three times, then close the connection

conn.close
set conn = nothing

THe code that does work opens a connection in a function, and returns a recordset. Each time a query is to be performed, the function opens a new connection. None of the connections are closed.

 

[Sheco]

It will help to figure out why the function doesn't work if you post the function itself in addition to the code that calls it.

 

[Tarwn]

<%
Function GetConnection
	Dim strConnect
	strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
				 "Data Source=" & Server.MapPath("MyDb.mdb") & _
				 "Persist Security Info=False"
	
	Dim conn
	Set conn = Server.CreateObject("ADODB.Connection")
	conn.Open strConnect

	[highlight]Set[/highlight] GetConnection = conn
End Function


Dim myCrazyConn, myCrazyRS

[highlight]Set[/highlight] myCrazyConn = GetConnection()
Set myCrazyRS = myCrazyConn.Execute("SELECT * FROM urllist")

If myCrazyRS.EOF Then
	response.Write "No records returned, but no errors so it must have worked"
Else
	Response.Write "Some records returned, it must have worked."
End If

Set MyCrazyRS = Nothing
MyCrazyConn.Close
Set MyCrazyConn = Nothing

%>

Make sure all your "Set"'s are truly in place, otherwise you will get odd "Object Required" errors when you attempt to use the variable you hoped would have a reference to the open connection.

-T

 

[erstwhileShirker]

Not using set was indeed the cause of the error.
Thanks for your comments.