Presently I am attempting to create some coding short cuts for the website. Now to query a database and return an archive set 2 functions need to be known as:
GetDBConn returns a wide open ado connection resist the bond string passed in.
GetRS returns an archive set object in line with the ado connection and sql passed in.
set objConn = GetDBConn(connString, commandTimeout, connTimeout, cursorType) set objRs = GetRS(objConn, sql)
I wish to basically write individuals two like a single function but my question really becomes this... I think that you are designed to close and destroy your ado connections when completed with them. Basically
dim the bond within the new function, query the database and return the record set, I can not close and destroy the bond within the function otherwise the record set came back through the function becomes useless (i.e. that connection object isn't clearly closed/destroyed). Is the fact that ok? Does it have negative effect on the SQL/Web Servers?
Not closing connections may cause issues with your SQL server's assets. Based on your hardware and just how many connections are becoming established, you might not watch a problem. Or it might result in the server inaccessible...
Utilizing it for record sets is most likely not recommended (too simple to forget to shut them), however opening / closing an association should not be considered a problem. I personally use the next two functions on my legacy Classic ASP sites:
<% Dim oConn Sub openConn() Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open sConn 'connection string here' End Sub Sub closeConn() If IsObject(oConn) Then On Error Resume Next If oConn.State = 1 Then oConn.Close End If Set oConn = Nothing Err.Clear On Error Goto 0 End If End Sub %>
I Quickly include
conn.asp and employ
openConn() once towards the top of the page and
closeConn() at the end.
On Error Resume Next is generally regarded as like a "bad coding" practice, in this situation I believe it's appropriate otherwise you will be getting errors on all of the pages you utilize
closeConn() on for individuals edge cases when
oConn.State fails unconditionally.
I have always used the dbhelper.asp supplied by the MS reference application FmStocks The functions always return a disconnected recordset which means you never get in danger
The functions are just like that one:
Function RunSQLReturnRS(sqlstmt, params()) On Error Resume next ' Create the ADO objects' Dim rs , cmd Set rs = server.createobject("ADODB.Recordset") Set cmd = server.createobject("ADODB.Command") ' Init the ADO objects & the stored proc parameters' cmd.ActiveConnection = GetConnectionString() cmd.CommandText = sqlstmt cmd.CommandType = adCmdText cmd.CommandTimeout = 900 ' 15 minutes collectParams cmd, params ' Execute the query for readonly' rs.CursorLocation = adUseClient rs.Open cmd, , adOpenForwardOnly, adLockReadOnly If err.number > 0 then BuildErrorMessage() exit function end if ' Disconnect the recordset' Set cmd.ActiveConnection = Nothing Set cmd = Nothing Set rs.ActiveConnection = Nothing ' Return the resultant recordset' Set RunSQLReturnRS = rs End Function